Join/scalar subquery of indexed BIGINT or DECIMAL column with DOUBLE column returns different rows with and without the index (MDEV-6969 still present)

Comparing an indexed exact-numeric column (BIGINT, DECIMAL) with a DOUBLE column gives different rows depending on whether the index is used. With a ref lookup on the index, the DOUBLE is converted to the key type and compared exactly. Without the index, both sides are compared as DOUBLE. For a value that a double cannot hold exactly, the two rules disagree, and the result of a join depends on the plan.

This is the defect of MDEV-6969. That issue was closed as Won't Fix in 2022 with only the comment "10.0 was EOLed in March 2019". It reproduces unchanged on 13.2.0 main (d45cf75c), and also with signed BIGINT and DECIMAL(30,0).

How to repeat

CREATE DATABASE t091; USE t091;

CREATE TABLE big(id INT PRIMARY KEY, v BIGINT, KEY kv(v)) ENGINE=InnoDB;

CREATE TABLE dbl(id INT PRIMARY KEY, d DOUBLE) ENGINE=InnoDB;

INSERT INTO big VALUES (1, 9007199254740992),   -- 2^53

                       (2, 9007199254740993),   -- 2^53 + 1

                       (3, 1);

INSERT INTO dbl VALUES (1, 9007199254740992);

ANALYZE TABLE big, dbl;

SELECT big.id FROM big JOIN dbl ON big.v = dbl.d ORDER BY big.id;

-- 1

SELECT big.id FROM big IGNORE INDEX(kv) JOIN dbl ON big.v = dbl.d ORDER BY big.id;

-- 1, 2

SELECT id FROM big WHERE v = (SELECT d FROM dbl) ORDER BY id;

-- 1

SELECT id FROM big WHERE EXISTS (SELECT 1 FROM dbl WHERE d = v) ORDER BY id;

-- 1, 2

SELECT id FROM big WHERE v IN (SELECT d FROM dbl) ORDER BY id;

-- 1, 2

v = (SELECT d FROM dbl), EXISTS (... WHERE d = v) and v IN (SELECT d ...) have the same meaning here (the subquery returns one row), but the scalar-subquery form uses the index and returns one row fewer.

EXPLAIN SELECT big.id FROM big JOIN dbl ON big.v = dbl.d;

1 SIMPLE dbl ALL NULL NULL NULL NULL       1 Using where

1 SIMPLE big ref kv   kv   9    t091.dbl.d 1 Using where; Using index

DECIMAL(30,0): the matching rows disappear with the index

CREATE TABLE dec30(id INT PRIMARY KEY, v DECIMAL(30,0), KEY kv(v)) ENGINE=InnoDB;

CREATE TABLE dbl2(id INT PRIMARY KEY, d DOUBLE) ENGINE=InnoDB;

INSERT INTO dec30 VALUES (1, 123456789012345678901234567890),

                         (2, 123456789012345678901234567891),

                         (3, 1);

INSERT INTO dbl2 VALUES (1, 123456789012345678901234567890);

SELECT dec30.id FROM dec30 JOIN dbl2 ON dec30.v = dbl2.d;                  -- (empty)

SELECT dec30.id FROM dec30 IGNORE INDEX(kv) JOIN dbl2 ON dec30.v = dbl2.d; -- 1, 2

SELECT id FROM dec30 WHERE v IN (SELECT d FROM dbl2);                      -- 1, 2

With the index, even row 1 is lost. Its value is the exact decimal that was inserted into the DOUBLE column. SHOW WARNINGS is empty after every query above.

Summary of results on 13.2.0

Expected

The same rows with and without the index. Whichever comparison rule is chosen, exact or as DOUBLE, it should be the same on the ref path and the scan path. One way is to treat the index lookup as a range that is re-checked with the scan-path comparison, or not to build a ref lookup for exact-key = DOUBLE.

The same defect is present on MySQL trunk (26.10.0). There it additionally makes a row fall out of both IN and NOT IN. It is being reported to MySQL separately.