Description:
A query over a VIEW or CTE incorrectly returns an empty result when an equality predicate is applied to a SUM(DOUBLE) aggregate value.
For the group where SIGN(c1) = 1, the aggregate value is:
SUM(c0) = 2.363159231809063e+30
Therefore, the following predicate should match that group:
vc_1 = 2.363159231809063e+30
AND vc_0 <> 0
However, both the VIEW and CTE queries incorrectly reject the row and return an empty result.
When the same aggregate query is materialized into a TEMPORARY TABLE first, the predicate correctly returns:
(1, 2.363159231809063e30)
The wrong result appears to occur when the predicate is evaluated directly against the aggregate expression in a VIEW or CTE. It may be related to predicate pushdown into HAVING, evaluation of SUM(DOUBLE), or comparison of the aggregate value before final materialization.
How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS temp_t;
CREATE TABLE t0 (
c0 DOUBLE,
c1 TINYINT
);
INSERT INTO t0 (c0, c1) VALUES
(2.1498754579837528e+29, 69),
(6.072842930493321e+29, 126),
(NULL, 0),
(0, 0),
(NULL, 0),
(0, NULL),
(0, 0),
(3.939572903036893e+29, 119),
(0, NULL),
(8.41717808718879e+29, 88),
(4.585345453702677e+29, 17),
(0, NULL),
(0, 0),
(0, 0),
(NULL, NULL),
(4.550116764513005e+29, 112),
(-6.0833392788278086e+29, 15);
CREATE VIEW v0 AS
SELECT
SIGN(c1) AS vc_0,
SUM(c0) AS vc_1
FROM t0
GROUP BY vc_0
HAVING COUNT(DISTINCT c0) >= 0;
-- Wrong result: the matching row is incorrectly filtered out.
SELECT *
FROM v0
WHERE vc_1 = 2.363159231809063e+30
AND vc_0 <> 0;
-- Wrong result: the matching row is incorrectly filtered out.
WITH cte AS (
SELECT
SIGN(c1) AS vc_0,
SUM(c0) AS vc_1
FROM t0
GROUP BY vc_0
HAVING COUNT(DISTINCT c0) >= 0
)
SELECT *
FROM cte
WHERE vc_1 = 2.363159231809063e+30
AND vc_0 <> 0;
-- Control query: explicit materialization preserves the matching row.
CREATE TEMPORARY TABLE temp_t AS
SELECT
SIGN(c1) AS vc_0,
SUM(c0) AS vc_1
FROM t0
GROUP BY vc_0
HAVING COUNT(DISTINCT c0) >= 0;
SELECT *
FROM temp_t
WHERE vc_1 = 2.363159231809063e+30
AND vc_0 <> 0;
Actual result:
The VIEW query incorrectly returns:
Empty set
The CTE query incorrectly returns:
Empty set
The TEMPORARY TABLE query returns:
+------+----------------------+
| vc_0 | vc_1 |
+------+----------------------+
| 1 | 2.363159231809063e30 |
+------+----------------------+
1 row in set
Expected result:
The VIEW and CTE queries should return the matching aggregate row:
+------+----------------------+
| vc_0 | vc_1 |
+------+----------------------+
| 1 | 2.363159231809063e30 |
+------+----------------------+
1 row in set
Suggested fix:
SET SESSION optimizer_switch = 'derived_condition_pushdown=off'; The result is correct
Description: A query over a VIEW or CTE incorrectly returns an empty result when an equality predicate is applied to a SUM(DOUBLE) aggregate value. For the group where SIGN(c1) = 1, the aggregate value is: SUM(c0) = 2.363159231809063e+30 Therefore, the following predicate should match that group: vc_1 = 2.363159231809063e+30 AND vc_0 <> 0 However, both the VIEW and CTE queries incorrectly reject the row and return an empty result. When the same aggregate query is materialized into a TEMPORARY TABLE first, the predicate correctly returns: (1, 2.363159231809063e30) The wrong result appears to occur when the predicate is evaluated directly against the aggregate expression in a VIEW or CTE. It may be related to predicate pushdown into HAVING, evaluation of SUM(DOUBLE), or comparison of the aggregate value before final materialization. How to repeat: DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; DROP TABLE IF EXISTS t0; DROP VIEW IF EXISTS v0; DROP TEMPORARY TABLE IF EXISTS temp_t; CREATE TABLE t0 ( c0 DOUBLE, c1 TINYINT ); INSERT INTO t0 (c0, c1) VALUES (2.1498754579837528e+29, 69), (6.072842930493321e+29, 126), (NULL, 0), (0, 0), (NULL, 0), (0, NULL), (0, 0), (3.939572903036893e+29, 119), (0, NULL), (8.41717808718879e+29, 88), (4.585345453702677e+29, 17), (0, NULL), (0, 0), (0, 0), (NULL, NULL), (4.550116764513005e+29, 112), (-6.0833392788278086e+29, 15); CREATE VIEW v0 AS SELECT SIGN(c1) AS vc_0, SUM(c0) AS vc_1 FROM t0 GROUP BY vc_0 HAVING COUNT(DISTINCT c0) >= 0; -- Wrong result: the matching row is incorrectly filtered out. SELECT * FROM v0 WHERE vc_1 = 2.363159231809063e+30 AND vc_0 <> 0; -- Wrong result: the matching row is incorrectly filtered out. WITH cte AS ( SELECT SIGN(c1) AS vc_0, SUM(c0) AS vc_1 FROM t0 GROUP BY vc_0 HAVING COUNT(DISTINCT c0) >= 0 ) SELECT * FROM cte WHERE vc_1 = 2.363159231809063e+30 AND vc_0 <> 0; -- Control query: explicit materialization preserves the matching row. CREATE TEMPORARY TABLE temp_t AS SELECT SIGN(c1) AS vc_0, SUM(c0) AS vc_1 FROM t0 GROUP BY vc_0 HAVING COUNT(DISTINCT c0) >= 0; SELECT * FROM temp_t WHERE vc_1 = 2.363159231809063e+30 AND vc_0 <> 0; Actual result: The VIEW query incorrectly returns: Empty set The CTE query incorrectly returns: Empty set The TEMPORARY TABLE query returns: +------+----------------------+ | vc_0 | vc_1 | +------+----------------------+ | 1 | 2.363159231809063e30 | +------+----------------------+ 1 row in set Expected result: The VIEW and CTE queries should return the matching aggregate row: +------+----------------------+ | vc_0 | vc_1 | +------+----------------------+ | 1 | 2.363159231809063e30 | +------+----------------------+ 1 row in set Suggested fix: SET SESSION optimizer_switch = 'derived_condition_pushdown=off'; The result is correct