Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.3.3, 13.0.2, 13.1.1
-
None
-
Platform: Ubuntu 24.04, x86_64
-
Not for Release Notes
-
Comparing an indexed DOUBLE UNSIGNED column with a negative number given as a string, such as c = '-1', could return the rows whose value is 0. The same comparison with a numeric constant, or without the index, returned no rows.
Description
Comparing an indexed DOUBLE UNSIGNED column with the string constant '-1' returns the row whose value is 0. 0 = '-1' is false. The numeric constant -1 and the same query with IGNORE INDEX both return the correct empty set.
How to reproduce
CREATE TABLE t(c DOUBLE UNSIGNED, KEY(c)); |
INSERT INTO t VALUES (0), (5); |
|
|
SELECT 0 = '-1'; |
-- 0
|
|
|
SELECT c FROM t IGNORE INDEX (c) WHERE c = '-1'; |
-- Empty set correct
|
|
|
SELECT c FROM t WHERE c = -1; |
-- Empty set correct
|
|
|
SELECT c FROM t WHERE c = '-1'; |
-- expected: Empty set
|
-- actual: 0 |
Notes
INT UNSIGNED PRIMARY KEY, REAL UNSIGNED PRIMARY KEY and DECIMAL UNSIGNED with a secondary key did not show the problem in my tests; DOUBLE UNSIGNED with a secondary key does.
Attachments
Issue Links
- duplicates
-
MDEV-30594 The incorrect result of the WHERE condition which contains the IN operation between the unsigned double value with the index and the negative value in String type.
-
- Confirmed
-