BIGINT UNSIGNED value >= 2^63 is returned by both IN (subquery) and NOT IN (subquery) against a DOUBLE column

A BIGINT UNSIGNED column (no index) is compared with a DOUBLE column through IN (subquery). A row whose value is 2^63 or more is returned by both v IN (SELECT d ...) and v NOT IN (SELECT d ...). Neither table contains a NULL, so every row should be in exactly one of the two results.

The semi-join (FirstMatch) path compares the values as DOUBLE and finds the match. The non-semi-join IN evaluation (the select-list expression, NOT IN, and IN with semijoin=off) says there is no match. The gate is exactly 2^63, which suggests that the unsigned left operand is read as a signed value on that path.

How to repeat

CREATE DATABASE t092; USE t092;

CREATE TABLE o(id INT PRIMARY KEY, v BIGINT UNSIGNED) ENGINE=InnoDB;  -- no index

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

INSERT INTO o VALUES (1, 18446744073709551615), (2, 18446744073709551614), (3, 1);

INSERT INTO i VALUES (1, 18446744073709551615);

SELECT id FROM o WHERE v IN     (SELECT d FROM i) ORDER BY id;   -- 1, 2

SELECT id FROM o WHERE v NOT IN (SELECT d FROM i) ORDER BY id;   -- 1, 2, 3

Rows 1 and 2 are in both results.

SELECT id, CAST(v AS CHAR) v,

       (v IN     (SELECT d FROM i)) AS in_q,

       (v NOT IN (SELECT d FROM i)) AS notin_q

FROM o ORDER BY id;

-- 1  18446744073709551615  0  1

-- 2  18446744073709551614  0  1

-- 3  1                     0  1

SELECT o.id, v = d FROM o JOIN i ORDER BY o.id;                    -- 1:1  2:1  3:0

SELECT id FROM o WHERE EXISTS (SELECT 1 FROM i WHERE d = v);       -- 1, 2

SET optimizer_switch='semijoin=off';

SELECT id FROM o WHERE v IN (SELECT d FROM i) ORDER BY id;         -- (empty)

SET optimizer_switch=default;

v = d is true for rows 1 and 2, and so are EXISTS and the semi-join. The IN predicate evaluated per row (and therefore NOT IN) is false for both. The result of WHERE v IN (...) changes with optimizer_switch='semijoin=off'.

EXPLAIN SELECT id FROM o WHERE v IN (SELECT d FROM i);

1 PRIMARY o ALL NULL NULL NULL NULL 3

1 PRIMARY i ALL NULL NULL NULL NULL 1 Using where; FirstMatch(o); Using join buffer (flat, BNL join)

The boundary is 2^63

TRUNCATE o; TRUNCATE i;

INSERT INTO o VALUES (1, 9223372036854775807),   -- 2^63 - 1

                     (2, 9223372036854775808),   -- 2^63

                     (3, 9223372036854777856),   -- next double above 2^63

                     (4, 1);

INSERT INTO i VALUES (1, 9223372036854775808), (2, 1);

SELECT id, (SELECT COUNT(*) FROM i WHERE i.d = o.v) AS eq_matches,

       (v IN (SELECT d FROM i)) AS in_q, (v NOT IN (SELECT d FROM i)) AS notin_q,

       v = ANY (SELECT d FROM i) AS eq_any

FROM o ORDER BY id;

Below 2^63, IN agrees with =. At 2^63 it does not. With these rows, WHERE v IN (...) returns 1, 2, 4 and WHERE v NOT IN (...) returns 2, 3, so row 2 is in both.

The non-semi-join plan is <in_optimizer>(o.v, <exists>(select i.d from i where <cache>(o.v) = i.d)). The <cache> is an Item_cache_int with unsigned_flag set, and Item_cache_int::val_real() (sql/item.cc) returns (double) value without checking unsigned_flag, so 2^63 is compared as -2^63 in Arg_comparator::compare_real(). This looks like the same function as MDEV-40680 (MIN/MAX over BIGINT UNSIGNED converted to a float), here reached without any aggregate.

Expected

IN and NOT IN partition the table, as they do on MySQL 26.10.0 / 9.7.2. On MySQL, IN returns 1, 2 and NOT IN returns 3 for the first example, and the per-row in_q is 1 for rows 1 and 2. The IN-subquery evaluation should compare an unsigned left operand with a DOUBLE the same way = does.

No warnings are raised. The defect is silent.