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

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                lukas.eder Lukas Eder
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: