diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/group_by.result | 44 | ||||
| -rw-r--r-- | mysql-test/t/group_by.test | 33 |
2 files changed, 77 insertions, 0 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index c996627486c..55e7703a377 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2875,3 +2875,47 @@ WHERE c.table_schema=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.columns GROUP BY c GROUP BY t.table_name; ERROR HY001: Out of sort memory, consider increasing server sort buffer size SET max_sort_length= @save_max_sort_length; +# +# MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view +# +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); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1 ORDER 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; +dt foo +2020-09-25 12:00:00 1 +2020-09-25 13:00:00 1 +2020-09-26 12:00:00 1 +2020-09-26 13:00:00 2 +2020-09-27 12:00:00 1 +2020-09-27 13:00:00 1 +2020-09-28 12:00:00 1 +2020-09-28 13:00:00 1 +SELECT dt, sum(foo) AS foo FROM v2 WHERE dt>DATE_SUB('2020-09-27 00:00:00', INTERVAL 3 DAY) GROUP BY dt; +dt foo +2020-09-25 12:00:00 1 +2020-09-25 13:00:00 1 +2020-09-26 12:00:00 1 +2020-09-26 13:00:00 2 +2020-09-27 12:00:00 1 +2020-09-27 13:00:00 1 +2020-09-28 12:00:00 1 +2020-09-28 13:00:00 1 +DROP TABLE t1; +DROP VIEW v1,v2; +# End of 10.2 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 324b41ce23c..d4c6d6527d5 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1996,3 +1996,36 @@ ON t.table_schema=c.table_schema WHERE c.table_schema=(SELECT COUNT(*) FROM INFORMATION_SCHEMA.columns GROUP BY column_type) GROUP BY t.table_name; SET max_sort_length= @save_max_sort_length; + + +--echo # +--echo # MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view +--echo # + +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); + +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE VIEW v2 AS SELECT * FROM t1 ORDER 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; + +DROP TABLE t1; +DROP VIEW v1,v2; + +--echo # End of 10.2 tests |