Bug #110125 Unexpected query result fetched from an indexed column

• Submitted: 19 Feb 2023 8:48
• Modified: 11 Sep 16:28
• Reporter: John Jove
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.32, 5.7
• OS: Ubuntu
• CPU Architecture: Any

[19 Feb 2023 8:48] John Jove

Description:
The SELECT statement returns an incorrect result on an indexed column.

How to repeat:
Run the following statements, in which the SELECT statement should return an empty result instead of a 0.

CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE ZEROFILL) ;
CREATE INDEX i0 ON t0(c0 DESC);
INSERT DELAYED IGNORE INTO t0(c0) VALUES(0);

SELECT c0 FROM t0 WHERE (IFNULL(-1123870957, '')) IN (t0.c0); -- 0

[20 Feb 2023 5:03] MySQL Verification Team

Hello John Jove,

Thank you for the report and feedback.

regards,
Umesh

[14 Oct 2024 15:24] OCA Admin

Contribution submitted via Github - bug-110125 Unexpected query result fetched from an indexed column 
(*) Contribution by c q (Github hotdb-cq, mysql-server/pull/573#issuecomment-2410655526): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_2122268321.txt (text/plain), 1.89 KiB.

[3 Jun 8:48] MySQL Admin

Posted by developer: Bug status updated to 'Patch pending'

[19 Jun 14:48] MySQL Admin

Posted by developer: Bug status updated to 'Patch approved'

[19 Jun 20:48] MySQL Admin

Posted by developer: Bug status updated to 'Documenting'

[11 Sep 16:28] Edward Gilmore

Posted by developer:
 
Added the following note to the MySQL Server 26.7.0 release notes:

A SELECT using an indexed unsigned DOUBLE column could return the row
containing zero instead of an empty result when compared with a negative
constant expression having a string result type. 

The out-of-range conversion is now recognized as an
impossible range. 

Our thanks to Kun Qin for the contribution.