Bug #120921 Wrong result with VIEW, GROUP BY, STDDEV(DOUBLE), and IN subquery

• Submitted: 14 Jul 13:51
• Reporter: Aaditya Dubey
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.4.10
• OS: Any
• CPU Architecture: Any

[14 Jul 13:51] Aaditya Dubey

Description:
A query using a VIEW returns a different result from an equivalent query using a CTE.

The query involves:

GROUP BY

boolean grouping expressions: b > 0 and c IS NULL

aggregate functions: COUNT(DISTINCT 0) and STDDEV(b)

DOUBLE values with very large magnitudes

an IN subquery over the aggregate result

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 VIEW expansion, aggregate evaluation, floating-point aggregate comparison, or optimizer transformation of IN subqueries.

How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
  b DOUBLE,
  c INT
);
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES (9.14190167261196e29, 1);
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES ();
INSERT INTO a VALUES (8.022600560042253e29, 1);
INSERT INTO a VALUES (1.9600251635571174e29, 1);
INSERT INTO a VALUES ();
INSERT INTO a VALUES (1.9258639150661972e29, NULL);
CREATE OR REPLACE VIEW v0 AS
SELECT
  b > 0 AS e,
  c IS NULL AS f,
  COUNT(DISTINCT 0) AS n,
  STDDEV(b) AS g
FROM a
GROUP BY e, f;
-- Query using VIEW
SELECT *
FROM v0
WHERE g IN (
  SELECT g FROM v0 WHERE e
);
-- Equivalent query using CTE
WITH CTE AS (
  SELECT
    b > 0 AS e,
    c IS NULL AS f,
    COUNT(DISTINCT 0) AS n,
    STDDEV(b) AS g
  FROM a
  GROUP BY e, f
)
SELECT *
FROM CTE
WHERE g IN (
  SELECT g FROM CTE WHERE e
);

Actual result
The VIEW query returns 1 row:

+------+---+---+------+
| e    | f | n | g    |
+------+---+---+------+
|    1 | 1 | 1 |    0 |
+------+---+---+------+
1 row in set
The equivalent CTE query returns 2 rows:

+------+---+---+-----------------------+
| e    | f | n | g                     |
+------+---+---+-----------------------+
|    1 | 0 | 1 | 3.1550137455235078e29 |
|    1 | 1 | 1 |                     0 |
+------+---+---+-----------------------+
2 rows in set
Expected result
Both queries should return the same result.

The expected result appears to be 2 rows, matching the CTE query.

Suggested fix:
Reasoning
The VIEW/CTE body groups the table by:

b > 0 AS e,
c IS NULL AS f
The relevant grouped result should contain these rows:

e      f    n    g
----   --   --   -----------------------
1      0    1    3.1550137455235078e29
1      1    1    0
NULL   1    1    NULL
The subquery:

SELECT g FROM v0 WHERE e
should return the g values from rows where e is TRUE:

3.1550137455235078e29
0
Therefore the outer query:

SELECT *
FROM v0
WHERE g IN (
  SELECT g FROM v0 WHERE e
);
should return both rows where e = 1:

e    f    n    g
1    0    1    3.1550137455235078e29
1    1    1    0
The CTE query returns these 2 rows, while the VIEW query only returns the row where g = 0.

This suggests that the VIEW query incorrectly filters out the non-zero STDDEV(b) aggregate value.