Bug #114224 Incorrect query result may caused by INDEX and Type conversion
Submitted: 5 Mar 2024 14:10 Modified: 5 Mar 2024 15:23
Reporter: Ye Shiyang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[5 Mar 2024 14:10] Ye Shiyang
Description:
Since c0 is 0!=ABS(0.1),so the second query should not return 0.

SELECT * FROM t0;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
SELECT t0.c0 FROM t0 WHERE t0.c0=ABS(0.1);
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

How to repeat:
it's noted that the bug can't be reproduced without INDEX in the second line.

CREATE TABLE t0(c0 TINYINT);
CREATE INDEX i0 ON t0(c0 DESC); 
INSERT IGNORE INTO t0(c0) VALUES("c");

SELECT * FROM t0;
SELECT t0.c0 FROM t0 WHERE t0.c0=ABS(0.1);
[5 Mar 2024 15:23] MySQL Verification Team
Hi Mr. Shiyang,

Thank you for your bug report.

We were able to repeat your test case:

Level	Code	Message
Warning	1366	Incorrect integer value: 'c' for column 'c0' at row 1
c0
0
c0
0
Level	Code	Message
Warning	1366	Incorrect integer value: 'c' for column 'c0' at row 1
c0
0

Hence, there is a different result, with or without the index. 

However, this is a very low priority bug. First of all, as our Manual clearly points out, ABS() can be used ONLY with integers. However, instead of integers, you are using string , in this case "c" or floating point (0.1). That is why this is a very low priority bug. It is verified only due to different behaviour due to the presence of an index.

SQL Standard strictly prohibits conversion between different types.

The only bug is in the different behaviour, as explained above.

Verified as a feature request.