Bug #120248 CREATE VIEW materialization empties a CROSS JOIN aggregation result set

• Submitted: 11 Apr 9:16
• Reporter: Peiyuan Liu
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.4.8
• OS: Ubuntu
• CPU Architecture: Any

[11 Apr 9:16] Peiyuan Liu

Description:
Materializing a semantically equivalent filtered table as a view changes the result of a grouped CROSS JOIN query. The predicate only references table `t3`, so replacing `t3` with a view defined by the same filter should preserve the query result, but it does not.

This issue may be related to the https://bugs.mysql.com/bug.php?id=120247 bug，but since this case uses CROSS JOIN plus HAVING, We choose to report it separately.

How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;

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);

SELECT t3.c2 AS c2, t2.c7 AS c7, COUNT(DISTINCT t2.c8) AS col_1
FROM t2 AS t2 CROSS JOIN t3 AS t3 ON (t3.c3 = t2.c1)
WHERE t3.c2 IS NOT NULL
GROUP BY t3.c2, t2.c7
HAVING (MIN(t2.c11) >= 6)
ORDER BY t2.c7 DESC;

CREATE VIEW V_t3_7e0b8e91 AS
SELECT * FROM t3 AS t3 WHERE (t3.c2 IS NOT NULL);

SELECT V_t3_7e0b8e91.c2 AS c2, t2.c7 AS c7, 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;

Original result:

+------+----+-------+
| c2   | c7 | col_1 |
+------+----+-------+
| 6342 | x  | 1     |
+------+----+-------+

After create view, result:

+----+----+-------+
| c2 | c7 | col_1 |
+----+----+-------+
+----+----+-------+