Description:
Identical behaviour and an identical bisected threshold on all four. This is
not a regression; it is a long-standing defect whose second failure mode has not
been reported.
With internal_tmp_mem_storage_engine = MEMORY, a table holding both -0e0 and
0e0 in a DOUBLE column produces two different wrong outcomes on either side of a
size threshold, neither of them the correct answer:
tmp_table_size SELECT count(*)
FROM (SELECT DISTINCT v FROM r) t
------------------------------ ---------------------------------------
large enough to stay in memory 102 -- silently over by one
small enough to spill to disk ERROR 1062: Duplicate entry '79' for
key '.../#sql....<group_key>'
on a 102-row table whose correct DISTINCT count is 101. SELECT -0e0 = 0e0
returns 1, and count(DISTINCT v) returns 101 -- so the server's own answers
contradict each other.
--------------------------------------------------------------------------------
Mechanism
--------------------------------------------------------------------------------
The MEMORY engine's hash index keys on the raw bytes. In
storage/heap/hp_hash.cc on current trunk:
- hp_hashnr() (line 227, loop at line 282) and hp_rec_hashnr() (line 294, loop
at line 342) hash a non-text key segment with a plain byte loop:
} else {
for (; pos < end; pos++) {
nr ^= (uint64)((((uint)nr & 63) + nr2) * ((uint)*pos)) + (nr << 8);
nr2 += 3;
}
}
- hp_rec_key_cmp() (line 366, comparison at line 436) compares such a segment
with memcmp:
} else {
if (memcmp(rec1 + seg->start, rec2 + seg->start, seg->length) != 0)
return 1;
}
-0.0 and 0.0 differ in the sign bit, so both the hash and the comparison call
them distinct -- this is #67978. Note that the same file already normalizes
NaN, but only on the BTREE key path (hp_rb_make_key(), line 558, NaN handling at
lines 571-587, "Replace NAN with zero"); signed zero is normalized nowhere, and
the hash path normalizes neither.
The escalation is in sql/sql_tmp_table.cc. When the MEMORY table outgrows
tmp_table_size, create_ondisk_from_heap() copies its rows into an on-disk InnoDB
table whose unique index on <group_key> compares NUMERICALLY and therefore
rejects the pair. The copy loop (lines 2803-2806) tolerates no duplicate at
all:
while (!table->file->ha_rnd_next(new_table.record[1])) {
write_err = new_table.file->ha_write_row(new_table.record[1]);
DBUG_EXECUTE_IF("raise_error", write_err = HA_ERR_FOUND_DUPP_KEY;);
if (write_err) goto err_after_open;
}
Only the single LAST record -- the one that overflowed the heap -- is given
ignore_last_dup treatment a few lines below. Rows already in the MEMORY table
are fatal.
So the two halves of one internal temporary table disagree about equality, and
the handover between them fails. Below the threshold only the first half ever
runs, and you get its answer.
--------------------------------------------------------------------------------
This is the exact pattern of Bug #27643, which Oracle fixed
--------------------------------------------------------------------------------
Bug #27643 (https://bugs.mysql.com/bug.php?id=27643; closed, fixed in 5.0.44 /
5.1.20-beta) was the same shape one value class earlier: HASH indexes on VARCHAR
with binary collations did not ignore trailing spaces, duplicates therefore got
into the internal MEMORY temp table, and the conversion to the on-disk table
then failed on them. Its fix had two parts -- normalize the hash for the
offending value class, and stop masking the conversion error (it was being
reported as "table is full"). Signed zero is the same defect with the same
two-part fix available, and the second part is already done.
--------------------------------------------------------------------------------
Which statements are affected
--------------------------------------------------------------------------------
On trunk 26.10.0, the 102-row table below (100 ordinary values plus the two
zeros; the correct DISTINCT count is 101), spilled at tmp_table_size = 1024,
max_heap_table_size = 16384:
SELECT DISTINCT -> ERROR 1062 ... Duplicate entry '79' ... <group_key>
GROUP BY -> ERROR 1062 ... Duplicate entry '79' ... <group_key>
UNION -> ERROR 1062 ... entry '87' ... <auto_distinct_key>
CREATE TABLE AS -> ERROR 1062 ... Duplicate entry '79' ... <group_key>
count(DISTINCT v) -> 101 correct -- different code path (Unique/tree)
ORDER BY -> 102 row count, correct
plain count(*) -> 102 row count, correct
Every one of the first four returns 102 -- the silent overcount -- when the same
table is run with tmp_table_size = 67108864 instead, so this is one defect with
two presentations and not several.
count(DISTINCT v) being correct while SELECT DISTINCT v cannot be executed at
all, on the same column of the same table in the same session, is the sharpest
statement of the problem.
The identical matrix was also measured on a 4002-row table at
tmp_table_size = 16384 (errors on the same four, count(DISTINCT) 4001,
count(*) 4002); the small table is given here because it is easier to paste.
--------------------------------------------------------------------------------
The threshold
--------------------------------------------------------------------------------
Bisected on trunk, 4000 ordinary rows plus the two zeros:
fails at and below tmp_table_size = 160528
returns a value at and above = 160780
252 bytes apart, and byte-identical to the bisection recorded on 9.4.0.
Expressed as rows at tmp_table_size = 16384:
ordinary rows 10 100 200 400 800 4000
result 12 102 202 ERROR ERROR ERROR
correct 11 101 201 401 801 4001
At tmp_table_size = 1024, max_heap_table_size = 16384 (both documented
minima-adjacent values), the boundary is at 79 ordinary rows: 78 returns 80
where 79 is correct, 79 fails. An 81-row table is enough to trigger the hard
failure.
--------------------------------------------------------------------------------
Controls
--------------------------------------------------------------------------------
Each of these makes the failure disappear, which isolates the trigger.
Measured on the 4002-row table (correct DISTINCT count 4001):
control result
--------------------------------------------------- -------------------------
0.25 and 0.75 instead of the two zeros, same row 4002 at both sizes,
count and sizes no error
internal_tmp_mem_storage_engine = TempTable (the 4001 -- correct at every
shipped default) size
tmp_table_size large enough to avoid the spill 4002 (wrong, no error)
Deterministic: 5/5 identical runs.
--------------------------------------------------------------------------------
Scope
--------------------------------------------------------------------------------
The shipped default is safe. internal_tmp_mem_storage_engine = TempTable has
been the default since 8.0.16 and returns the correct 4001 at every size.
MEMORY remains a permitted, documented value of that variable on current trunk
-- sql/sys_vars.cc:5187 still lists {"MEMORY", "TempTable"} with no deprecation
marker -- and it is still chosen by deployments that hit TempTable's own memory
behaviour.
How to repeat:
Needs no data files -- a recursive CTE builds the table. Against a stock
server, 102 rows:
CREATE DATABASE IF NOT EXISTS z1; USE z1;
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
SET SESSION cte_max_recursion_depth = 100000;
DROP TABLE IF EXISTS r;
CREATE TABLE r(v DOUBLE);
INSERT INTO r VALUES (-0e0), (0e0);
INSERT INTO r WITH RECURSIVE s(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 100)
SELECT n FROM s;
SELECT count(*) AS rows_stored, count(DISTINCT v) AS correct_distinct
FROM r;
-- rows_stored 102, correct_distinct 101
-- A. stays in memory: silent overcount
SET SESSION tmp_table_size = 67108864, max_heap_table_size = 67108864;
SELECT count(*) AS got FROM (SELECT DISTINCT v FROM r) t;
-- got 102, expected 101
-- B. same data, spills to disk: the query fails
SET SESSION tmp_table_size = 1024, max_heap_table_size = 16384;
SELECT count(*) AS got FROM (SELECT DISTINCT v FROM r) t;
-- ERROR 1062 (23000): Duplicate entry '79' for key
-- '/tmp/#sql1_9_6.<group_key>'
The control, which must be run to show that signed zero is the trigger and not
the spill -- same row count, same sizes, two ordinary values in place of the two
zeros:
DROP TABLE IF EXISTS c;
CREATE TABLE c(v DOUBLE);
INSERT INTO c VALUES (0.25), (0.75);
INSERT INTO c WITH RECURSIVE s(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 100)
SELECT n FROM s;
SET SESSION tmp_table_size = 1024, max_heap_table_size = 16384;
SELECT count(*) AS got FROM (SELECT DISTINCT v FROM c) t; -- 102, no error
The statement matrix, on the spilled table. Run each in its own statement --
the first four abort:
SET SESSION internal_tmp_mem_storage_engine = MEMORY;
SET SESSION tmp_table_size = 1024, max_heap_table_size = 16384;
SELECT count(*) FROM (SELECT DISTINCT v FROM r) t;
-- ERROR 1062 '79' <group_key>
SELECT count(*) FROM (SELECT v FROM r GROUP BY v) t;
-- ERROR 1062 '79' <group_key>
SELECT count(*) FROM (SELECT v FROM r UNION SELECT v FROM r) t;
-- ERROR 1062 '87' <auto_distinct_key>
CREATE TABLE ctas AS SELECT DISTINCT v FROM r;
-- ERROR 1062 '79' <group_key>
SELECT count(DISTINCT v) FROM r; -- 101, correct
SELECT count(*) FROM (SELECT v FROM r ORDER BY v) t; -- 102, row count
SELECT count(*) FROM r; -- 102, row count
Raising tmp_table_size to 67108864 turns each of those four errors into the
silent 102.
Smallest case. For SELECT DISTINCT alone the boundary at these settings is
exactly 79 ordinary rows: an 81-row table (79 + the two zeros) fails, an 80-row
table returns 80 where 79 is correct. UNION needs a few more rows because its
temporary table has a different row layout, which is why the paste above uses
100.
Reproduced in the official Docker images:
docker run -d --name my -e MYSQL_ROOT_PASSWORD=root mysql:9 # 9.7.2
docker run -d --name my -e MYSQL_ROOT_PASSWORD=root mysql:8.4 # 8.4.11
docker exec -i my mysql -uroot -proot < repro.sql
and from a source build of trunk:
git clone https://github.com/mysql/mysql-server.git # fcf22d3, 26.10.0
cmake ... -DWITH_BOOST=extra/boost/boost_1_87_0 && make -j64
© 2026, Oracle Corporation and/or its affiliates
Description: Identical behaviour and an identical bisected threshold on all four. This is not a regression; it is a long-standing defect whose second failure mode has not been reported. With internal_tmp_mem_storage_engine = MEMORY, a table holding both -0e0 and 0e0 in a DOUBLE column produces two different wrong outcomes on either side of a size threshold, neither of them the correct answer: tmp_table_size SELECT count(*) FROM (SELECT DISTINCT v FROM r) t ------------------------------ --------------------------------------- large enough to stay in memory 102 -- silently over by one small enough to spill to disk ERROR 1062: Duplicate entry '79' for key '.../#sql....<group_key>' on a 102-row table whose correct DISTINCT count is 101. SELECT -0e0 = 0e0 returns 1, and count(DISTINCT v) returns 101 -- so the server's own answers contradict each other. -------------------------------------------------------------------------------- Mechanism -------------------------------------------------------------------------------- The MEMORY engine's hash index keys on the raw bytes. In storage/heap/hp_hash.cc on current trunk: - hp_hashnr() (line 227, loop at line 282) and hp_rec_hashnr() (line 294, loop at line 342) hash a non-text key segment with a plain byte loop: } else { for (; pos < end; pos++) { nr ^= (uint64)((((uint)nr & 63) + nr2) * ((uint)*pos)) + (nr << 8); nr2 += 3; } } - hp_rec_key_cmp() (line 366, comparison at line 436) compares such a segment with memcmp: } else { if (memcmp(rec1 + seg->start, rec2 + seg->start, seg->length) != 0) return 1; } -0.0 and 0.0 differ in the sign bit, so both the hash and the comparison call them distinct -- this is #67978. Note that the same file already normalizes NaN, but only on the BTREE key path (hp_rb_make_key(), line 558, NaN handling at lines 571-587, "Replace NAN with zero"); signed zero is normalized nowhere, and the hash path normalizes neither. The escalation is in sql/sql_tmp_table.cc. When the MEMORY table outgrows tmp_table_size, create_ondisk_from_heap() copies its rows into an on-disk InnoDB table whose unique index on <group_key> compares NUMERICALLY and therefore rejects the pair. The copy loop (lines 2803-2806) tolerates no duplicate at all: while (!table->file->ha_rnd_next(new_table.record[1])) { write_err = new_table.file->ha_write_row(new_table.record[1]); DBUG_EXECUTE_IF("raise_error", write_err = HA_ERR_FOUND_DUPP_KEY;); if (write_err) goto err_after_open; } Only the single LAST record -- the one that overflowed the heap -- is given ignore_last_dup treatment a few lines below. Rows already in the MEMORY table are fatal. So the two halves of one internal temporary table disagree about equality, and the handover between them fails. Below the threshold only the first half ever runs, and you get its answer. -------------------------------------------------------------------------------- This is the exact pattern of Bug #27643, which Oracle fixed -------------------------------------------------------------------------------- Bug #27643 (https://bugs.mysql.com/bug.php?id=27643; closed, fixed in 5.0.44 / 5.1.20-beta) was the same shape one value class earlier: HASH indexes on VARCHAR with binary collations did not ignore trailing spaces, duplicates therefore got into the internal MEMORY temp table, and the conversion to the on-disk table then failed on them. Its fix had two parts -- normalize the hash for the offending value class, and stop masking the conversion error (it was being reported as "table is full"). Signed zero is the same defect with the same two-part fix available, and the second part is already done. -------------------------------------------------------------------------------- Which statements are affected -------------------------------------------------------------------------------- On trunk 26.10.0, the 102-row table below (100 ordinary values plus the two zeros; the correct DISTINCT count is 101), spilled at tmp_table_size = 1024, max_heap_table_size = 16384: SELECT DISTINCT -> ERROR 1062 ... Duplicate entry '79' ... <group_key> GROUP BY -> ERROR 1062 ... Duplicate entry '79' ... <group_key> UNION -> ERROR 1062 ... entry '87' ... <auto_distinct_key> CREATE TABLE AS -> ERROR 1062 ... Duplicate entry '79' ... <group_key> count(DISTINCT v) -> 101 correct -- different code path (Unique/tree) ORDER BY -> 102 row count, correct plain count(*) -> 102 row count, correct Every one of the first four returns 102 -- the silent overcount -- when the same table is run with tmp_table_size = 67108864 instead, so this is one defect with two presentations and not several. count(DISTINCT v) being correct while SELECT DISTINCT v cannot be executed at all, on the same column of the same table in the same session, is the sharpest statement of the problem. The identical matrix was also measured on a 4002-row table at tmp_table_size = 16384 (errors on the same four, count(DISTINCT) 4001, count(*) 4002); the small table is given here because it is easier to paste. -------------------------------------------------------------------------------- The threshold -------------------------------------------------------------------------------- Bisected on trunk, 4000 ordinary rows plus the two zeros: fails at and below tmp_table_size = 160528 returns a value at and above = 160780 252 bytes apart, and byte-identical to the bisection recorded on 9.4.0. Expressed as rows at tmp_table_size = 16384: ordinary rows 10 100 200 400 800 4000 result 12 102 202 ERROR ERROR ERROR correct 11 101 201 401 801 4001 At tmp_table_size = 1024, max_heap_table_size = 16384 (both documented minima-adjacent values), the boundary is at 79 ordinary rows: 78 returns 80 where 79 is correct, 79 fails. An 81-row table is enough to trigger the hard failure. -------------------------------------------------------------------------------- Controls -------------------------------------------------------------------------------- Each of these makes the failure disappear, which isolates the trigger. Measured on the 4002-row table (correct DISTINCT count 4001): control result --------------------------------------------------- ------------------------- 0.25 and 0.75 instead of the two zeros, same row 4002 at both sizes, count and sizes no error internal_tmp_mem_storage_engine = TempTable (the 4001 -- correct at every shipped default) size tmp_table_size large enough to avoid the spill 4002 (wrong, no error) Deterministic: 5/5 identical runs. -------------------------------------------------------------------------------- Scope -------------------------------------------------------------------------------- The shipped default is safe. internal_tmp_mem_storage_engine = TempTable has been the default since 8.0.16 and returns the correct 4001 at every size. MEMORY remains a permitted, documented value of that variable on current trunk -- sql/sys_vars.cc:5187 still lists {"MEMORY", "TempTable"} with no deprecation marker -- and it is still chosen by deployments that hit TempTable's own memory behaviour. How to repeat: Needs no data files -- a recursive CTE builds the table. Against a stock server, 102 rows: CREATE DATABASE IF NOT EXISTS z1; USE z1; SET SESSION internal_tmp_mem_storage_engine = MEMORY; SET SESSION cte_max_recursion_depth = 100000; DROP TABLE IF EXISTS r; CREATE TABLE r(v DOUBLE); INSERT INTO r VALUES (-0e0), (0e0); INSERT INTO r WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 100) SELECT n FROM s; SELECT count(*) AS rows_stored, count(DISTINCT v) AS correct_distinct FROM r; -- rows_stored 102, correct_distinct 101 -- A. stays in memory: silent overcount SET SESSION tmp_table_size = 67108864, max_heap_table_size = 67108864; SELECT count(*) AS got FROM (SELECT DISTINCT v FROM r) t; -- got 102, expected 101 -- B. same data, spills to disk: the query fails SET SESSION tmp_table_size = 1024, max_heap_table_size = 16384; SELECT count(*) AS got FROM (SELECT DISTINCT v FROM r) t; -- ERROR 1062 (23000): Duplicate entry '79' for key -- '/tmp/#sql1_9_6.<group_key>' The control, which must be run to show that signed zero is the trigger and not the spill -- same row count, same sizes, two ordinary values in place of the two zeros: DROP TABLE IF EXISTS c; CREATE TABLE c(v DOUBLE); INSERT INTO c VALUES (0.25), (0.75); INSERT INTO c WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 100) SELECT n FROM s; SET SESSION tmp_table_size = 1024, max_heap_table_size = 16384; SELECT count(*) AS got FROM (SELECT DISTINCT v FROM c) t; -- 102, no error The statement matrix, on the spilled table. Run each in its own statement -- the first four abort: SET SESSION internal_tmp_mem_storage_engine = MEMORY; SET SESSION tmp_table_size = 1024, max_heap_table_size = 16384; SELECT count(*) FROM (SELECT DISTINCT v FROM r) t; -- ERROR 1062 '79' <group_key> SELECT count(*) FROM (SELECT v FROM r GROUP BY v) t; -- ERROR 1062 '79' <group_key> SELECT count(*) FROM (SELECT v FROM r UNION SELECT v FROM r) t; -- ERROR 1062 '87' <auto_distinct_key> CREATE TABLE ctas AS SELECT DISTINCT v FROM r; -- ERROR 1062 '79' <group_key> SELECT count(DISTINCT v) FROM r; -- 101, correct SELECT count(*) FROM (SELECT v FROM r ORDER BY v) t; -- 102, row count SELECT count(*) FROM r; -- 102, row count Raising tmp_table_size to 67108864 turns each of those four errors into the silent 102. Smallest case. For SELECT DISTINCT alone the boundary at these settings is exactly 79 ordinary rows: an 81-row table (79 + the two zeros) fails, an 80-row table returns 80 where 79 is correct. UNION needs a few more rows because its temporary table has a different row layout, which is why the paste above uses 100. Reproduced in the official Docker images: docker run -d --name my -e MYSQL_ROOT_PASSWORD=root mysql:9 # 9.7.2 docker run -d --name my -e MYSQL_ROOT_PASSWORD=root mysql:8.4 # 8.4.11 docker exec -i my mysql -uroot -proot < repro.sql and from a source build of trunk: git clone https://github.com/mysql/mysql-server.git # fcf22d3, 26.10.0 cmake ... -DWITH_BOOST=extra/boost/boost_1_87_0 && make -j64