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

Window Lag function returning different result under from MySQL-8.0

    XMLWordPrintable

Details

    Description

      CREATE TABLE aggregates
      (
        `date` date,
        `ticker` varchar(3),
        l float,
        is_fs tinyint(1)
      );
       
      INSERT INTO aggregates
      VALUES
      	('2016-08-02', 'GFF', 16.75, NULL),
      	('2016-08-24', 'GFF', 16.75, NULL),
          ('2016-09-16', 'GFF', 16.75, NULL),
          ('2016-01-06', 'GFF', 16.75, NULL),
          ('2016-10-10', 'GFF', 16.75, NULL);
      

       
      SELECT 
       
      (1=1) 
      AND 
      (
          (LAG(l) OVER (PARTITION BY ticker ORDER BY date) > 1 AND 1=1) 
      OR (LAG(l) OVER (PARTITION BY ticker ORDER BY date) > 1 AND 1=1)
      )
       
      AS is_fs FROM `aggregates` WHERE YEAR(date) = 2016 AND ticker = 'GFF' ORDER BY is_fs DESC
      

      result:

      NULL
      NULL
      NULL
      NULL
      NULL
      

      https://www.db-fiddle.com/f/iEoGYnnKA3pmkB9piu8pWk/0

      MySQL-8.0 result

      1
      1
      1
      1
      NULL
      
      

      Attachments

        Issue Links

          Activity

            People

              cvicentiu Vicențiu Ciorbaru
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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