MySQL Bugs: #114382: Incorrect query result may caused by LOW

Bug #114382	Incorrect query result may caused by LOW_PRIORITY
Submitted:	17 Mar 2024 13:58	Modified:	18 Mar 2024 7:06
Reporter:	Ye Shiyang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.11, 8.0.35, 8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
 Dear devps, Our fuzzer may found a bug.

Consider the following statements, qeury1 and query2 should reutrn the same reuslt. However, qeury1 return -127 while query2 return nothing.

-- query1
mysql> SELECT f1 FROM (SELECT (LEAST(CURDATE(), (- (t1.c0)), (~ (t1.c0)),  EXISTS (SELECT 1))) AS f1 FROM t1) as t WHERE (f1) & (921571774);
+------+
| f1   |
+------+
| -127 |
+------+
1 row in set, 3 warnings (0.00 sec)

--query2
mysql> SELECT f1 FROM (SELECT (LEAST(CURDATE(), (- (t1.c0)), (~ (t1.c0)),  EXISTS (SELECT 1))) AS f1, ((LEAST(CURDATE(), (- (t1.c0)), (~ (t1.c0)),  EXISTS (SELECT 1))) & (921571774)) IS TRUE AS flag FROM t1) as t WHERE flag=1;
Empty set, 6 warnings (0.00 sec)

How to repeat:
The bug can be reproduced from 8.0.11

CREATE TABLE IF NOT EXISTS t1(c0 TINYINT(199)) ;
INSERT LOW_PRIORITY IGNORE INTO t1(c0) VALUES (1950654919);

SELECT f1 FROM (SELECT (LEAST(CURDATE(), (- (t1.c0)), (~ (t1.c0)),  EXISTS (SELECT 1))) AS f1 FROM t1) as t WHERE (f1) & (921571774);

SELECT f1 FROM (SELECT (LEAST(CURDATE(), (- (t1.c0)), (~ (t1.c0)),  EXISTS (SELECT 1))) AS f1, ((LEAST(CURDATE(), (- (t1.c0)), (~ (t1.c0)),  EXISTS (SELECT 1))) & (921571774)) IS TRUE AS flag FROM t1) as t WHERE flag=1;

The bug can't be reproduced without keyword LOW_PRIORITY in DML statement.

Hello Ye Shiyang,

Thank you for the report and feedback

regards,
Umesh

I tried to simplify the repeat, it omitted the  & (921571774).

CREATE TABLE IF NOT EXISTS t1(c0 TINYINT(199)) ;
INSERT LOW_PRIORITY IGNORE INTO t1(c0) VALUES (1950654919);

SELECT f1 FROM (SELECT (LEAST(CURDATE(), (- (t1.c0)),  EXISTS (SELECT 1))) AS f1 FROM t1) as t WHERE (f1) ;

+------+
| f1   |
+------+
| -127 |
+------+
1 row in set, 2 warnings (0.00 sec)

SELECT f1 FROM (SELECT (LEAST(CURDATE(), (- (t1.c0)),  EXISTS (SELECT 1))) AS f1, (LEAST(CURDATE(), (- (t1.c0)),   EXISTS (SELECT 1)) ) IS TRUE AS flag FROM t1) as t WHERE flag=1;

Empty set, 4 warnings (0.01 sec)

I kindly request the development team to examine this  simplified query to identify the root cause of the BUG and fix the  issue as soon as possible. By the way, please let me know if  this  simplified query is useful.I believe that by analyzing and fixing this simplified query, you can more easily locate the problem in the original  complex query.

Thank you for your hard work and support!