Description:
A query using a CTE returns one row, while the equivalent query using a VIEW returns an empty result set. The VIEW result appears to be correct.
The issue seems related to CTE handling of AVG() over a large NUMERIC(62) value. The CTE query appears to produce a saturated/truncated decimal value for AVG(b), which makes the predicate e < c incorrectly evaluate to true.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (b NUMERIC(62));
INSERT INTO a VALUES
('-58785330068238417101100789832031223843466864936328772116867644');
CREATE VIEW v0 AS
SELECT AVG(b) AS c, MIN(b) AS e
FROM a;
SELECT c
FROM (SELECT e FROM v0 WHERE e < c) AS d
JOIN v0;
WITH cte AS (
SELECT AVG(b) AS c, MIN(b) AS e
FROM a
)
SELECT c
FROM (SELECT e FROM cte WHERE e < c) AS d
JOIN cte;
Actual result:
The VIEW query returns an empty result set:
Empty set
The equivalent CTE query returns one row:
+---------------------------------------------------------------------+
| c |
+---------------------------------------------------------------------+
| -9999999999999999999999999999999999999999999999999999999999999.9999 |
+---------------------------------------------------------------------+
Description: A query using a CTE returns one row, while the equivalent query using a VIEW returns an empty result set. The VIEW result appears to be correct. The issue seems related to CTE handling of AVG() over a large NUMERIC(62) value. The CTE query appears to produce a saturated/truncated decimal value for AVG(b), which makes the predicate e < c incorrectly evaluate to true. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE a (b NUMERIC(62)); INSERT INTO a VALUES ('-58785330068238417101100789832031223843466864936328772116867644'); CREATE VIEW v0 AS SELECT AVG(b) AS c, MIN(b) AS e FROM a; SELECT c FROM (SELECT e FROM v0 WHERE e < c) AS d JOIN v0; WITH cte AS ( SELECT AVG(b) AS c, MIN(b) AS e FROM a ) SELECT c FROM (SELECT e FROM cte WHERE e < c) AS d JOIN cte; Actual result: The VIEW query returns an empty result set: Empty set The equivalent CTE query returns one row: +---------------------------------------------------------------------+ | c | +---------------------------------------------------------------------+ | -9999999999999999999999999999999999999999999999999999999999999.9999 | +---------------------------------------------------------------------+