`a BETWEEN b AND c` should not be equivalent to `a >= b AND a <= c`
- 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.