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

ORDER BY in view definition leads to wrong result with GROUP BY on query using view

Details

    Description

      Starting with MariaDB 10.2 the SQL snippet below does no longer return the same result on all three SELECTs at its end as expected, instead the last query, which uses the view with ORDER BY in it, returns an empty result set now:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (
      		 
        id INT PRIMARY KEY AUTO_INCREMENT,
      		 
        dt datetime,
      		 
        INDEX(dt),
      		 
        foo int
      		 
      );
      		 
       
      		 
      INSERT INTO t1 VALUES (1,'2020-09-26 12:00:00',1);
      		 
      INSERT INTO t1 VALUES (2,'2020-09-26 13:00:00',1);
      		 
      INSERT INTO t1 VALUES (3,'2020-09-27 13:00:00',1);
      		 
      INSERT INTO t1 VALUES (4,'2020-09-27 12:00:00',1);
      		 
      INSERT INTO t1 VALUES (5,'2020-09-28 12:00:00',1);
      		 
      INSERT INTO t1 VALUES (6,'2020-09-28 13:00:00',1);
      		 
      INSERT INTO t1 VALUES (7,'2020-09-25 12:00:00',1);
      		 
      INSERT INTO t1 VALUES (8,'2020-09-25 13:00:00',1);
      		 
      INSERT INTO t1 VALUES (9,'2020-09-26 13:00:00',1);
      		 
       
      		 
      DROP VIEW IF EXISTS v1;
      		 
      CREATE VIEW v1 AS SELECT * FROM t1;
      		 
       
      		 
      DROP VIEW IF EXISTS v2;
      		 
      CREATE VIEW v2 AS SELECT * FROM t1 ORDER BY dt;
      		 
       
      		 
      SELECT dt, sum(foo) AS foo FROM t1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
      		 
       
      		 
      SELECT dt, sum(foo) AS foo FROM v1 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;
      		 
       
      		 
      SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt;

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            hholzgra Hartmut Holzgraefe
            Votes:
            0 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.