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 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 ]
            varun Varun Gupta (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            varun Varun Gupta (Inactive) made changes -
            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.