Bug #92503 Wrong result from distinct and arithmetic expression using window function

• Submitted: 19 Sep 2018 13:48
• Modified: 11 Feb 2019 16:41
• Reporter: Lukas Eder
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.12
• OS: Linux
• CPU Architecture: x86
• Tags: lag, Window functions

[19 Sep 2018 13:48] Lukas Eder

Description:
When using the LAG() window function in an arithmetic expression, the result may be wrong

How to repeat:
This statement:

------------------------------------------------------------
WITH tab(t, company, quote) AS (
  SELECT 1 AS t,   'G' AS company, 40 AS quote
  UNION SELECT 2 , 'G',    60 
  UNION SELECT 3 , 'S',    60 
  UNION SELECT 4,  'S',    20
)
SELECT DISTINCT company, quote - LAG(quote) OVER(PARTITION BY company ORDER BY t)
FROM tab;
------------------------------------------------------------

produces wrong output:

------------------------------------------------------------
company	e
--------------
G	(null)
S	(null)
------------------------------------------------------------

Remove the DISTINCT keyword....

------------------------------------------------------------
WITH tab(t, company, quote) AS (
  SELECT 1 AS t,   'G' AS company, 40 AS quote
  UNION SELECT 2 , 'G',    60 
  UNION SELECT 3 , 'S',    60 
  UNION SELECT 4,  'S',    20
)
SELECT company, quote - LAG(quote) OVER(PARTITION BY company ORDER BY t) e
FROM tab;
------------------------------------------------------------

to get this:

------------------------------------------------------------
company	e
--------------
G	(null)
G	20
S	(null)
S	-40
------------------------------------------------------------

As can be seen, the DISTINCT keyword should have no effect on this query.

This is also described in this stack overflow question:
https://stackoverflow.com/q/49700278/521799

[19 Sep 2018 15:15] MySQL Verification Team

Thank you for the bug report.

[11 Feb 2019 16:41] Jon Stephens

Documented fix as follows in the MySQL 8.0.16 changelog:

    A windowing function employed in an arithmetic expression
    produced a wrong result when the query containing it used
    DISTINCT.

Closed.

[18 Feb 2021 9:39] Erlend Dahl

Bug#90992 DISTINCT ignores window functions

was marked as a duplicate.