[MDEV-17242] Wrong result from distinct and arithmetic expression using window function - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Duplicate
Affects Version/s: 10.3.8, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: N/A
Component/s: Optimizer - Window functions
Labels:
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

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2018-09-20 10:30 - edited

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)

Alice Sherepa added a comment - 2018-09-20 10:30 - edited 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)

Varun Gupta (Inactive) added a comment - 2019-04-11 19:17

Looks like a duplicate for ~~MDEV-14791~~

Varun Gupta (Inactive) added a comment - 2019-04-11 19:17 Looks like a duplicate for MDEV-14791

Varun Gupta (Inactive) added a comment - 2019-05-04 10:16 - edited

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~~

Varun Gupta (Inactive) added a comment - 2019-05-04 10:16 - edited 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

MariaDB Server

Wrong result from distinct and arithmetic expression using window function

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration