[MDEV-17242] Wrong result from distinct and arithmetic expression using window function Created: 2018-09-19  Updated: 2019-05-04  Resolved: 2019-05-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.8, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Lukas Eder Assignee: Varun Gupta (Inactive)
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-14791 Crash with order by expression contai... Closed

 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

 Comments   
Comment by Alice Sherepa [ 2018-09-20 ]

Thanks for the report! Reproducible on MariaDB 10.2,10.3

create table t1 (i1 int, i2 int);
 
insert into t1 values (1,40), (2,60), (3,60),(4,20);
 
MariaDB [test]> SELECT 1+LAG(i2) OVER(ORDER BY i1) FROM t1;
 
+-----------------------------+
 
| 1+LAG(i2) OVER(ORDER BY i1) |
 
+-----------------------------+
 
|                        NULL |
 
|                          41 |
 
|                          61 |
 
|                          61 |
 
+-----------------------------+
 
4 rows in set (0.001 sec)
 
 
 
MariaDB [test]> SELECT distinct 1+LAG(i2) OVER(ORDER BY i1) FROM t1;
 
+-----------------------------+
 
| 1+LAG(i2) OVER(ORDER BY i1) |
 
+-----------------------------+
 
|                        NULL |
 
+-----------------------------+
 
1 row in set (0.000 sec)
 
MariaDB [test]> SELECT distinct 1+LAG(i2,0) OVER(ORDER BY i1) FROM t1;
 
+-------------------------------+
 
| 1+LAG(i2,0) OVER(ORDER BY i1) |
 
+-------------------------------+
 
|                            41 |
 
+-------------------------------+
 
1 row in set (0.000 sec)
 
MariaDB [test]> SELECT distinct 1+LEAD(i2) OVER(ORDER BY i1) FROM t1;
 
+------------------------------+
 
| 1+LEAD(i2) OVER(ORDER BY i1) |
 
+------------------------------+
 
|                           61 |
 
+------------------------------+
 
1 row in set (0.002 sec)
 
MariaDB [test]> SELECT distinct 1+rank() OVER(ORDER BY i1) FROM t1;
 
+----------------------------+
 
| 1+rank() OVER(ORDER BY i1) |
 
+----------------------------+
 
|                          2 |
 
+----------------------------+
 
1 row in set (0.001 sec)

As a temporary workaround, it is possible to use derived table or CTE table

MariaDB [test]> select distinct * from (SELECT  1+LAG(i2) OVER(ORDER BY i1) FROM t1) tmp;
 
+-----------------------------+
 
| 1+LAG(i2) OVER(ORDER BY i1) |
 
+-----------------------------+
 
|                        NULL |
 
|                          41 |
 
|                          61 |
 
+-----------------------------+
 
3 rows in set (0.003 sec)

Comment by Varun Gupta (Inactive) [ 2019-04-11 ]

Looks like a duplicate for MDEV-14791

Comment by Varun Gupta (Inactive) [ 2019-05-04 ]

After fix for MDEV-14791 was pushed we now get the correct results

MariaDB [test]> SELECT 1+LAG(i2) OVER(ORDER BY i1) FROM t1;
 
+-----------------------------+
 
| 1+LAG(i2) OVER(ORDER BY i1) |
 
+-----------------------------+
 
|                        NULL |
 
|                          41 |
 
|                          61 |
 
|                          61 |
 
+-----------------------------+
 
4 rows in set (0.01 sec)
 
 
 
MariaDB [test]> SELECT distinct 1+LAG(i2) OVER(ORDER BY i1) FROM t1;
 
+-----------------------------+
 
| 1+LAG(i2) OVER(ORDER BY i1) |
 
+-----------------------------+
 
|                        NULL |
 
|                          41 |
 
|                          61 |
 
+-----------------------------+
 
3 rows in set (0.01 sec)
 
 
 
MariaDB [test]> SELECT distinct 1+LAG(i2,0) OVER(ORDER BY i1) FROM t1;
 
+-------------------------------+
 
| 1+LAG(i2,0) OVER(ORDER BY i1) |
 
+-------------------------------+
 
|                            41 |
 
|                            61 |
 
|                            21 |
 
+-------------------------------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT distinct 1+LEAD(i2) OVER(ORDER BY i1) FROM t1;
 
+------------------------------+
 
| 1+LEAD(i2) OVER(ORDER BY i1) |
 
+------------------------------+
 
|                           61 |
 
|                           21 |
 
|                         NULL |
 
+------------------------------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT distinct 1+rank() OVER(ORDER BY i1) FROM t1;
 
+----------------------------+
 
| 1+rank() OVER(ORDER BY i1) |
 
+----------------------------+
 
|                          2 |
 
|                          3 |
 
|                          4 |
 
|                          5 |
 
+----------------------------+
 
4 rows in set (0.00 sec)

So closing this as a duplicate of MDEV-14791

Generated at Thu Feb 08 08:34:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.