Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8, 12.2, 11.8.5
-
None
-
OS: Ubuntu 24.04
DBMS: MariaDB
Docker image: mariadb:11.8.5
Image ID: bfe9184ea9e5
Description
- Summary
Two logically equivalent queries that differ only in the placement of a constant-true predicate (HAVING vs. outer WHERE) return different cardinalities:
Query A (HAVING version): returns 0 rows
Query B (subquery + WHERE version): returns 1 row
This indicates that relocating a HAVING predicate into a materialized derived table is not semantics-preserving, especially in the presence of NOT IN and COUNT() under GROUP BY.
mysql> -- cardinality: 0
|
mysql> SELECT t1.c1 AS g0, t0.c0 AS g1, t0.c1 AS g2 FROM t1 STRAIGHT_JOIN t0 ON ('pt tw' NOT IN ((- 1873356510), true)) GROUP BY t1.c1, t0.c0, t0.c1 HAVING ((COUNT(g0) < 464443616) AND (g2 NOT IN (g0, (g0 NOT IN (g1, true, g2)))));
|
Empty set, 1 warning (0.00 sec)
|
|
|
mysql> -- cardinality: 1
|
mysql> SELECT g0, g1, g2 FROM (SELECT t1.c1 AS g0, t0.c0 AS g1, t0.c1 AS g2, (((COUNT(t1.c1) < 464443616) AND (t0.c1 NOT IN (t1.c1, (t1.c1 NOT IN (t0.c0, true, t0.c1)))))) AS ref1 FROM t1 STRAIGHT_JOIN t0 ON ('pt tw' NOT IN ((- 1873356510), true)) GROUP BY t1.c1, t0.c0, t0.c1) AS s WHERE ref1;
|
+-------------+------------+------------+
|
| g0 | g1 | g2 |
|
+-------------+------------+------------+
|
| -1732148813 | 1753580383 | 1340049994 |
|
+-------------+------------+------------+
|
1 row in set, 1 warning (0.00 sec)
|
However, our tests in MySQL revealed that both Query1 and Query2 returned identical results.
mysql> SELECT t1.c1 AS g0, t0.c0 AS g1, t0.c1 AS g2 FROM t1 STRAIGHT_JOIN t0 ON ('pt tw' NOT IN ((- 1873356510), true)) GROUP BY t1.c1, t0.c0, t0.c1 HAVING ((COUNT(g0) < 464443616) AND (g2 NOT IN (g0, (g0 NOT IN (g1, true, g2)))));
|
+-------------+------------+------------+
|
| g0 | g1 | g2 |
|
+-------------+------------+------------+
|
| -1732148813 | 1753580383 | 1340049994 |
|
+-------------+------------+------------+
|
1 row in set, 1 warning (0.01 sec)
|
|
|
mysql> -- cardinality: 1
|
mysql> SELECT g0, g1, g2 FROM (SELECT t1.c1 AS g0, t0.c0 AS g1, t0.c1 AS g2, (((COUNT(t1.c1) < 464443616) AND (t0.c1 NOT IN (t1.c1, (t1.c1 NOT IN (t0.c0, true, t0.c1)))))) AS ref1 FROM t1 STRAIGHT_JOIN t0 ON ('pt tw' NOT IN ((- 1873356510), true)) GROUP BY t1.c1, t0.c0, t0.c1) AS s WHERE ref1;
|
+-------------+------------+------------+
|
| g0 | g1 | g2 |
|
+-------------+------------+------------+
|
| -1732148813 | 1753580383 | 1340049994 |
|
+-------------+------------+------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
mysql>
|
mysql> select version();
|
+-----------+
|
| version() |
|
+-----------+
|
| 8.0.36 |
|
+-----------+
|
1 row in set (0.00 sec)
|
- Minimal Reproduction Steps
CREATE TABLE t0(c0 VARCHAR(100) PRIMARY KEY, c1 INT UNIQUE);CREATE TABLE t1 LIKE t0;INSERT INTO t1 VALUES ('', -1732148813);CREATE UNIQUE INDEX ic0 USING BTREE ON t1(c0 DESC);INSERT INTO t0 VALUES ('1753580383', 1340049994);-- cardinality: 0SELECT t1.c1 AS g0, t0.c0 AS g1, t0.c1 AS g2 FROM t1 STRAIGHT_JOIN t0 ON ('pt tw' NOT IN ((- 1873356510), true)) GROUP BY t1.c1, t0.c0, t0.c1 HAVING ((COUNT(g0) < 464443616) AND (g2 NOT IN (g0, (g0 NOT IN (g1, true, g2)))));-- cardinality: 1SELECT g0, g1, g2 FROM (SELECT t1.c1 AS g0, t0.c0 AS g1, t0.c1 AS g2, (((COUNT(t1.c1) < 464443616) AND (t0.c1 NOT IN (t1.c1, (t1.c1 NOT IN (t0.c0, true, t0.c1)))))) AS ref1 FROM t1 STRAIGHT_JOIN t0 ON ('pt tw' NOT IN ((- 1873356510), true)) GROUP BY t1.c1, t0.c0, t0.c1) AS s WHERE ref1;