Bug #118916 Incorrect result set when querying view with LEFT JOIN, COALESCE and EXISTS subquery

• Submitted: 31 Aug 18:26
• Modified: 5 Sep 8:21
• Reporter: Runyuan He
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[31 Aug 18:26] Runyuan He

Description:
When creating a view that uses LEFT JOIN with COALESCE and then querying this view with an EXISTS subquery that references the same view, MySQL returns an incorrect number of rows. The query returns 12 rows instead of the expected 6 rows.

How to repeat:
CREATE  TABLE  t0(c VARCHAR(500));
CREATE  TABLE  t1(c VARCHAR(500));
INSERT INTO t0 VALUES ('A'), ('B'), ('C');
INSERT INTO t1 VALUES ('D'), ('E');

CREATE VIEW v AS SELECT
  COALESCE(t1.c, t0.c) AS c
FROM t0
LEFT JOIN t1 ON TRUE;

SELECT a.c
FROM v a
WHERE EXISTS (
  SELECT 1
  FROM v b
  WHERE b.c = a.c
);
-- 12rows (INCORRECT), should be 6rows

SELECT v.c FROM v;
-- 6rows (CORRECT)

[3 Sep 8:28] MySQL Verification Team

Thank you for the report.

mysql> SELECT a.c
    -> FROM v a
    -> WHERE EXISTS (
    ->   SELECT 1
    ->   FROM v b
    ->   WHERE b.c = a.c
    -> );
+------+
| c    |
+------+
| E    |
| D    |
| E    |
| D    |
| E    |
| D    |
| E    |
| D    |
| E    |
| D    |
| E    |
| D    |
+------+
12 rows in set (0.002 sec)

mysql> SELECT v.c FROM v;
+------+
| c    |
+------+
| E    |
| D    |
| E    |
| D    |
| E    |
| D    |
+------+
6 rows in set (0.001 sec)

mysql> -- 6rows (CORRECT)
Query OK, 0 rows affected (0.000 sec)

mysql> select @@version
    -> ;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>

[5 Sep 8:21] Roy Lyseng

Duplicate of bug#118512