Bug #120300 Window function logic mismatch across engines

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

[20 Apr 9:02] QiFan Liu

Description:
MySQL returns different results for the same logical query depending only on storage engine choice. In the reproduced case, the `MyISAM` version produces `5` rows while the default-engine version produces `0`.

The bug is observable on a window-function query that uses `ROW_NUMBER()`, `LAG(..., NULL)`, and a repeater CTE together with the shown temporal predicate.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 BOOLEAN, c1 REAL) ENGINE=MyISAM;
INSERT INTO t0 VALUES (TRUE, 3.5);

WITH wf_src AS (
  SELECT
    src.c0,
    src.c1,
    ROW_NUMBER() OVER w_main AS wf_rn,
    LAG(src.c1, 1, NULL) OVER w_main AS prev_c1
  FROM t0 AS src
  WINDOW w_main AS (PARTITION BY src.c1 ORDER BY src.c0)
),
repeater AS (
  SELECT ROW_NUMBER() OVER (ORDER BY seed.n) AS rep_id
  FROM (
    SELECT 1 AS n
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
  ) AS seed
)
SELECT @myisam_count := COUNT(*)
FROM (
  SELECT rep_id
  FROM (
    SELECT 1 AS c0
    FROM wf_src AS tom0
    WHERE tom0.wf_rn >= 1
      AND IFNULL(
        CASE
          WHEN NULLIF(TIME('2026-08-09 03:04:05'), 'A') >> tom0.c0 LIKE '%'
            THEN '2026-08-09'
          ELSE NULLIF('2026-08-09', '2026-08-09')
        END,
        1
      ) ^ DATE_SUB('2026-08-09', INTERVAL 2 DAY)
    GROUP BY tom0.c1
  ) AS hit
  JOIN repeater ON TRUE
) AS q;

DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 BOOLEAN, c1 REAL);
INSERT INTO t0 VALUES (TRUE, 3.5);

WITH wf_src AS (
  SELECT
    src.c0,
    src.c1,
    ROW_NUMBER() OVER w_main AS wf_rn,
    LAG(src.c1, 1, NULL) OVER w_main AS prev_c1
  FROM t0 AS src
  WINDOW w_main AS (PARTITION BY src.c1 ORDER BY src.c0)
),
repeater AS (
  SELECT ROW_NUMBER() OVER (ORDER BY seed.n) AS rep_id
  FROM (
    SELECT 1 AS n
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
  ) AS seed
)
SELECT @default_count := COUNT(*)
FROM (
  SELECT rep_id
  FROM (
    SELECT 1 AS c0
    FROM wf_src AS tom0
    WHERE tom0.wf_rn >= 1
      AND IFNULL(
        CASE
          WHEN NULLIF(TIME('2026-08-09 03:04:05'), 'A') >> tom0.c0 LIKE '%'
            THEN '2026-08-09'
          ELSE NULLIF('2026-08-09', '2026-08-09')
        END,
        1
      ) ^ DATE_SUB('2026-08-09', INTERVAL 2 DAY)
    GROUP BY tom0.c1
  ) AS hit
  JOIN repeater ON TRUE
) AS q;

SELECT JSON_OBJECT(
  'myisam_count',
  @myisam_count,
  'default_count',
  @default_count
) AS observation;

[20 Apr 11:02] Roy Lyseng

Thank you for the bug report.
Verified as described.