Bug #108008 mysql window function first_value wrong

• Submitted: 28 Jul 2022 5:47
• Modified: 4 Nov 2022 14:35
• Reporter: YuSeong Jeong
• Status: Closed
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 8.0.23, 8.0.30
• OS: Any
• CPU Architecture: Any
• Tags: first_value, nth_value, window function

[28 Jul 2022 5:47] YuSeong Jeong

Description:
in window function

In certain cases, The value of first_value is displayed as the value of the row indicated by nth_value

How to repeat:
SELECT id                         AS id
     , parent_id                  AS parent_id
     , value                      AS value
     , FIRST_VALUE(value) OVER w  AS 'FIRST_VALUE'
     , LAG(value) OVER w          AS 'LAG'
     , NTH_VALUE(value, 4) OVER w AS 'NTH_VALUE'
FROM (
         VALUES
             ROW(1, 1, 000001)
            ,ROW(2, 1, 000020)
            ,ROW(3, 1, 000300)
            ,ROW(4, 1, 004000)
            ,ROW(5, 1, 050000)
            ,ROW(6, 1, 600000)
         ) AS t(id, parent_id, value)
    WINDOW w AS (
        PARTITION BY parent_id
        ORDER BY value
        ROWS BETWEEN 1 PRECEDING
            AND 2 FOLLOWING
        )

-----------------------------------------------
1	1	1	1	NULL	NULL
2	1	20	4000	1	4000
3	1	300	20	20	50000
4	1	4000	300	300	600000
5	1	50000	4000	4000	NULL
6	1	600000	50000	50000	NULL

[28 Jul 2022 5:53] YuSeong Jeong

correct value

SELECT id                         AS id
     , parent_id                  AS parent_id
     , value                      AS value
     , FIRST_VALUE(value) OVER w  AS 'FIRST_VALUE'
#      , LAG(value) OVER w          AS 'LAG'
#      , NTH_VALUE(value, 4) OVER w AS 'NTH_VALUE'
FROM (
         VALUES
             ROW(1, 1, 1)
            ,ROW(2, 1, 20)
            ,ROW(3, 1, 300)
            ,ROW(4, 1, 4000)
            ,ROW(5, 1, 50000)
            ,ROW(6, 1, 600000)
         ) AS t(id, parent_id, value)
    WINDOW w AS (
        PARTITION BY parent_id
        ORDER BY value
        ROWS BETWEEN 1 PRECEDING
            AND 2 FOLLOWING
        )
----------------------------------

1	1	1	1
2	1	20	1
3	1	300	20
4	1	4000	300
5	1	50000	4000
6	1	600000	50000

[28 Jul 2022 7:40] MySQL Verification Team

Hello YuSeong Jeong,

Thank you for the report and test case.

regards,
Umesh

[4 Nov 2022 14:35] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.32 release, and here's the proposed changelog entry from the documentation team:

When using window functions, the current row could reevaluate itself based
on the wrong record in some cases.

Thank you for the bug report.