Description:
When a WHERE condition contains +(IF('', NULL, '0.22857643383585602')) IN (bigint_column), a direct base‑table scan correctly evaluates it (no rows match).
However, when the query is rewritten as a derived table using window functions and STRAIGHT_JOIN, the optimizer pushes the IN condition down to an index lookup, and in doing so, incorrectly drops the unary plus operator (+). The comparison then becomes a string‑to‑BIGINT equality check.
According to MySQL’s type conversion rules, the string '0.22857643383585602' is truncated to 0 when compared with an integer, causing an erroneous match on rows where c0 = 0.
Actual Results and Status
Query Actual Result Status
Single‑table (FROM src) Empty set Correct
Derived table (CTE + STRAIGHT_JOIN) 1 row (c0=0) Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Zero rows (Impossible WHERE noticed after reading const tables) (cost=0..0 rows=0)
Derived‑table query (incorrect) – key part:
-> Nested loop inner join
-> Table scan on lrn
-> Index lookup on rrn using <auto_key0>
(rn = lrn.rn, c0 = if('',NULL,'0.22857643383585602'))
Expression semantics: +(IF('', NULL, '0.22857643383585602')) – IF returns the string '0.228...', and the unary plus + converts it to the DOUBLE value 0.228....
Original condition: 0.228... IN (c0) checks whether 0.228... equals 0 or 1, which is false (no match).
Pushdown rewrite bug: When pushing the condition to an index lookup, the optimizer converts IN into an equality comparison and generates a lookup key. In this process, it erroneously drops the outer + operator and uses the raw string returned by IF() as the key.
Type conversion difference: Comparing a BIGINT column with a string causes MySQL to convert the string to an integer (parsing from the beginning and stopping at the first non‑digit). '0.228...' is truncated to 0, leading to a false match on c0=0.
How to repeat:
DROP DATABASE IF EXISTS repro_vp14;
CREATE DATABASE repro_vp14;
USE repro_vp14;
CREATE TABLE src (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 BIGINT NULL,
UNIQUE KEY (c0)
);
INSERT INTO src VALUES (1, 0), (2, 1);
CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY);
CREATE TABLE r (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 BIGINT NULL,
KEY (c0, vp_rowid)
);
INSERT INTO l SELECT vp_rowid FROM src;
INSERT INTO r SELECT vp_rowid, c0 FROM src;
-- Single‑table query (correct): returns empty set
SELECT c0
FROM src
WHERE +(IF('', NULL, '0.22857643383585602')) IN (c0)
ORDER BY CAST((CASE c0 WHEN vp_rowid THEN vp_rowid ELSE NULL END) IS NOT NULL AS SIGNED) DESC;
-- Derived‑table query (incorrect): returns one row (c0=0)
WITH
lrn AS (
SELECT ROW_NUMBER() OVER (ORDER BY vp_rowid) AS rn, vp_rowid FROM l
),
rrn AS (
SELECT ROW_NUMBER() OVER (ORDER BY vp_rowid) AS rn, vp_rowid, c0 FROM r
)
SELECT x.c0
FROM (
SELECT lrn.vp_rowid, rrn.c0
FROM lrn STRAIGHT_JOIN rrn ON lrn.rn = rrn.rn
) AS x
WHERE +(IF('', NULL, '0.22857643383585602')) IN (x.c0)
ORDER BY CAST((CASE x.c0 WHEN x.vp_rowid THEN x.vp_rowid ELSE NULL END) IS NOT NULL AS SIGNED) DESC;
Description: When a WHERE condition contains +(IF('', NULL, '0.22857643383585602')) IN (bigint_column), a direct base‑table scan correctly evaluates it (no rows match). However, when the query is rewritten as a derived table using window functions and STRAIGHT_JOIN, the optimizer pushes the IN condition down to an index lookup, and in doing so, incorrectly drops the unary plus operator (+). The comparison then becomes a string‑to‑BIGINT equality check. According to MySQL’s type conversion rules, the string '0.22857643383585602' is truncated to 0 when compared with an integer, causing an erroneous match on rows where c0 = 0. Actual Results and Status Query Actual Result Status Single‑table (FROM src) Empty set Correct Derived table (CTE + STRAIGHT_JOIN) 1 row (c0=0) Incorrect EXPLAIN Analysis Single‑table query (correct): -> Zero rows (Impossible WHERE noticed after reading const tables) (cost=0..0 rows=0) Derived‑table query (incorrect) – key part: -> Nested loop inner join -> Table scan on lrn -> Index lookup on rrn using <auto_key0> (rn = lrn.rn, c0 = if('',NULL,'0.22857643383585602')) Expression semantics: +(IF('', NULL, '0.22857643383585602')) – IF returns the string '0.228...', and the unary plus + converts it to the DOUBLE value 0.228.... Original condition: 0.228... IN (c0) checks whether 0.228... equals 0 or 1, which is false (no match). Pushdown rewrite bug: When pushing the condition to an index lookup, the optimizer converts IN into an equality comparison and generates a lookup key. In this process, it erroneously drops the outer + operator and uses the raw string returned by IF() as the key. Type conversion difference: Comparing a BIGINT column with a string causes MySQL to convert the string to an integer (parsing from the beginning and stopping at the first non‑digit). '0.228...' is truncated to 0, leading to a false match on c0=0. How to repeat: DROP DATABASE IF EXISTS repro_vp14; CREATE DATABASE repro_vp14; USE repro_vp14; CREATE TABLE src ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL, UNIQUE KEY (c0) ); INSERT INTO src VALUES (1, 0), (2, 1); CREATE TABLE l (vp_rowid BIGINT NOT NULL PRIMARY KEY); CREATE TABLE r ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL, KEY (c0, vp_rowid) ); INSERT INTO l SELECT vp_rowid FROM src; INSERT INTO r SELECT vp_rowid, c0 FROM src; -- Single‑table query (correct): returns empty set SELECT c0 FROM src WHERE +(IF('', NULL, '0.22857643383585602')) IN (c0) ORDER BY CAST((CASE c0 WHEN vp_rowid THEN vp_rowid ELSE NULL END) IS NOT NULL AS SIGNED) DESC; -- Derived‑table query (incorrect): returns one row (c0=0) WITH lrn AS ( SELECT ROW_NUMBER() OVER (ORDER BY vp_rowid) AS rn, vp_rowid FROM l ), rrn AS ( SELECT ROW_NUMBER() OVER (ORDER BY vp_rowid) AS rn, vp_rowid, c0 FROM r ) SELECT x.c0 FROM ( SELECT lrn.vp_rowid, rrn.c0 FROM lrn STRAIGHT_JOIN rrn ON lrn.rn = rrn.rn ) AS x WHERE +(IF('', NULL, '0.22857643383585602')) IN (x.c0) ORDER BY CAST((CASE x.c0 WHEN x.vp_rowid THEN x.vp_rowid ELSE NULL END) IS NOT NULL AS SIGNED) DESC;