Details
Major 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;
|