Bug #121155 MySQL loses LEFT JOIN preserved rows when an empty temporary table is used with DISTINCT and a window function

• Submitted: 22 Aug 15:02
• Reporter: cl hl
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.7.1
• OS: Any
• CPU Architecture: Any

[22 Aug 15:02] cl hl

Description:
Two equivalent empty right-hand relations produce different results in a `LEFT JOIN` combined with `DISTINCT` and
`LEAD()`. Keeping the empty relation as a derived table returns 27 rows. Materializing it into an empty temporary
table first returns only 22 rows.

# Expected result

Both final queries should return the same row count because both right-hand relations are empty. The temporary-table
query should therefore return 27.

# Actual result

```text
derived_rows:         27
temporary_table_rows: 0
temporary_rows:       22
```

The discrepancy is unchanged with `derived_merge=off`.

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

CREATE TABLE a (
    c1 INT PRIMARY KEY,
    c3 VARCHAR(255),
    c4 INT,
    c5 DATE,
    INDEX (c5)
);

CREATE TABLE b (
    c1 INT PRIMARY KEY,
    c4 VARCHAR(50),
    c10 ENUM('value1','value2','value3'),
    c13 DATETIME,
    INDEX (c13)
);

CREATE TABLE e (
    c1 INT PRIMARY KEY,
    c6 TINYINT
);

INSERT INTO a VALUES
    (1, 'a', 44, '2024-01-01'),
    (2, 'b', 57, '2024-01-01'),
    (3, 'c', 29, '2024-01-01'),
    (4, 'd', 27, '2024-01-01'),
    (5, 'e', 84, '2024-01-01'),
    (6, 'f', 43, '2024-01-01'),
    (7, 'g', 93, '2024-01-01');

INSERT INTO b VALUES
    (1, 'x1', 'value3', '2026-03-12 07:22:05'),
    (2, 'x2', 'value3', '2025-10-22 07:55:29'),
    (3, 'x3', 'value2', '2026-01-05 16:07:46'),
    (4, 'x4', 'value1', '2025-08-29 07:36:18'),
    (5, 'x5', 'value2', '2025-12-13 18:36:18'),
    (6, 'x6', 'value3', '2026-03-26 01:01:27');

-- Table e is intentionally empty, so this derived relation is empty.
-- Result on MySQL 9.7.1: 27.
SELECT COUNT(*) AS derived_rows
FROM (
    SELECT DISTINCT
        q.v,
        LEAD(a.c3) OVER (ORDER BY a.c4),
        UPPER(b.c10),
        a.c3
    FROM a
    JOIN b ON b.c4 IS NOT NULL
    LEFT JOIN (
        SELECT SUM(e.c6) AS v, COUNT(*) AS n
        FROM e
        JOIN a AS x
        GROUP BY x.c1
    ) AS q ON a.c1 > q.n
    WHERE a.c5 NOT BETWEEN '2023-01-01' AND '2023-12-31'
      AND b.c13 IN (SELECT c13 FROM b)
) AS result;

CREATE TEMPORARY TABLE tmp AS
SELECT SUM(e.c6) AS v, COUNT(*) AS n
FROM e
JOIN a AS x
GROUP BY x.c1;

-- Confirms that the materialized right-hand relation is also empty: 0.
SELECT COUNT(*) AS temporary_table_rows FROM tmp;

-- Result on MySQL 9.7.1: 22.
SELECT COUNT(*) AS temporary_rows
FROM (
    SELECT DISTINCT
        q.v,
        LEAD(a.c3) OVER (ORDER BY a.c4),
        UPPER(b.c10),
        a.c3
    FROM a
    JOIN b ON b.c4 IS NOT NULL
    LEFT JOIN tmp AS q ON a.c1 > q.n
    WHERE a.c5 NOT BETWEEN '2023-01-01' AND '2023-12-31'
      AND b.c13 IN (SELECT c13 FROM b)
) AS result;

DROP TEMPORARY TABLE tmp;