MySQL Bugs: #117213: Wrong execution result when remove all parentheses

Bug #117213	Wrong execution result when remove all parentheses
Submitted:	16 Jan 5:22	Modified:	16 Jan 12:17
Reporter:	zachary dylan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I noticed that MySQL seems to have issues with default operator precedence. 
According to the precedence rules in the manual, adding parentheses results in inconsistencies.

mysql> SELECT *
    -> FROM t0
    -> WHERE c0 * -1 < 0 AND c0 BETWEEN 2.5 AND c0 >> 1 > 1 - c0 AND c0 ^ 3 << 2 < 41
    -> ORDER BY c0;
+----+------+
| id | c0   |
+----+------+
|  2 |  2.5 |
|  3 |   10 |
+----+------+
2 rows in set (0.01 sec)

mysql> SELECT *
    -> FROM t0
    -> WHERE ((c0 * (-1)) < 0)
    ->   AND (c0 BETWEEN 2.5 AND ((c0 >> 1) > (1 - c0)))
    ->   AND (((c0 ^ 3) << 2) < 41)
    -> ORDER BY c0;
Empty set (0.01 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    c0 DOUBLE,
    INDEX idx_c0 (c0)
);

INSERT INTO t0 (c0) VALUES 
(1), 
(2.5), 
(10), 
(18.8), 
(-2.4), 
(-1.4134e10);

SELECT *
FROM t0 
WHERE c0 * -1 < 0 AND c0 BETWEEN 2.5 AND c0 >> 1 > 1 - c0 AND c0 ^ 3 << 2 < 41 
ORDER BY c0;

SELECT *
FROM t0
WHERE ((c0 * (-1)) < 0)
  AND (c0 BETWEEN 2.5 AND ((c0 >> 1) > (1 - c0)))
  AND (((c0 ^ 3) << 2) < 41)
ORDER BY c0;

Update DBMS version

Hi Mr. dylan,

Thank you for your bug report.

However, we do not see the problem in the results.

Using parenthesis overrides operator precedence rules. 

Hence, it is expected behaviour that you can not get the same results.

Not a bug.

Dear MySQL Verification Team,

Thank you for your response.

I would like to clarify my concern further. According to the MySQL documentation, the default behavior of operator precedence should follow the exact rules outlined in the manual. The issue arises because the default behavior (without parentheses) does not align with the results obtained when parentheses are added to define the operator precedence explicitly. This inconsistency suggests that the default behavior might not be adhering to the documented precedence rules.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

Explanation of Parentheses:

1. c0 * -1 < 0:
   1. Unary minus (-) is applied to c0 first because it has higher precedence than multiplication (*).
   2. The multiplication result is then compared with 0 using <.
2. c0 BETWEEN 2.5 AND c0 >> 1 > 1 - c0:
   1. BETWEEN checks if c0 lies between 2.5 and the result of (c0 >> 1) > (1 - c0).
   2. Inside BETWEEN:
      1. c0 >> 1 is evaluated first because bitwise shift (>>) has higher precedence than comparison (>).
      2. The result of the shift is then compared with (1 - c0).
3. c0 ^ 3 << 2 < 41:
   1. c0 ^ 3 (bitwise XOR) is evaluated first, as ^ has higher precedence than the bitwise shift (<<).
   2. The result of the XOR operation is then left-shifted by 2.
   3. Finally, the shifted value is compared with 41 using <.

Hi Mr. dylan,

We have analysed your test case.

You are correct.

This is a small bug in our Optimiser.

Verified for versions 8.0 and higher.

Thanks for your help ......

regards,
Dylan