[#MDEV-23826] ORDER BY in view definition leads to wrong result with GROUP BY on query using view

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;

[MDEV-23826] ORDER BY in view definition leads to wrong result with GROUP BY on query using view Created: 2020-09-27 Updated: 2021-01-12 Resolved: 2021-01-12
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.2.33, 10.3.24, 10.4.14, 10.5.5
Fix Version/s:	10.2.37, 10.3.28, 10.4.18, 10.5.9

[MDEV-23826] ORDER BY in view definition leads to wrong result with GROUP BY on query using view Created: 2020-09-27 Updated: 2021-01-12 Resolved: 2021-01-12