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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14791 Crash with order by expression containing window functions

          • Major - Major loss of function.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Alice Sherepa made transition -
            Open Confirmed
            		20h 36m 1
            Varun Gupta (Inactive) made transition -
            Confirmed In Progress
            		203d 8h 47m 1
            Varun Gupta (Inactive) made transition -
            In Progress Closed
            		22d 15h 3m 1

            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.