Bug #120262 CREATE VIEW materialization changes INTERSECT result cardinality for an aggregate branch

• Submitted: 14 Apr 2:54
• Modified: 14 Apr 4:38
• Reporter: Peiyuan Liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.4.8
• OS: Any
• CPU Architecture: Any

[14 Apr 2:54] Peiyuan Liu

Description:
Materializing the left aggregate branch of an `INTERSECT` as a view changes the final result set even though the view definition is textually equivalent to that branch.The original query intersects `AVG(c14)` with the base-row value `c14` and correctly returns one row, but the transformed query returns zero rows after exposing the aggregate branch through a view.  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 t2 (
  c14 FLOAT(8,2)
);

INSERT INTO t2 VALUES (83.63);

SELECT AVG(c14) AS col_2
FROM t2
INTERSECT
SELECT c14 AS col_2
FROM t2;

CREATE VIEW V_t2_10 AS
SELECT AVG(c14) AS col_2
FROM t2;

SELECT col_2
FROM V_t2_10
INTERSECT
SELECT c14 AS col_2
FROM t2;

Original result:
+-----------+
| col_2     |
+-----------+
| 83.629997 |
+-----------+

After create view, result:
+-------+
| col_2 |
+-------+
+-------+

[14 Apr 4:38] Chaithra Marsur Gopala Reddy

Hi Peiyuan Liu,

Thank you for the test case. Verified as described.