Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.3.8, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
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
The same bug appears also in MySQL:
https://bugs.mysql.com/bug.php?id=92503
Attachments
Issue Links
- is duplicated by
-
MDEV-14791 Crash with order by expression containing window functions
- Closed