Description:
When a query uses GROUP BY (IFNULL(NULL, c1)) XOR ((CASE c0 WHEN c0 THEN const ELSE c1 END) IS NOT NULL), the result type is BIGINT (since XOR yields an integer).
In a direct covering index scan path, GROUP BY correctly separates rows into two groups: NULL (rows with c1=NULL) and 1 (the row with c1=0).
However, when the query is rewritten as a derived table containing JSON_OBJECT, with session settings big_tables=1 and internal_tmp_mem_storage_engine='TempTable', the optimizer uses a TempTable‑engine temporary table for deduplication and grouping. During this process, the value 1 is incorrectly recognized as NULL, causing two distinct groups to be merged into a single NULL group, losing one row.
This is a wrong‑result bug caused by a comparison defect in the TempTable engine for mixed‑type grouping expressions.
Actual Results and Status
Query Actual Groups Status
Single‑table (covering index scan) NULL, 1 (2 groups) Correct
JSON_PACK (TempTable) NULL (only 1 group) Incorrect
EXPLAIN Analysis
Single‑table query (correct):
-> Table scan on <temporary>
-> Temporary table with deduplication
-> Covering index scan on vp_s using i0 (c0 DESC, c1)
JSON_PACK query (incorrect):
-> Table scan on <temporary>
-> Temporary table with deduplication
-> Nested loop inner join
-> Nested loop inner join
-> Covering index scan on l using l_idx
-> Single-row covering index lookup on vp_r using PRIMARY
-> Single-row index lookup on r using PRIMARY
How to repeat:
DROP DATABASE IF EXISTS repro_mysql714_db33;
CREATE DATABASE repro_mysql714_db33;
USE repro_mysql714_db33;
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 big_tables = 1;
SET SESSION internal_tmp_mem_storage_engine = 'TempTable';
CREATE TABLE vp_s (
vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 DECIMAL(65, 30) NULL,
c1 DECIMAL(65, 30) NULL
) ENGINE = InnoDB;
INSERT INTO vp_s (c0, c1) VALUES (-934955100, NULL), (1, NULL), (698073542, 0);
ALTER TABLE vp_s ADD UNIQUE KEY i0 (c0 DESC, c1);
CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE = InnoDB;
CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65,30) NULL) ENGINE = InnoDB;
INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s;
INSERT INTO vp_r SELECT vp_rowid, c1 FROM vp_s;
CREATE INDEX l_idx ON vp_l(vp_rowid, c0);
-- Single‑table query (correct): returns 2 groups (NULL and 1)
SELECT DISTINCTROW (IFNULL(NULL, vp_s.c1)) XOR
((CASE vp_s.c0 WHEN vp_s.c0 THEN 1.241251282E9 ELSE vp_s.c1 END) IS NOT NULL) AS ref0
FROM vp_s
GROUP BY (IFNULL(NULL, vp_s.c1)) XOR
((CASE vp_s.c0 WHEN vp_s.c0 THEN 1.241251282E9 ELSE vp_s.c1 END) IS NOT NULL);
-- JSON_PACK rewrite (incorrect): returns only 1 group (NULL)
SELECT DISTINCTROW (IFNULL(NULL, t.c1)) XOR
((CASE t.c0 WHEN t.c0 THEN 1.241251282E9 ELSE t.c1 END) IS NOT NULL) AS ref0
FROM (
SELECT l.vp_rowid, l.c0, r.c1
FROM vp_l l
JOIN (SELECT vp_rowid, JSON_OBJECT('vp', vp_rowid) AS payload FROM vp_r) jp
ON l.vp_rowid = jp.vp_rowid
JOIN vp_r r ON r.vp_rowid = jp.vp_rowid
) t
GROUP BY (IFNULL(NULL, t.c1)) XOR
((CASE t.c0 WHEN t.c0 THEN 1.241251282E9 ELSE t.c1 END) IS NOT NULL);
Description: When a query uses GROUP BY (IFNULL(NULL, c1)) XOR ((CASE c0 WHEN c0 THEN const ELSE c1 END) IS NOT NULL), the result type is BIGINT (since XOR yields an integer). In a direct covering index scan path, GROUP BY correctly separates rows into two groups: NULL (rows with c1=NULL) and 1 (the row with c1=0). However, when the query is rewritten as a derived table containing JSON_OBJECT, with session settings big_tables=1 and internal_tmp_mem_storage_engine='TempTable', the optimizer uses a TempTable‑engine temporary table for deduplication and grouping. During this process, the value 1 is incorrectly recognized as NULL, causing two distinct groups to be merged into a single NULL group, losing one row. This is a wrong‑result bug caused by a comparison defect in the TempTable engine for mixed‑type grouping expressions. Actual Results and Status Query Actual Groups Status Single‑table (covering index scan) NULL, 1 (2 groups) Correct JSON_PACK (TempTable) NULL (only 1 group) Incorrect EXPLAIN Analysis Single‑table query (correct): -> Table scan on <temporary> -> Temporary table with deduplication -> Covering index scan on vp_s using i0 (c0 DESC, c1) JSON_PACK query (incorrect): -> Table scan on <temporary> -> Temporary table with deduplication -> Nested loop inner join -> Nested loop inner join -> Covering index scan on l using l_idx -> Single-row covering index lookup on vp_r using PRIMARY -> Single-row index lookup on r using PRIMARY How to repeat: DROP DATABASE IF EXISTS repro_mysql714_db33; CREATE DATABASE repro_mysql714_db33; USE repro_mysql714_db33; 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 big_tables = 1; SET SESSION internal_tmp_mem_storage_engine = 'TempTable'; CREATE TABLE vp_s ( vp_rowid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 DECIMAL(65, 30) NULL, c1 DECIMAL(65, 30) NULL ) ENGINE = InnoDB; INSERT INTO vp_s (c0, c1) VALUES (-934955100, NULL), (1, NULL), (698073542, 0); ALTER TABLE vp_s ADD UNIQUE KEY i0 (c0 DESC, c1); CREATE TABLE vp_l (vp_rowid BIGINT NOT NULL PRIMARY KEY, c0 DECIMAL(65,30) NULL) ENGINE = InnoDB; CREATE TABLE vp_r (vp_rowid BIGINT NOT NULL PRIMARY KEY, c1 DECIMAL(65,30) NULL) ENGINE = InnoDB; INSERT INTO vp_l SELECT vp_rowid, c0 FROM vp_s; INSERT INTO vp_r SELECT vp_rowid, c1 FROM vp_s; CREATE INDEX l_idx ON vp_l(vp_rowid, c0); -- Single‑table query (correct): returns 2 groups (NULL and 1) SELECT DISTINCTROW (IFNULL(NULL, vp_s.c1)) XOR ((CASE vp_s.c0 WHEN vp_s.c0 THEN 1.241251282E9 ELSE vp_s.c1 END) IS NOT NULL) AS ref0 FROM vp_s GROUP BY (IFNULL(NULL, vp_s.c1)) XOR ((CASE vp_s.c0 WHEN vp_s.c0 THEN 1.241251282E9 ELSE vp_s.c1 END) IS NOT NULL); -- JSON_PACK rewrite (incorrect): returns only 1 group (NULL) SELECT DISTINCTROW (IFNULL(NULL, t.c1)) XOR ((CASE t.c0 WHEN t.c0 THEN 1.241251282E9 ELSE t.c1 END) IS NOT NULL) AS ref0 FROM ( SELECT l.vp_rowid, l.c0, r.c1 FROM vp_l l JOIN (SELECT vp_rowid, JSON_OBJECT('vp', vp_rowid) AS payload FROM vp_r) jp ON l.vp_rowid = jp.vp_rowid JOIN vp_r r ON r.vp_rowid = jp.vp_rowid ) t GROUP BY (IFNULL(NULL, t.c1)) XOR ((CASE t.c0 WHEN t.c0 THEN 1.241251282E9 ELSE t.c1 END) IS NOT NULL);