Bug #92503 Wrong result from distinct and arithmetic expression using window function
Submitted: 19 Sep 2018 13:48 Modified: 11 Feb 16:41
Reporter: Lukas Eder Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.12 OS:Linux
Assigned to: 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] Miguel Solorzano
Thank you for the bug report.
[11 Feb 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.