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

• Submitted: 14 Apr 2:54
• Modified: 16 Apr 20:55
• Reporter: Peiyuan Liu
• Status: Not a Bug
• 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.

[16 Apr 20:55] Roy Lyseng

Actually, this is not a bug.

Float data are approximate, and there are no guarantees that equalities work.
Also, using float precision and decimal may enforce different rounding and truncation operations. These are also deprecated features and there are better alternatives.

Workaround; Use DECIMAL data type which has more exact arithmetic.