Description:
When an outer query has a WHERE condition of the form constant IN (derived_table_column) and the optimizer pushes that condition down into the base table scan inside the derived table, the derived table materialize as empty.
According to SQL standard, an outer GROUP BY EXISTS(SELECT 1) over an empty input should still produce one group (since the grouping expression is constant), with the aggregate returning NULL. However, the actual execution returns Empty set, which differs from the direct base-table query where the same WHERE condition matches a row and returns NULL.
The push-down causes all rows to be filtered out before materialization, and the outer aggregate fails to generate the default group for a constant GROUP BY on an empty set.
Results
Query Actual Result Status
Base table (FROM s) 1 row: ref0 = NULL ✓
Derived table (FROM d) Empty set (0 rows) ✗
Explanation: In standard SQL, GROUP BY EXISTS(SELECT 1) over an empty input should produce one group (since the grouping expression is constant). The aggregate should return NULL. The derived‑table version returns an empty set, violating the SQL standard.
EXPLAIN Analysis (Derived Table Version – Key Part)
Group aggregate
-> Table scan on d
-> Materialize
-> Nested loop inner join
-> Filter: (<cache>(ifnull(0.9349479012631875,-1058210595)) = l.c3)
-> Single-row covering index lookup on r using PRIMARY
Key point:
The outer WHERE IFNULL(...) IN (d.c3) is pushed down into the derived table’s base table scan, becoming Filter: (constant = l.c3).
Since l.c3 contains only NULL and 1, and the constant 0.9349... equals neither, the Filter eliminates all rows, resulting in an empty materialized derived table.
The outer aggregate MIN(DISTINCT ...) should return NULL for an empty input when there is a constant GROUP BY, but instead the whole result set is empty.
How to repeat:
DROP DATABASE IF EXISTS codex_db0;
CREATE DATABASE codex_db0;
USE codex_db0;
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 = 1;
SET SESSION internal_tmp_mem_storage_engine = 'MEMORY';
CREATE TABLE s(
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 TEXT NULL,
c1 DECIMAL(65,30) NULL,
c2 BIGINT NULL,
c3 BIGINT NULL,
KEY idx_c3_c2(c3, c2)
) ENGINE=InnoDB;
INSERT INTO s(c0, c1, c2, c3) VALUES
(NULL, NULL, 1, NULL),
(NULL, 1143839529.000000000000000000000000000000, -268005709, 1);
CREATE TABLE l(
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c1 DECIMAL(65,30) NULL,
c2 BIGINT NULL,
c3 BIGINT NULL
) ENGINE=InnoDB;
CREATE TABLE r(
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 TEXT NULL
) ENGINE=InnoDB;
INSERT INTO l(vp_rowid, c1, c2, c3)
SELECT vp_rowid, c1, c2, c3 FROM s;
INSERT INTO r(vp_rowid, c0)
SELECT vp_rowid, c0 FROM s;
-- Base table query (correct): returns 1 row with NULL
SELECT MIN(DISTINCT CAST((CAST(s.c1 AS SIGNED)) IN ((s.c0) != (s.c2)) AS SIGNED)) AS ref0
FROM s
WHERE IFNULL(0.9349479012631875, -1058210595) IN (s.c3)
GROUP BY EXISTS (SELECT 1);
-- Derived table rewrite (incorrect): returns Empty set
SELECT MIN(DISTINCT CAST((CAST(d.c1 AS SIGNED)) IN ((d.c0) != (d.c2)) AS SIGNED)) AS ref0
FROM (
SELECT
l.vp_rowid AS vp_rowid,
(SELECT r.c0 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c0,
l.c1 AS c1,
l.c2 AS c2,
l.c3 AS c3
FROM l
WHERE l.vp_rowid IN (SELECT r.vp_rowid FROM r)
) d
WHERE IFNULL(0.9349479012631875, -1058210595) IN (d.c3)
GROUP BY EXISTS (SELECT 1);
Description: When an outer query has a WHERE condition of the form constant IN (derived_table_column) and the optimizer pushes that condition down into the base table scan inside the derived table, the derived table materialize as empty. According to SQL standard, an outer GROUP BY EXISTS(SELECT 1) over an empty input should still produce one group (since the grouping expression is constant), with the aggregate returning NULL. However, the actual execution returns Empty set, which differs from the direct base-table query where the same WHERE condition matches a row and returns NULL. The push-down causes all rows to be filtered out before materialization, and the outer aggregate fails to generate the default group for a constant GROUP BY on an empty set. Results Query Actual Result Status Base table (FROM s) 1 row: ref0 = NULL ✓ Derived table (FROM d) Empty set (0 rows) ✗ Explanation: In standard SQL, GROUP BY EXISTS(SELECT 1) over an empty input should produce one group (since the grouping expression is constant). The aggregate should return NULL. The derived‑table version returns an empty set, violating the SQL standard. EXPLAIN Analysis (Derived Table Version – Key Part) Group aggregate -> Table scan on d -> Materialize -> Nested loop inner join -> Filter: (<cache>(ifnull(0.9349479012631875,-1058210595)) = l.c3) -> Single-row covering index lookup on r using PRIMARY Key point: The outer WHERE IFNULL(...) IN (d.c3) is pushed down into the derived table’s base table scan, becoming Filter: (constant = l.c3). Since l.c3 contains only NULL and 1, and the constant 0.9349... equals neither, the Filter eliminates all rows, resulting in an empty materialized derived table. The outer aggregate MIN(DISTINCT ...) should return NULL for an empty input when there is a constant GROUP BY, but instead the whole result set is empty. How to repeat: DROP DATABASE IF EXISTS codex_db0; CREATE DATABASE codex_db0; USE codex_db0; 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 = 1; SET SESSION internal_tmp_mem_storage_engine = 'MEMORY'; CREATE TABLE s( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 TEXT NULL, c1 DECIMAL(65,30) NULL, c2 BIGINT NULL, c3 BIGINT NULL, KEY idx_c3_c2(c3, c2) ) ENGINE=InnoDB; INSERT INTO s(c0, c1, c2, c3) VALUES (NULL, NULL, 1, NULL), (NULL, 1143839529.000000000000000000000000000000, -268005709, 1); CREATE TABLE l( vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65,30) NULL, c2 BIGINT NULL, c3 BIGINT NULL ) ENGINE=InnoDB; CREATE TABLE r( vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 TEXT NULL ) ENGINE=InnoDB; INSERT INTO l(vp_rowid, c1, c2, c3) SELECT vp_rowid, c1, c2, c3 FROM s; INSERT INTO r(vp_rowid, c0) SELECT vp_rowid, c0 FROM s; -- Base table query (correct): returns 1 row with NULL SELECT MIN(DISTINCT CAST((CAST(s.c1 AS SIGNED)) IN ((s.c0) != (s.c2)) AS SIGNED)) AS ref0 FROM s WHERE IFNULL(0.9349479012631875, -1058210595) IN (s.c3) GROUP BY EXISTS (SELECT 1); -- Derived table rewrite (incorrect): returns Empty set SELECT MIN(DISTINCT CAST((CAST(d.c1 AS SIGNED)) IN ((d.c0) != (d.c2)) AS SIGNED)) AS ref0 FROM ( SELECT l.vp_rowid AS vp_rowid, (SELECT r.c0 FROM r WHERE r.vp_rowid = l.vp_rowid) AS c0, l.c1 AS c1, l.c2 AS c2, l.c3 AS c3 FROM l WHERE l.vp_rowid IN (SELECT r.vp_rowid FROM r) ) d WHERE IFNULL(0.9349479012631875, -1058210595) IN (d.c3) GROUP BY EXISTS (SELECT 1);