Description:
A query using GROUP BY ... HAVING returns one row, but an equivalent derived-table rewrite that materializes the HAVING predicate as a projected column and filters it in an outer WHERE returns an empty set.
This indicates a wrong-result bug in the optimizer or executor when handling a materialized HAVING predicate across a derived table boundary.
This behavior was observed on MySQL, whereas TiDB v8.5.5 and MariaDB 11.8.5 handles both queries consistently, returning 1 row (null) for both.
How to repeat:
-------------------------------- To Reproduce --------------------------------
CREATE TABLE t1(c0 FLOAT, c1 FLOAT) ;
INSERT INTO t1(c0) VALUES(0.6809136682939025);
INSERT INTO t1(c0) VALUES(NULL);
INSERT INTO t1(c0) VALUES(0);
CREATE UNIQUE INDEX i1 USING HASH ON t1(c1, c0);
-- cardinality: 1
SELECT DISTINCT CONCAT(t1.c1) FROM t1 WHERE (NOT ((t1.c0) IS NOT FALSE)) GROUP BY t1.c1 HAVING ((MAX(DISTINCT t1.c0)) >= (-1214742575));
-- cardinality: 0
SELECT DISTINCT ref0 FROM (SELECT CONCAT(t1.c1) AS ref0, (((MAX(DISTINCT t1.c0)) >= (-1214742575))) AS ref1 FROM t1 WHERE (NOT ((t1.c0) IS NOT FALSE)) GROUP BY t1.c1) AS s WHERE ref1;
-------------------------------- What did I expect to see? --------------------------------
mysql> SELECT DISTINCT CONCAT(t1.c1)
-> FROM t1
-> WHERE (NOT ((t1.c0) IS NOT FALSE))
-> GROUP BY t1.c1
-> HAVING ((MAX(DISTINCT t1.c0)) >= (-1214742575))
-> || ((MAX(DISTINCT t1.c0)) < (-506330848));
Empty set, 1 warning (0.00 sec)
mysql>
mysql> SELECT DISTINCT ref0
-> FROM (
-> SELECT CONCAT(t1.c1) AS ref0,
-> (((MAX(DISTINCT t1.c0)) >= (-1214742575))
-> || ((MAX(DISTINCT t1.c0)) < (-506330848))) AS ref1
-> FROM t1
-> WHERE (NOT ((t1.c0) IS NOT FALSE))
-> GROUP BY t1.c1
-> ) AS s
-> WHERE ref1;
Empty set, 1 warning (0.00 sec)
-------------------------------- What did I see instead --------------------------------
mysql> -- Query 1: returns 1 row
mysql> SELECT DISTINCT CONCAT(t1.c1)
-> FROM t1
-> WHERE (NOT ((t1.c0) IS NOT FALSE))
-> GROUP BY t1.c1
-> HAVING ((MAX(DISTINCT t1.c0)) >= (-1214742575))
-> || ((MAX(DISTINCT t1.c0)) < (-506330848));
+---------------+
| CONCAT(t1.c1) |
+---------------+
| NULL |
+---------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> -- Query 2: unexpectedly returns 0 rows
mysql> SELECT DISTINCT ref0
-> FROM (
-> SELECT CONCAT(t1.c1) AS ref0,
-> (((MAX(DISTINCT t1.c0)) >= (-1214742575))
-> || ((MAX(DISTINCT t1.c0)) < (-506330848))) AS ref1
-> FROM t1
-> WHERE (NOT ((t1.c0) IS NOT FALSE))
-> GROUP BY t1.c1
-> ) AS s
-> WHERE ref1;
Empty set, 1 warning (0.00 sec)
Description: A query using GROUP BY ... HAVING returns one row, but an equivalent derived-table rewrite that materializes the HAVING predicate as a projected column and filters it in an outer WHERE returns an empty set. This indicates a wrong-result bug in the optimizer or executor when handling a materialized HAVING predicate across a derived table boundary. This behavior was observed on MySQL, whereas TiDB v8.5.5 and MariaDB 11.8.5 handles both queries consistently, returning 1 row (null) for both. How to repeat: -------------------------------- To Reproduce -------------------------------- CREATE TABLE t1(c0 FLOAT, c1 FLOAT) ; INSERT INTO t1(c0) VALUES(0.6809136682939025); INSERT INTO t1(c0) VALUES(NULL); INSERT INTO t1(c0) VALUES(0); CREATE UNIQUE INDEX i1 USING HASH ON t1(c1, c0); -- cardinality: 1 SELECT DISTINCT CONCAT(t1.c1) FROM t1 WHERE (NOT ((t1.c0) IS NOT FALSE)) GROUP BY t1.c1 HAVING ((MAX(DISTINCT t1.c0)) >= (-1214742575)); -- cardinality: 0 SELECT DISTINCT ref0 FROM (SELECT CONCAT(t1.c1) AS ref0, (((MAX(DISTINCT t1.c0)) >= (-1214742575))) AS ref1 FROM t1 WHERE (NOT ((t1.c0) IS NOT FALSE)) GROUP BY t1.c1) AS s WHERE ref1; -------------------------------- What did I expect to see? -------------------------------- mysql> SELECT DISTINCT CONCAT(t1.c1) -> FROM t1 -> WHERE (NOT ((t1.c0) IS NOT FALSE)) -> GROUP BY t1.c1 -> HAVING ((MAX(DISTINCT t1.c0)) >= (-1214742575)) -> || ((MAX(DISTINCT t1.c0)) < (-506330848)); Empty set, 1 warning (0.00 sec) mysql> mysql> SELECT DISTINCT ref0 -> FROM ( -> SELECT CONCAT(t1.c1) AS ref0, -> (((MAX(DISTINCT t1.c0)) >= (-1214742575)) -> || ((MAX(DISTINCT t1.c0)) < (-506330848))) AS ref1 -> FROM t1 -> WHERE (NOT ((t1.c0) IS NOT FALSE)) -> GROUP BY t1.c1 -> ) AS s -> WHERE ref1; Empty set, 1 warning (0.00 sec) -------------------------------- What did I see instead -------------------------------- mysql> -- Query 1: returns 1 row mysql> SELECT DISTINCT CONCAT(t1.c1) -> FROM t1 -> WHERE (NOT ((t1.c0) IS NOT FALSE)) -> GROUP BY t1.c1 -> HAVING ((MAX(DISTINCT t1.c0)) >= (-1214742575)) -> || ((MAX(DISTINCT t1.c0)) < (-506330848)); +---------------+ | CONCAT(t1.c1) | +---------------+ | NULL | +---------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> -- Query 2: unexpectedly returns 0 rows mysql> SELECT DISTINCT ref0 -> FROM ( -> SELECT CONCAT(t1.c1) AS ref0, -> (((MAX(DISTINCT t1.c0)) >= (-1214742575)) -> || ((MAX(DISTINCT t1.c0)) < (-506330848))) AS ref1 -> FROM t1 -> WHERE (NOT ((t1.c0) IS NOT FALSE)) -> GROUP BY t1.c1 -> ) AS s -> WHERE ref1; Empty set, 1 warning (0.00 sec)