Bug #121481 Wrong result: INTERSECT/EXCEPT and DISTINCT/GROUP BY/UNION treat 0 and -0 as distinct although 0 = -0

• Submitted: 11 Oct 15:45
• Modified: 13 Oct 8:16
• Reporter: Ann Calla
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.1
• OS: Ubuntu (24.04)
• CPU Architecture: x86 (x86_64)

[11 Oct 15:45] Ann Calla

Description:
0e0 = -0e0 is true, and UNION of the two literals returns one row, but:
 - INTERSECT of the two literals returns no row, and EXCEPT returns a row;
 - for a table with a TEXT column and a FLOAT column holding 0 and -0, SELECT DISTINCT, GROUP BY and UNION over both columns return two rows, at default settings.
SELECT DISTINCT over the FLOAT column alone returns one row.

INTERSECT stays wrong with optimizer_switch='hash_set_operations=off'.

Bug #67978 describes signed zero in MEMORY HASH indexes and GROUP BY; it does not mention INTERSECT, EXCEPT, DISTINCT or UNION, which is why this is filed separately.

How to repeat:
SELECT 0e0 = -0e0;                    -- 1
SELECT 0e0 UNION SELECT -0e0;         -- 1 row (correct)
SELECT 0e0 INTERSECT SELECT -0e0;
-- expected: 1 row
-- actual:   empty
SELECT 0e0 EXCEPT SELECT -0e0;
-- expected: empty
-- actual:   0

CREATE TABLE t(a TINYTEXT, b FLOAT);
INSERT INTO t(b) VALUES (0), (-0e0);

SELECT DISTINCT b FROM t;             -- 1 row (correct)
SELECT DISTINCT a, b FROM t;
-- expected: 1 row
-- actual:   (NULL, 0), (NULL, -0)
SELECT a, b FROM t GROUP BY a, b;     -- same: 2 rows instead of 1
SELECT a, b FROM t UNION SELECT a, b FROM t;   -- same: 2 rows instead of 1

[13 Oct 5:41] Chaithra Marsur Gopala Reddy

Hi Ann Calla,

Thank you for the test case. Verified as described.

[13 Oct 8:16] Knut Anders Hatlen

This appears to describe the same problem as bug#118863. Closing as duplicate.