Bug #120299 mismatch in `DELETE ... EXISTS` under window function query

• Submitted: 20 Apr 9:00
• Modified: 20 Apr 10:41
• Reporter: QiFan Liu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.6
• OS: Linux (Observed on Linux via the `mysql:9.6` )
• CPU Architecture: Any

[20 Apr 9:00] QiFan Liu

Description:
MySQL produces inconsistent `DELETE` row counts for two related `EXISTS` predicates built from `EXCEPT`/`INTERSECT` expressions. In the reproduced case, the first statement deletes all 5 rows from `t3`, while the mutated statement deletes 0 rows.

The bug is observable on a window-function query that uses `ROW_NUMBER()`, `LAG()`, `LEAD()`, and a named window inside CTEs feeding the `DELETE ... EXISTS` predicate.

How to repeat:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;

CREATE TABLE t1 (c5 DATE NOT NULL);
CREATE TABLE t2 (c13 DATETIME NULL);
CREATE TABLE t3 (id INT PRIMARY KEY);

INSERT INTO t1 VALUES ('2026-06-15'), ('2026-06-17');
INSERT INTO t2 VALUES ('2026-06-17 00:00:00');
INSERT INTO t3 VALUES (7), (8), (9), (10), (11);

START TRANSACTION;
DELETE FROM t3
WHERE EXISTS (
  WITH wf_t1 AS (
    SELECT
      src.c5,
      ROW_NUMBER() OVER w_main AS wf_rn,
      LAG(src.c5, 1, NULL) OVER w_main AS prev_c5
    FROM t1 AS src
    WINDOW w_main AS (ORDER BY src.c5)
  ),
  wf_t2 AS (
    SELECT
      src.c13,
      ROW_NUMBER() OVER w_main AS wf_rn,
      LEAD(src.c13, 1, NULL) OVER w_main AS next_c13
    FROM t2 AS src
    WINDOW w_main AS (ORDER BY src.c13)
  )
  SELECT 1
  FROM (
    (
      SELECT x.k, COALESCE(x.k, '2020-02-02 02:02:02') AS pad2
      FROM (
        SELECT a.c5 AS k, 11 AS pad FROM wf_t1 AS a WHERE a.wf_rn >= 1
        EXCEPT
        SELECT b.c13 AS k, 22 AS pad FROM wf_t2 AS b WHERE b.wf_rn >= 1
      ) AS x
    )
    INTERSECT
    (
      SELECT y.k, y.k AS pad2
      FROM (
        SELECT a.c5 AS k, 11 AS pad FROM wf_t1 AS a WHERE a.wf_rn >= 1
        EXCEPT
        SELECT b.c13 AS k, 22 AS pad FROM wf_t2 AS b WHERE b.wf_rn >= 1
      ) AS y
    )
  ) AS z
);
SET @original_deleted = ROW_COUNT();
ROLLBACK;

START TRANSACTION;
DELETE FROM t3
WHERE EXISTS (
  WITH wf_t1 AS (
    SELECT
      src.c5,
      ROW_NUMBER() OVER w_main AS wf_rn,
      LAG(src.c5, 1, NULL) OVER w_main AS prev_c5
    FROM t1 AS src
    WINDOW w_main AS (ORDER BY src.c5)
  ),
  wf_t2 AS (
    SELECT
      src.c13,
      ROW_NUMBER() OVER w_main AS wf_rn,
      LEAD(src.c13, 1, NULL) OVER w_main AS next_c13
    FROM t2 AS src
    WINDOW w_main AS (ORDER BY src.c13)
  )
  SELECT 1
  FROM (
    (
      SELECT x.k, COALESCE(x.k, '2020-02-02 02:02:02') AS pad2
      FROM (
        SELECT a.c5 AS k, 11 AS pad FROM wf_t1 AS a WHERE a.wf_rn >= 1
        EXCEPT
        SELECT b.c13 AS k, 22 AS pad FROM wf_t2 AS b WHERE b.wf_rn >= 1
      ) AS x
    )
    INTERSECT
    (
      SELECT y.k, y.k AS pad2
      FROM (
        SELECT a.c5 AS k, 11 AS pad FROM wf_t1 AS a WHERE a.wf_rn >= 1
      ) AS y
    )
  ) AS z
);
SET @mutated_deleted = ROW_COUNT();
ROLLBACK;

SELECT JSON_OBJECT(
  'original_deleted',
  @original_deleted,
  'mutated_deleted',
  @mutated_deleted
) AS observation;

[20 Apr 10:41] Roy Lyseng

Thank you for the bug report.
Verified as described.