Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17242

Wrong result from distinct and arithmetic expression using window function

Details

    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

          Activity

            lukas.eder Lukas Eder created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            Assignee Alice Sherepa [ alice ]
            alice Alice Sherepa added a comment - - 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 Alice Sherepa added a comment - - 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 Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Assignee Alice Sherepa [ alice ] Vicentiu Ciorbaru [ cvicentiu ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            alice Alice Sherepa made changes -
            Component/s Optimizer - Window functions [ 13502 ]
            Component/s Data Manipulation - Subquery [ 10107 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.4 [ 22408 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.4 [ 22408 ]
            alice Alice Sherepa made changes -
            Assignee Vicentiu Ciorbaru [ cvicentiu ] Varun Gupta [ varun ]

            Looks like a duplicate for MDEV-14791

            varun Varun Gupta (Inactive) added a comment - Looks like a duplicate for MDEV-14791
            varun Varun Gupta (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) added a comment - - 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 Varun Gupta (Inactive) added a comment - - 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 Varun Gupta (Inactive) made changes -
            varun Varun Gupta (Inactive) made changes -
            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 ]
            varun Varun Gupta (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 89670 ] MariaDB v4 [ 154957 ]

            People

              varun Varun Gupta (Inactive)
              lukas.eder Lukas Eder
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.