Bug #120904 Wrong/inconsistent result between VIEW and CTE with UNION ALL, EXISTS, and integer division

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

[13 Jul 9:59] Aaditya Dubey

Description:
A query using a VIEW and an equivalent query using a CTE return different results.

Both queries are logically equivalent: they calculate c1 / c2 from the same table twice via UNION ALL, then use EXISTS to test whether any computed value is greater than 0.

The VIEW query returns 2 rows, while the equivalent CTE query returns an empty set.

This looks like a wrong-result bug, possibly related to CTE materialization / expression precision / derived-table optimization.

How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;

CREATE TABLE t0 (
  c1 INT,
  c2 INT
);

INSERT INTO t0 VALUES (-1, -100000);

CREATE OR REPLACE VIEW v0 AS
SELECT c1 / c2 AS vc_0 FROM t0
UNION ALL
SELECT c1 / c2 AS vc_0 FROM t0;

-- Query using VIEW
SELECT *
FROM v0
WHERE EXISTS (
  SELECT 0
  FROM v0 AS i
  WHERE i.vc_0 > 0
);

-- Equivalent query using CTE
WITH CTE AS (
  SELECT c1 / c2 AS vc_0 FROM t0
  UNION ALL
  SELECT c1 / c2 AS vc_0 FROM t0
)
SELECT *
FROM CTE
WHERE EXISTS (
  SELECT 0
  FROM CTE AS i
  WHERE i.vc_0 > 0
);

Actual result:

The VIEW query returns:

+--------+
| vc_0   |
+--------+
| 0.0000 |
| 0.0000 |
+--------+
2 rows in set

The CTE query returns:

Empty set

Suggested fix:
The VIEW query and the CTE query should return the same result because they are logically equivalent.

Either both should return 2 rows, or both should return an empty set. Returning different results indicates an inconsistent/wrong result.

Additional notes:
The underlying expression is:

  -1 / -100000

which is a positive value. Although the displayed value is 0.0000, the predicate i.vc_0 > 0 behaves differently depending on whether the query is written using a VIEW or a CTE.

Please check whether this is caused by expression precision, CTE materialization, UNION ALL type derivation, or optimizer transformation.