MySQL Bugs: #116765: Incorrect Operator Precedence of BETWEEN and Comparators in MySQL 8.4

Bug #116765	Incorrect Operator Precedence of BETWEEN and Comparators in MySQL 8.4
Submitted:	25 Nov 2024 2:18	Modified:	25 Nov 2024 11:23
Reporter:	Team QueryHouse	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any (22.04)
Assigned to:		CPU Architecture:	Any
Tags:	between, comparison, operator precedence

Description:
Hi,

According to the official MySQL documentation, comparison operators have higher precedence than the BETWEEN operator. Here’s the relevant part of the documentation:
https://dev.mysql.com/doc/refman/8.4/en/operator-precedence.html
```sql
(highest)
...
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
...
(lowest)
```

However, we’ve observed that MySQL evaluates the BETWEEN operator first while testing this feature, which seems to be in contrast to what's stated in the documentation.

How to repeat:
**Minimized Query to Reproduce**:
```sql
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 VALUES (1), (5), (15), (20);
SELECT c0, (c0 BETWEEN 15 AND c0 <= 0) FROM t0;
```

**Expected Result**:
```
+------+-------------------------------+
| c0   | (c0 BETWEEN 15 AND (c0 <= 0)) |
+------+-------------------------------+
|    1 |                             0 |
|    5 |                             0 |
|   15 |                             0 |
|   20 |                             0 |
+------+-------------------------------+
```
This is the expected result when the comparison operator is  evaluated first, as indicated in the documentation. (Note the parentheses around `c0 <= 0`, which are essential for correct behavior.)

**Actual result** (MySQL, version 8.4 and 8.0.32):
```
+------+-----------------------------+
| c0   | (c0 BETWEEN 15 AND c0 <= 0) |
+------+-----------------------------+
|    1 |                           1 |
|    5 |                           1 |
|   15 |                           0 |
|   20 |                           0 |
+------+-----------------------------+
```
In MySQL 8.4 and 8.0.32, the actual result is the same as when BETWEEN is explicitly forced to be evaluated first using parentheses.

This behavior was observed in both the Docker-installed version 8.0.43 and the apt-installed version 8.4.

Suggested fix:
We believe this behavior is a bug because it differs from the precedence described in the official documentation.

Developers often rely on the official documentation, assuming it's accurate, only to find that it doesn't reflect the actual behavior in this case.
The fact that each DBMS defines the precedence of BETWEEN and comparison operators autonomously makes this situation even more problematic.

We suggest that MySQL be updated to evaluate comparison operators first, as specified in the documentation, or that the documentation be updated to align with the current behavior.

Thank you for your time and attention to this matter. We look forward to your response.

Best regards,

HI Team QueryHouse,

Thank you for your bug report.

We have managed to reproduce your findings :

mysql> SELECT c0, (c0 BETWEEN 15 AND c0 <= 0) FROM t0;
+------+-----------------------------+
| c0   | (c0 BETWEEN 15 AND c0 <= 0) |
+------+-----------------------------+
|    1 |                           1 |
|    5 |                           1 |
|   15 |                           0 |
|   20 |                           0 |
+------+-----------------------------+
4 rows in set (0.00 sec)

mysql> SELECT c0, c0 BETWEEN 15 AND c0 <= 0 FROM t0;
+------+---------------------------+
| c0   | c0 BETWEEN 15 AND c0 <= 0 |
+------+---------------------------+
|    1 |                         1 |
|    5 |                         1 |
|   15 |                         0 |
|   20 |                         0 |
+------+---------------------------+
4 rows in set (0.00 sec)

We agree that this goes against our own documentation and SQL Standard.

This is now a verified bug for version 8.0 and all higher versions.