Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
11.8.5
-
None
-
None
-
OS: Ubuntu 24.04
DBMS: MariaDB
Docker image: mariadb:11.8.5
Image ID: bfe9184ea9e5
Description
Description
When a string literal is compared to a REAL UNSIGNED column using IN, the index ref lookup path clips the negative string to 0 and returns matching rows, while the table scan path (expression evaluation) correctly evaluates the comparison as FALSE. The same predicate produces two different results depending on the access path.
Steps to Reproduce
CREATE TABLE IF NOT EXISTS t0(c0 VARCHAR(100), c1 REAL UNSIGNED NOT NULL, PRIMARY KEY(c1, c0)); |
|
|
INSERT INTO t0 VALUES ('8689279', 389113378); |
INSERT INTO t0 VALUES ('-1063519893', 75464848); |
INSERT INTO t0 VALUES ('-112129356', 1636287067); |
INSERT INTO t0 VALUES ('td', 1129361368); |
UPDATE IGNORE t0 SET c1=-2005606925; |
|
|
SELECT t0.c0 FROM t0 WHERE ('-1928045772' IN (t0.c1));-- cardinality: 4 |
SELECT ref0 FROM (SELECT t0.c0 AS ref0, ('-1928045772' IN (t0.c1)) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 0 |
Expected Result
Both queries should return 0 rows, because '-1928045772' is not equal to 0.
Actual Result
Query A returns 4 rows; Query B returns 0 rows.
mysql> SELECT t0.c0 FROM t0 WHERE ('-1928045772' IN (t0.c1));-- cardinality: 4 |
+-------------+ |
| c0 |
|
+-------------+ |
| -1063519893 |
|
| -112129356 |
|
| 8689279 |
|
| td |
|
+-------------+ |
4 rows in set (0.00 sec) |
|
|
mysql> SELECT ref0 FROM (SELECT t0.c0 AS ref0, ('-1928045772' IN (t0.c1)) AS ref1 FROM t0) AS s WHERE ref1;-- cardinality: 0 |
Empty set (0.00 sec) |
Explain Evidence
Query A uses ref access on PRIMARY, treating the string as const and apparently matching c1 = 0
{
|
"table": { |
"table_name": "t0", |
"access_type": "ref", |
"key": "PRIMARY", |
"used_key_parts": ["c1"], |
"ref": ["const"], |
"rows": 4 |
}
|
}
|
Query B uses ALL (table scan) with the predicate evaluated as an attached_condition, yielding FALSE
{
|
"table": { |
"table_name": "t0", |
"access_type": "ALL", |
"rows": 4, |
"attached_condition": "'-1928045772' = t0.c1 is true" |
}
|
}
|
Analysis
The ref access path converts the string literal '-1928045772' using the column's UNSIGNED domain semantics (clipping to 0), while the expression evaluator converts both sides to DOUBLE for comparison. These two conversion strategies are inconsistent.
Suggested Fix
Ensure that constant-to-column conversion in the index lookup path uses the same rules as the expression evaluation path when the column is REAL UNSIGNED.
Attachments
Issue Links
- relates to
-
-
- Confirmed
-