Description:
For an indexed INT column, comparing the column with a fractional DECIMAL/function expression can return rows that do not satisfy the predicate.
The row-by-row expression evaluation is correct, and the same query with IGNORE INDEX is correct. However, the indexed access path appears to truncate the fractional expression value to an integer when building the equality range, and returns that integer row without applying a residual filter.
This was found by SQLancer TLP WHERE testing.
Observed on:
8.0.46-0ubuntu0.24.04.3
How to repeat:
CREATE DATABASE mysql_int_fractional_repro;
USE mysql_int_fractional_repro;
CREATE TABLE t(c INT NOT NULL, KEY(c));
INSERT INTO t VALUES (-1), (0), (1);
-- Row-by-row evaluation is correct: all comparisons are false.
SELECT c,
c = CAST(0.06669620421124867 AS DECIMAL(20,18)) AS eq
FROM t
ORDER BY c;
-- Wrong: indexed access returns c=0.
SELECT c
FROM t
WHERE c = CAST(0.06669620421124867 AS DECIMAL(20,18));
-- Correct: full scan returns no rows.
SELECT c
FROM t IGNORE INDEX(c)
WHERE c = CAST(0.06669620421124867 AS DECIMAL(20,18));
-- Another example: indexed access returns c=1, but row-by-row comparison is false.
SELECT c
FROM t
WHERE c = CAST(1.1 AS DECIMAL(10,1));
-- Correct: full scan returns no rows.
SELECT c
FROM t IGNORE INDEX(c)
WHERE c = CAST(1.1 AS DECIMAL(10,1));
Suggested fix:
This looks like a range optimizer issue.
For an equality predicate between an indexed INT column and a non-integer DECIMAL/function expression, the optimizer builds an equality range using the expression value stored/coerced into the INT field. The fractional part is truncated, e.g. 0.066... -> 0 and 1.1 -> 1. The range scan then returns the truncated integer row even though the original SQL predicate evaluates to false.
The optimizer should either reject this as an impossible equality range when the expression is known to have a fractional part, or keep a residual predicate filter so that rows returned by the range scan are rechecked against the original expression.
Description: For an indexed INT column, comparing the column with a fractional DECIMAL/function expression can return rows that do not satisfy the predicate. The row-by-row expression evaluation is correct, and the same query with IGNORE INDEX is correct. However, the indexed access path appears to truncate the fractional expression value to an integer when building the equality range, and returns that integer row without applying a residual filter. This was found by SQLancer TLP WHERE testing. Observed on: 8.0.46-0ubuntu0.24.04.3 How to repeat: CREATE DATABASE mysql_int_fractional_repro; USE mysql_int_fractional_repro; CREATE TABLE t(c INT NOT NULL, KEY(c)); INSERT INTO t VALUES (-1), (0), (1); -- Row-by-row evaluation is correct: all comparisons are false. SELECT c, c = CAST(0.06669620421124867 AS DECIMAL(20,18)) AS eq FROM t ORDER BY c; -- Wrong: indexed access returns c=0. SELECT c FROM t WHERE c = CAST(0.06669620421124867 AS DECIMAL(20,18)); -- Correct: full scan returns no rows. SELECT c FROM t IGNORE INDEX(c) WHERE c = CAST(0.06669620421124867 AS DECIMAL(20,18)); -- Another example: indexed access returns c=1, but row-by-row comparison is false. SELECT c FROM t WHERE c = CAST(1.1 AS DECIMAL(10,1)); -- Correct: full scan returns no rows. SELECT c FROM t IGNORE INDEX(c) WHERE c = CAST(1.1 AS DECIMAL(10,1)); Suggested fix: This looks like a range optimizer issue. For an equality predicate between an indexed INT column and a non-integer DECIMAL/function expression, the optimizer builds an equality range using the expression value stored/coerced into the INT field. The fractional part is truncated, e.g. 0.066... -> 0 and 1.1 -> 1. The range scan then returns the truncated integer row even though the original SQL predicate evaluates to false. The optimizer should either reject this as an impossible equality range when the expression is known to have a fractional part, or keep a residual predicate filter so that rows returned by the range scan are rechecked against the original expression.