Bug #120555 Equivalent VIEW and CTE queries return different row counts with EXISTS, BINARY ENUM comparison, and LEFT JOIN

• Submitted: 27 May 8:22
• Modified: 27 May 11:38
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.45
• OS: Ubuntu
• CPU Architecture: Any

[27 May 8:22] Xiaoyuan Xie

Description:
An equivalent VIEW query and CTE query return different results.

The VIEW version returns 2 rows:
  SELECT a.h FROM v0 a LEFT JOIN v0 b ON b.i;

The CTE version returns 1 row:
  WITH CTE AS (...)
  SELECT a.h FROM CTE a LEFT JOIN CTE b ON b.i;

I believe these two queries should be semantically equivalent because v0 is defined using the same SELECT body as the CTE. The difference appears to be a wrong-result bug, possibly related to view merging/materialization, EXISTS, BINARY comparison on ENUM, and LEFT JOIN condition evaluation.

How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0     |
+-----------+

DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;

CREATE TABLE c (
  d DOUBLE,
  e BOOLEAN,
  f ENUM('e6_f3dd'),
  g VARBINARY(1)
);

INSERT INTO c VALUES (0, NULL, 'e6_f3dd', '1');
INSERT INTO c VALUES (0, 1, '0', '');
INSERT INTO c VALUES ();

CREATE VIEW v0 AS
SELECT 1 AS h, 2 AS i
FROM c AS j
WHERE EXISTS (
  SELECT 0
  FROM c
  WHERE BINARY j.f = BINARY f
    AND e
);

SELECT a.h
FROM v0 AS a
LEFT JOIN v0 AS b
ON b.i;

WITH CTE AS (
  SELECT 1 AS h, 2 AS i
  FROM c AS j
  WHERE EXISTS (
    SELECT 0
    FROM c
    WHERE BINARY j.f = BINARY f
      AND e
  )
)
SELECT a.h
FROM CTE AS a
LEFT JOIN CTE AS b
ON b.i;

Actual result:

VIEW query:
+---+
| h |
+---+
| 1 |
| 1 |
+---+
2 rows

CTE query:
+---+
| h |
+---+
| 1 |
+---+
1 row

[27 May 11:38] Roy Lyseng

Thank you for the bug report.
Verified as described.