Bug #120970 TempTable Engine Incorrectly Treats XOR Result Value 1 as NULL in GROUP BY, Merging Groups

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);

Hi Annie liu,

Thank you for the test case. This is fixed in the upcoming MySQL-9.7 LTS release. Marking this as duplicate of Bug#120429.

Thanks,
Chaithra