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
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Alice Sherepa [ alice ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Alice Sherepa [ alice ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Component/s | Optimizer - Window functions [ 13502 ] | |
| Component/s | Data Manipulation - Subquery [ 10107 ] |
| Affects Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Varun Gupta [ varun ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Link |
This issue relates to |
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
| Link |
This issue is duplicated by |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Duplicate [ 3 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 89670 ] | MariaDB v4 [ 154957 ] |
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)