Description:
A query using a VIEW returns a different result from an equivalent query using a CTE.
The query involves:
DECIMAL(62, 11)
AVG(b)
GROUP BY
comparison between the grouped value and its aggregate: d < c
outer UNION
VIEW vs CTE execution
The VIEW query returns 1 row, while the equivalent CTE query returns 2 rows.
This appears to be a wrong-result bug, possibly related to CTE materialization, AVG(DECIMAL) precision handling, aggregate evaluation, or comparison between DECIMAL values.
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
b DECIMAL(62, 11)
);
INSERT INTO a VALUES (
'-324496720440747619363574210177526687173690420725404.47046752783'
);
CREATE OR REPLACE VIEW v0 AS
SELECT
AVG(b) AS c,
b AS d
FROM a
GROUP BY d;
-- Query using VIEW
SELECT 0
FROM v0
UNION
SELECT COUNT(*)
FROM v0
WHERE d < c;
-- Equivalent query using CTE
WITH CTE AS (
SELECT
AVG(b) AS c,
b AS d
FROM a
GROUP BY d
)
SELECT 0
FROM CTE
UNION
SELECT COUNT(*)
FROM CTE
WHERE d < c;
Actual result
The VIEW query returns 1 row:
+---+
| 0 |
+---+
| 0 |
+---+
1 row in set
The equivalent CTE query returns 2 rows:
+---+
| 0 |
+---+
| 0 |
| 1 |
+---+
2 rows in set
Expected result
Both queries should return the same result.
The expected result appears to be 1 row:
+---+
| 0 |
+---+
| 0 |
+---+
Suggested fix:
Reasoning
The table contains exactly one row:
b
----------------------------------------------------------------
-324496720440747619363574210177526687173690420725404.47046752783
The VIEW/CTE body is:
SELECT
AVG(b) AS c,
b AS d
FROM a
GROUP BY d;
Since there is only one row in the group, AVG(b) should be numerically equal to b.
Therefore the VIEW/CTE body should produce one row where:
c = d
The predicate:
WHERE d < c
should not evaluate to TRUE, because d and c should be equal.
Therefore:
SELECT COUNT(*)
FROM v0
WHERE d < c;
should return 0.
The outer query:
SELECT 0
FROM v0
UNION
SELECT COUNT(*)
FROM v0
WHERE d < c;
should therefore return only one distinct value: 0.
The VIEW query returns the expected result. The CTE query returns an additional row 1, which suggests that the CTE path incorrectly evaluates d < c as TRUE.
Description: A query using a VIEW returns a different result from an equivalent query using a CTE. The query involves: DECIMAL(62, 11) AVG(b) GROUP BY comparison between the grouped value and its aggregate: d < c outer UNION VIEW vs CTE execution The VIEW query returns 1 row, while the equivalent CTE query returns 2 rows. This appears to be a wrong-result bug, possibly related to CTE materialization, AVG(DECIMAL) precision handling, aggregate evaluation, or comparison between DECIMAL values. How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE a ( b DECIMAL(62, 11) ); INSERT INTO a VALUES ( '-324496720440747619363574210177526687173690420725404.47046752783' ); CREATE OR REPLACE VIEW v0 AS SELECT AVG(b) AS c, b AS d FROM a GROUP BY d; -- Query using VIEW SELECT 0 FROM v0 UNION SELECT COUNT(*) FROM v0 WHERE d < c; -- Equivalent query using CTE WITH CTE AS ( SELECT AVG(b) AS c, b AS d FROM a GROUP BY d ) SELECT 0 FROM CTE UNION SELECT COUNT(*) FROM CTE WHERE d < c; Actual result The VIEW query returns 1 row: +---+ | 0 | +---+ | 0 | +---+ 1 row in set The equivalent CTE query returns 2 rows: +---+ | 0 | +---+ | 0 | | 1 | +---+ 2 rows in set Expected result Both queries should return the same result. The expected result appears to be 1 row: +---+ | 0 | +---+ | 0 | +---+ Suggested fix: Reasoning The table contains exactly one row: b ---------------------------------------------------------------- -324496720440747619363574210177526687173690420725404.47046752783 The VIEW/CTE body is: SELECT AVG(b) AS c, b AS d FROM a GROUP BY d; Since there is only one row in the group, AVG(b) should be numerically equal to b. Therefore the VIEW/CTE body should produce one row where: c = d The predicate: WHERE d < c should not evaluate to TRUE, because d and c should be equal. Therefore: SELECT COUNT(*) FROM v0 WHERE d < c; should return 0. The outer query: SELECT 0 FROM v0 UNION SELECT COUNT(*) FROM v0 WHERE d < c; should therefore return only one distinct value: 0. The VIEW query returns the expected result. The CTE query returns an additional row 1, which suggests that the CTE path incorrectly evaluates d < c as TRUE.