Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.3.1
-
ubuntu22.04
-
Not for Release Notes
Description
Summary
On MariaDB 12.3.1-MariaDB-asan-log (rev 21a0714a), indexed integer comparison still returns wrong rows for fractional literals and strings:
WHERE i = 0.6 |
WHERE i = '0.6' |
Both predicates incorrectly use the index and match rows where i = 1. However, semantically equivalent DECIMAL / folded forms return the correct empty result:
WHERE i = CAST('0.6' AS DECIMAL(10,2)) |
WHERE i = 0.6 + 0 |
This appears related to MDEV-38199, but the additional issue here is inconsistent behavior across equivalent constant-coercion paths.
Minimal Reproducer
CREATE TABLE t1 (i INT, KEY (i)); |
INSERT INTO t1 VALUES (1), (1), (1); |
|
|
SELECT COUNT(*) FROM t1 WHERE i = 0.6; |
-- 3, wrong
|
|
|
SELECT COUNT(*) FROM t1 WHERE i = '0.6'; |
-- 3, wrong
|
|
|
SELECT COUNT(*) FROM t1 WHERE i = CAST('0.6' AS DECIMAL(10,2)); |
-- 0, correct
|
|
|
SELECT COUNT(*) FROM t1 WHERE i = 0.6 + 0; |
-- 0, correct
|
|
|
SELECT COUNT(*) FROM t1 IGNORE INDEX (i) WHERE i = 0.6; |
-- 0, correct |
Expected
All predicates should return 0 rows. A fractional value 0.6 should not match integer value 1.
Actual
The indexed literal/string paths return all 3 rows, while DECIMAL cast, folded expression, and IGNORE INDEX return 0 rows.
Environment
| Item | Value |
|---|---|
| Version | 12.3.1-MariaDB-asan-log |
| Revision | 21a0714a118614982d20bfa504763d7247800091 |
Attachments
Issue Links
- duplicates
-
MDEV-29259 Comparison semantic of int = string changes with creation of an index
-
- Confirmed
-