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

Wrong result from distinct and arithmetic expression using window function

    XMLWordPrintable

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

            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.