Bug #121431 Index on BIGINT hides rows equal to a DOUBLE constant just above BIGINT max (range optimizer: value_out_of_range)

• Submitted: 4 Oct 5:24
• Reporter: Ke Han
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.10.0 (trunk, 3b99be40f8a3
• OS: Any
• CPU Architecture: Any

[4 Oct 5:24] Ke Han

Description:
CAST(9223372036854775807 AS DOUBLE) is 9.223372036854776e18 (2^63), one
above BIGINT's maximum.  The server compares a BIGINT column with it as
DOUBLE, so the values 9223372036854775807, ...806 and ...805 all compare equal
to it.  The server says so itself:

    SELECT a, CAST(9223372036854775807 AS DOUBLE) = a FROM t;   -- 1 for all three

Without an index, WHERE a = CAST(9223372036854775807 AS DOUBLE) returns these
three rows.  After CREATE INDEX on a, the same query returns no rows.  EXPLAIN
shows "Zero input rows (no matching row in const table)", so the plan is
decided as impossible during optimization.  IGNORE INDEX gives the 3 rows
back.

Same table, same data:

  predicate                                  with index   IGNORE INDEX
  -----------------------------------------  -----------  ------------
  a =  CAST(9223372036854775807 AS DOUBLE)   0            3
  a >= CAST(9223372036854775807 AS DOUBLE)   0            3
  a <  / a > / a <> / a <= (same constant)   same         same

  DELETE ... WHERE a = CAST(9223372036854775807 AS DOUBLE)
      with the index: 0 rows affected (3 rows match by the server's own =)

The same happens with BIGINT UNSIGNED and CAST(18446744073709551615 AS DOUBLE)
(2^64): 0 rows with the index, 2 with IGNORE INDEX.

With a PRIMARY KEY on a, two predicates on one table contradict each other:
"a = CAST(9223372036854775807 AS DOUBLE)" returns 9223372036854775807 (the
unique-key lookup path), while "a >= CAST(9223372036854775807 AS DOUBLE)" (the
range path) returns nothing.  A row that satisfies = must also satisfy >=.

Any expression that yields a DOUBLE triggers it: CAST(... AS DOUBLE),
CAST('9223372036854775807' AS DOUBLE), abs(lower(9223372036854775807)).  An
integer literal 9223372036854775807 or a DECIMAL (9223372036854775807 + 0.0)
is answered correctly.

Root cause (trunk, sql/range_optimizer/range_analysis.cc,
save_value_and_handle_conversion()):

When get_mm_leaf() stores the constant into the BIGINT field,
save_in_field_no_warnings() saturates to BIGINT max and returns
TYPE_WARN_OUT_OF_RANGE.  The handler for that case (line 1311 ff.) then
concludes:

  - lines 1317-1324: for = and <=>, "Independent of data type,
    out_of_range_value =/<=> field is always false" -> impossible_cond
  - lines 1347-1354: for > and >= against a value above the field's max ->
    "always false" -> impossible_cond

That reasoning holds only if the comparison the executor uses is exact.
Here it is not.  Item_func_eq / Arg_comparator compare BIGINT and DOUBLE as
DOUBLE, and BIGINT max converts to 2^63, which equals the constant.  So "an
out-of-range DOUBLE can never be equal to / less than or equal to a column
value" is false at the top of the range.  The range optimizer and the
executor disagree, and the range optimizer wins only when an index makes it
run.  (For the in-range constant CAST(9007199254740993 AS DOUBLE) index and
non-index agree, so this is specific to the out-of-range branch.)

Expected: the indexed query returns the same rows as IGNORE INDEX (3), and
the DELETE removes them.  Returning 0 consistently would also be defensible,
but the answer must not depend on whether an index exists.

Actual: 0 rows with the index, 3 rows without it.  DELETE removes nothing.

How to repeat:
CREATE DATABASE IF NOT EXISTS b056; USE b056;
CREATE TABLE t (a BIGINT);
INSERT INTO t VALUES (9223372036854775807),(9223372036854775806),
                     (9223372036854775805),(0),(1);

SELECT a, CAST(9223372036854775807 AS DOUBLE) = a AS eq FROM t ORDER BY a DESC;
-- eq = 1 for the three large values

SELECT count(*) FROM t WHERE a = CAST(9223372036854775807 AS DOUBLE);
-- 3

CREATE INDEX ia ON t(a);

SELECT count(*) FROM t WHERE a = CAST(9223372036854775807 AS DOUBLE);
-- 0   <-- wrong
SELECT count(*) FROM t IGNORE INDEX (ia)
 WHERE a = CAST(9223372036854775807 AS DOUBLE);
-- 3
SELECT count(*) FROM t WHERE a >= CAST(9223372036854775807 AS DOUBLE);
-- 0   <-- wrong
SELECT count(*) FROM t IGNORE INDEX (ia)
 WHERE a >= CAST(9223372036854775807 AS DOUBLE);
-- 3
EXPLAIN FORMAT=TREE
SELECT count(*) FROM t WHERE a = CAST(9223372036854775807 AS DOUBLE);
-- -> Zero input rows (no matching row in const table), ...

DELETE FROM t WHERE a = CAST(9223372036854775807 AS DOUBLE);
-- Query OK, 0 rows affected   <-- 3 rows match
SELECT count(*) FROM t;
-- 5

-- BIGINT UNSIGNED, same shape at 2^64:
CREATE TABLE u (a BIGINT UNSIGNED, KEY ia (a));
INSERT INTO u VALUES (18446744073709551615),(18446744073709551614),(0);
SELECT count(*) FROM u WHERE a = CAST(18446744073709551615 AS DOUBLE);
-- 0   <-- wrong
SELECT count(*) FROM u IGNORE INDEX (ia)
 WHERE a = CAST(18446744073709551615 AS DOUBLE);
-- 2

-- PRIMARY KEY: = and >= contradict each other
CREATE TABLE pk (a BIGINT NOT NULL PRIMARY KEY);
INSERT INTO pk VALUES (9223372036854775807),(9223372036854775806),
                      (9223372036854775805),(1);
SELECT a FROM pk WHERE a =  CAST(9223372036854775807 AS DOUBLE);
-- 9223372036854775807
SELECT a FROM pk WHERE a >= CAST(9223372036854775807 AS DOUBLE);
-- empty set   <-- wrong; IGNORE INDEX (PRIMARY) returns 3 rows

Suggested fix:
In save_value_and_handle_conversion(), the TYPE_WARN_OUT_OF_RANGE branch
should not declare =, <=>, > or >= impossible when the comparison the executor
will use is not exact for the column type.  The main case is a REAL_RESULT
constant against an INT_RESULT field.  One option: when the constant is a
DOUBLE and the field is an integer, re-check by comparing the saturated field
value with the constant as DOUBLE (the executor's comparison).  If they are
equal, treat the saturated key as an inclusive bound ([max, max] for =,
[max, +inf) for >=) instead of IMPOSSIBLE.  Do the same on the min side for
negative overflow.  Otherwise, fall back to "always true" (no range, keep the
predicate as a filter), as the code already does for types whose
overflow/underflow it cannot decide.