Description:
When a query uses MIN(DISTINCT IFNULL(NULL, c0)) to find the minimum value of a column, a direct table scan correctly returns the non‑NULL minimum (e.g., 1).
However, when the query is rewritten as a derived table that includes a JSON_OBJECT subquery and then joined with other tables, the optimizer merges the subquery and the final execution plan becomes a Nested Loop Join (NLJ).
In this NLJ path, MIN(DISTINCT IFNULL(NULL, c0)) incorrectly returns NULL, even though a non‑NULL value (1) exists in the table.
The root cause is that when creating a temporary table for DISTINCT deduplication, the optimizer incorrectly marks the non‑NULL values as NULL, causing all non‑NULL rows to be discarded, leaving only NULL rows.
Actual Results and Status
Query Actual Result Status
Single‑table (FROM src) 1 Correct
Derived table (with JSON_OBJECT) NULL Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Aggregate: min(ifnull(NULL,src.c0))
-> Table scan on src
Direct base table scan; MIN computes correctly.
Derived‑table query (incorrect):
-> Aggregate: min(ifnull(NULL,r.c0))
-> Nested loop inner join
-> Nested loop inner join
-> Covering index scan on l using PRIMARY
-> Single-row index lookup on r using PRIMARY (vp_rowid = l.vp_rowid)
-> Single-row index lookup on r using PRIMARY (vp_rowid = l.vp_rowid)
The optimizer eliminates the JSON_OBJECT subquery, executing two l JOIN r operations (one for the original jp join, one to fetch c0). The final input rows are identical to the r table.
For DISTINCT aggregates, the optimizer creates a temporary table for deduplication. For columns arriving from the NLJ path, the temporary table incorrectly propagates the column's nullable flag to all rows, causing the actual non‑NULL 1 to be treated as NULL during duplicate comparison, thus discarding it.
How to repeat:
DROP DATABASE IF EXISTS repro73_29;
CREATE DATABASE repro73_29;
USE repro73_29;
SET SESSION sql_mode =
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
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 = 0;
SET SESSION internal_tmp_mem_storage_engine = 'MEMORY';
CREATE TABLE src (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL(65,30) NULL,
c1 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
INSERT INTO src (c0, c1) VALUES
(NULL, NULL),
(NULL, NULL),
(NULL, -1295921894),
(1, NULL);
ALTER TABLE src ADD UNIQUE KEY i0 (c0 DESC, c1);
ALTER TABLE src ADD KEY i1 (c1, c0);
CREATE TABLE l (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c1 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
CREATE TABLE r (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 DECIMAL(65,30) NULL
) ENGINE=InnoDB;
INSERT INTO l (vp_rowid, c1) SELECT vp_rowid, c1 FROM src;
INSERT INTO r (vp_rowid, c0) SELECT vp_rowid, c0 FROM src;
CREATE INDEX l_asym ON l(vp_rowid, c1);
CREATE INDEX r_idx ON r(vp_rowid, c0);
SELECT 'single' AS qt, MIN(DISTINCT IFNULL(NULL, c0)) AS ref0
FROM src;
SELECT 'split' AS qt, MIN(DISTINCT IFNULL(NULL, c0)) AS ref0
FROM (
SELECT l.vp_rowid, r.c0, l.c1
FROM l
JOIN (
SELECT vp_rowid, JSON_OBJECT('vp', vp_rowid) AS payload
FROM r
) jp ON l.vp_rowid = jp.vp_rowid
JOIN r ON r.vp_rowid = jp.vp_rowid
) AS src;
Description: When a query uses MIN(DISTINCT IFNULL(NULL, c0)) to find the minimum value of a column, a direct table scan correctly returns the non‑NULL minimum (e.g., 1). However, when the query is rewritten as a derived table that includes a JSON_OBJECT subquery and then joined with other tables, the optimizer merges the subquery and the final execution plan becomes a Nested Loop Join (NLJ). In this NLJ path, MIN(DISTINCT IFNULL(NULL, c0)) incorrectly returns NULL, even though a non‑NULL value (1) exists in the table. The root cause is that when creating a temporary table for DISTINCT deduplication, the optimizer incorrectly marks the non‑NULL values as NULL, causing all non‑NULL rows to be discarded, leaving only NULL rows. Actual Results and Status Query Actual Result Status Single‑table (FROM src) 1 Correct Derived table (with JSON_OBJECT) NULL Incorrect EXPLAIN Analysis Single‑table query (correct): -> Aggregate: min(ifnull(NULL,src.c0)) -> Table scan on src Direct base table scan; MIN computes correctly. Derived‑table query (incorrect): -> Aggregate: min(ifnull(NULL,r.c0)) -> Nested loop inner join -> Nested loop inner join -> Covering index scan on l using PRIMARY -> Single-row index lookup on r using PRIMARY (vp_rowid = l.vp_rowid) -> Single-row index lookup on r using PRIMARY (vp_rowid = l.vp_rowid) The optimizer eliminates the JSON_OBJECT subquery, executing two l JOIN r operations (one for the original jp join, one to fetch c0). The final input rows are identical to the r table. For DISTINCT aggregates, the optimizer creates a temporary table for deduplication. For columns arriving from the NLJ path, the temporary table incorrectly propagates the column's nullable flag to all rows, causing the actual non‑NULL 1 to be treated as NULL during duplicate comparison, thus discarding it. How to repeat: DROP DATABASE IF EXISTS repro73_29; CREATE DATABASE repro73_29; USE repro73_29; SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; 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 = 0; SET SESSION internal_tmp_mem_storage_engine = 'MEMORY'; CREATE TABLE src ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL(65,30) NULL, c1 DECIMAL(65,30) NULL ) ENGINE=InnoDB; INSERT INTO src (c0, c1) VALUES (NULL, NULL), (NULL, NULL), (NULL, -1295921894), (1, NULL); ALTER TABLE src ADD UNIQUE KEY i0 (c0 DESC, c1); ALTER TABLE src ADD KEY i1 (c1, c0); CREATE TABLE l ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65,30) NULL ) ENGINE=InnoDB; CREATE TABLE r ( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL ) ENGINE=InnoDB; INSERT INTO l (vp_rowid, c1) SELECT vp_rowid, c1 FROM src; INSERT INTO r (vp_rowid, c0) SELECT vp_rowid, c0 FROM src; CREATE INDEX l_asym ON l(vp_rowid, c1); CREATE INDEX r_idx ON r(vp_rowid, c0); SELECT 'single' AS qt, MIN(DISTINCT IFNULL(NULL, c0)) AS ref0 FROM src; SELECT 'split' AS qt, MIN(DISTINCT IFNULL(NULL, c0)) AS ref0 FROM ( SELECT l.vp_rowid, r.c0, l.c1 FROM l JOIN ( SELECT vp_rowid, JSON_OBJECT('vp', vp_rowid) AS payload FROM r ) jp ON l.vp_rowid = jp.vp_rowid JOIN r ON r.vp_rowid = jp.vp_rowid ) AS src;