Description:
When a query uses ROW_NUMBER() window functions to align two tables into CTEs, and a Hash Join is used to join them, evaluating the outer expression IFNULL(id NOT IN (NULL), LEAST(c1, c0)) incorrectly returns NULL for rows that should return 0.
The same logic executed directly on the base table returns the correct results (0).
The root cause is that during the Hash Join probe phase, when copying the column c1 from the right‑hand CTE materialized table to the output row, the optimizer incorrectly sets the column’s NULL flag to 1 for all rows, causing even non‑NULL values (like 0 and 1) to be treated as NULL. This makes LEAST(c1, c0) return NULL.
Removing auxiliary indexes or disabling Hash Join / big_tables forces a different execution path, and the issue disappears.
Actual Results and Status
Query Actual Result Status
Single‑table (FROM s) NULL, 0, 0 Correct
CTE rewrite (Hash Join) NULL, NULL, NULL Incorrect
Note: The second and third rows should have v = 0, but the Hash Join path returns NULL for all.
EXPLAIN Analysis
Single‑table query (correct):
-> Limit: 11 row(s)
-> Index scan on s using PRIMARY
Direct index scan, expression evaluation is correct.
CTE rewrite query (incorrect) – key part:
-> Limit: 11 row(s)
-> Sort: id, limit input to 11 row(s) per chunk
-> Stream results
-> Inner hash join (r2.rn = l2.rn)
-> Table scan on r2
-> Materialize CTE r2
-> Window aggregate: row_number() OVER (ORDER BY r.id)
-> Sort: r.id
-> Covering index scan on r using r_idx
-> Hash
-> Table scan on l2
-> Materialize CTE l2
-> Window aggregate: row_number() OVER (ORDER BY l.id)
-> Sort: l.id
-> Covering index scan on l using l_idx
The optimizer uses a Hash Join with the right‑hand side being the materialized CTE r2 (columns rn, id, c1).
During the probe phase, the c1 column is copied incorrectly, with its NULL flag set to 1 for all rows, discarding actual non‑NULL values.
How to repeat:
DROP DATABASE IF EXISTS repro73_18;
CREATE DATABASE repro73_18;
USE repro73_18;
SET SESSION optimizer_switch =
'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,hash_set_operations=on';
SET SESSION big_tables = 1;
CREATE TABLE s (
id BIGINT NOT NULL PRIMARY KEY,
c0 DECIMAL(20,0),
c1 DECIMAL(20,0)
) ENGINE=InnoDB;
INSERT INTO s VALUES
(1, -1713750794, NULL),
(2, 0, 0),
(3, 0, 1);
CREATE TABLE l (
id BIGINT NOT NULL PRIMARY KEY,
c0 DECIMAL(20,0)
) ENGINE=InnoDB;
CREATE TABLE r (
id BIGINT NOT NULL PRIMARY KEY,
c1 DECIMAL(20,0)
) ENGINE=InnoDB;
INSERT INTO l SELECT id, c0 FROM s;
INSERT INTO r SELECT id, c1 FROM s;
CREATE INDEX l_idx ON l(id, c0);
CREATE INDEX r_idx ON r(c1, id);
-- Single‑table query (correct): returns NULL, 0, 0
SELECT IFNULL(s.id NOT IN (NULL), LEAST(s.c1, s.c0)) AS v
FROM s
ORDER BY s.id
LIMIT 11;
-- CTE rewrite + Hash Join (incorrect): returns NULL, NULL, NULL
SELECT IFNULL(q.id NOT IN (NULL), LEAST(q.c1, q.c0)) AS v
FROM (
WITH l2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, id, c0
FROM l
),
r2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, id, c1
FROM r
)
SELECT l2.id, l2.c0, r2.c1
FROM l2 JOIN r2 ON l2.rn = r2.rn
) AS q
ORDER BY q.id
LIMIT 11;
Description: When a query uses ROW_NUMBER() window functions to align two tables into CTEs, and a Hash Join is used to join them, evaluating the outer expression IFNULL(id NOT IN (NULL), LEAST(c1, c0)) incorrectly returns NULL for rows that should return 0. The same logic executed directly on the base table returns the correct results (0). The root cause is that during the Hash Join probe phase, when copying the column c1 from the right‑hand CTE materialized table to the output row, the optimizer incorrectly sets the column’s NULL flag to 1 for all rows, causing even non‑NULL values (like 0 and 1) to be treated as NULL. This makes LEAST(c1, c0) return NULL. Removing auxiliary indexes or disabling Hash Join / big_tables forces a different execution path, and the issue disappears. Actual Results and Status Query Actual Result Status Single‑table (FROM s) NULL, 0, 0 Correct CTE rewrite (Hash Join) NULL, NULL, NULL Incorrect Note: The second and third rows should have v = 0, but the Hash Join path returns NULL for all. EXPLAIN Analysis Single‑table query (correct): -> Limit: 11 row(s) -> Index scan on s using PRIMARY Direct index scan, expression evaluation is correct. CTE rewrite query (incorrect) – key part: -> Limit: 11 row(s) -> Sort: id, limit input to 11 row(s) per chunk -> Stream results -> Inner hash join (r2.rn = l2.rn) -> Table scan on r2 -> Materialize CTE r2 -> Window aggregate: row_number() OVER (ORDER BY r.id) -> Sort: r.id -> Covering index scan on r using r_idx -> Hash -> Table scan on l2 -> Materialize CTE l2 -> Window aggregate: row_number() OVER (ORDER BY l.id) -> Sort: l.id -> Covering index scan on l using l_idx The optimizer uses a Hash Join with the right‑hand side being the materialized CTE r2 (columns rn, id, c1). During the probe phase, the c1 column is copied incorrectly, with its NULL flag set to 1 for all rows, discarding actual non‑NULL values. How to repeat: DROP DATABASE IF EXISTS repro73_18; CREATE DATABASE repro73_18; USE repro73_18; SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,hash_set_operations=on'; SET SESSION big_tables = 1; CREATE TABLE s ( id BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(20,0), c1 DECIMAL(20,0) ) ENGINE=InnoDB; INSERT INTO s VALUES (1, -1713750794, NULL), (2, 0, 0), (3, 0, 1); CREATE TABLE l ( id BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(20,0) ) ENGINE=InnoDB; CREATE TABLE r ( id BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(20,0) ) ENGINE=InnoDB; INSERT INTO l SELECT id, c0 FROM s; INSERT INTO r SELECT id, c1 FROM s; CREATE INDEX l_idx ON l(id, c0); CREATE INDEX r_idx ON r(c1, id); -- Single‑table query (correct): returns NULL, 0, 0 SELECT IFNULL(s.id NOT IN (NULL), LEAST(s.c1, s.c0)) AS v FROM s ORDER BY s.id LIMIT 11; -- CTE rewrite + Hash Join (incorrect): returns NULL, NULL, NULL SELECT IFNULL(q.id NOT IN (NULL), LEAST(q.c1, q.c0)) AS v FROM ( WITH l2 AS ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, id, c0 FROM l ), r2 AS ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, id, c1 FROM r ) SELECT l2.id, l2.c0, r2.c1 FROM l2 JOIN r2 ON l2.rn = r2.rn ) AS q ORDER BY q.id LIMIT 11;