Bug #120247 CREATE VIEW materialization changes RIGHT JOIN preserved-side filtering result cardinality

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

[11 Apr 9:03] Peiyuan Liu

Description:
Materializing the filtered preserved side of a RIGHT JOIN as a view changes the final grouped result cardinality. The predicate only references table `t3`, so replacing `t3` with a view defined by the same filter should preserve the result set, but it does not. Logically, the original SQL and the view-based rewritten SQL are equivalent, but they produce different results.

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

CREATE TABLE t1 (
  c1 INT,
  c4 INT
);

CREATE TABLE t3 (
  c2 INT,
  c9 INT
);

INSERT INTO t1 VALUES (697, 16);

INSERT INTO t3 VALUES
  (697, 71),
  (697, 81);

SELECT DISTINCT EXP(t3.c9) AS col_1, t1.c4 AS c4
FROM t1 AS t1 RIGHT JOIN t3 AS t3 ON (t3.c2 = t1.c1)
WHERE (EXP(t3.c9) > 74)
GROUP BY EXP(t3.c9), t1.c4
ORDER BY t1.c4 ASC;

CREATE VIEW V_t3_34b1c811 AS
SELECT * FROM t3 AS t3 WHERE (EXP(t3.c9) > 74);

SELECT DISTINCT EXP(V_t3_34b1c811.c9) AS col_1, t1.c4 AS c4
FROM t1 RIGHT JOIN V_t3_34b1c811 ON (V_t3_34b1c811.c2 = t1.c1)
GROUP BY t1.c4, EXP(V_t3_34b1c811.c9)
ORDER BY t1.c4;

Original result:

+------------------------+----+
| col_1                  | c4 |
+------------------------+----+
| 6.837671229762744e+30  | 16 |
| 1.5060973145850306e+35 | 16 |
+------------------------+----+

After create view, result:

+-----------------------+----+
| col_1                 | c4 |
+-----------------------+----+
| 6.837671229762744e+30 | 16 |
+-----------------------+----+