Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 11.4.10
Description
Under semantic-equivalent rewriting, extracting the filtered `t3` relation into a view causes a stable row-count mismatch.
The source and mutated queries are logically equivalent, but produce different results.
How to repeat:
CREATE TABLE t2 ( |
c1 INT, |
c7 CHAR(1), |
c8 BINARY(1), |
c11 SET('a','b','c','d') |
);
|
CREATE TABLE t3 ( |
c2 INT, |
c3 INT |
);
|
INSERT INTO t2 VALUES (3580, 'x', X'01', 'a,b,d'); |
INSERT INTO t3 VALUES (6342, 3580); |
|
|
-- Source Original SQL
|
SELECT COUNT(DISTINCT t2.c8) AS col_1 FROM t2 AS t2 CROSS JOIN t3 AS t3 ON (t3.c3 = t2.c1) WHERE NOT t3.c2 IS NULL GROUP BY t3.c2, t2.c7 HAVING (MIN(t2.c11) >= 6) ORDER BY t2.c7 DESC; |
|
|
-- View SQL
|
CREATE VIEW V_t3_7e0b8e91 AS |
SELECT * FROM t3 AS t3 WHERE (t3.c2 IS NOT NULL); |
|
|
-- Mutated SQL
|
SELECT COUNT(DISTINCT t2.c8) AS col_1 FROM t2 CROSS JOIN V_t3_7e0b8e91 ON (V_t3_7e0b8e91.c3 = t2.c1) GROUP BY t2.c7, V_t3_7e0b8e91.c2 HAVING MIN(t2.c11) >= 6 ORDER BY t2.c7 DESC; |
Observed result - Query A (original):
| col_1 |
|---|
| 1 |
Observed result - Query B (mutated):
Empty set
Attachments
Issue Links
- is duplicated by
-
MDEV-39735 CREATE VIEW extraction causes row loss for grouped aggregate with `HAVING MIN(SET/ENUM) >= const`
-
- Closed
-