dolthub / dolthub/dolt

`a BETWEEN b AND c` should not be equivalent to `a >= b AND a <= c`

Open
#10,467 2 comments 0 reactions 0 assignees View on GitHub
analyzer bug correctness
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

Currently, the `BETWEEN` expression in GMS/Dolt is just a wrapper around (`LESS THAN EQUAL` && `GREATER THAN EQUAL`). This is incorrect because the wrong compare type is used when the `BETWEEN` expression is split apart.

Consider this in MySQL
```
mysql> select "8" >= "35" and "8" <= 75;
+---------------------------+
| "8" >= "35" and "8" <= 75 |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select "8" between "35" and 75;
+-------------------------+
| "8" between "35" and 75 |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
```
`select "8" >= "35" and "8" <= 90` is true because string `"8"` is greater than string `"35" and number `8` is less than number `75` (note that string `"8"` would be greater than string `"75"`). However, `select "8" between "35" and 75` is false because number `8` is not between numbers `35` and `75`.

Whereas in Dolt, these two queries are executed the same.
```
tmp/main*> select "8" >= "35" and "8" <= 75;
+---------------------------+
| "8" >= "35" and "8" <= 75 |
+---------------------------+
| true |
+---------------------------+
1 row in set (0.00 sec)

tmp/main*> select "8" between "35" and 75;
+-------------------------+
| "8" between "35" and 75 |
+-------------------------+
| true |
+-------------------------+
1 row in set (0.00 sec)
```

[MySQL docs](https://dev.mysql.com/doc/refman/8.4/en/comparison-operators.html#operator_between)

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.