Bug #111918 Auto increment column query wrong result
Submitted: 30 Jul 2023 6:12 Modified: 31 Jul 2023 6:29
Reporter: Pedro Ferreira Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.1.0, 8.0.34, 5.7.43 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:x86 (x86_64)
Tags: auto increment

[30 Jul 2023 6:12] Pedro Ferreira 
Description:
Run these queries:

CREATE TABLE t1 (c0 INT AUTO_INCREMENT UNIQUE KEY);
INSERT INTO t1 VALUES ();
INSERT IGNORE INTO t1 VALUES ();

SELECT 1 FROM t1;
 -- 2 rows
SELECT 1 FROM t1 WHERE (t1.c0 > CAST(X'0B36' AS CHAR));
 -- empty
SELECT 1 FROM t1 WHERE (NOT (t1.c0 > CAST(X'0B36' AS CHAR)));
 -- empty
SELECT 1 FROM t1 WHERE ((t1.c0 > CAST(X'0B36' AS CHAR)) IS NULL);
 -- empty

The cardinalities of the predicate queries must match the predicate-less query. By looking more carefully, the query SELECT 1 FROM t1 WHERE (NOT (t1.c0 > CAST(X'0B36' AS CHAR))); has the wrong result, it should return 2 rows.

How to repeat:
Run the queries above.
[31 Jul 2023 6:29] MySQL Verification Team 
Hello Pedro Ferreira,

Thank you for the report and test case.

regards,
Umesh