diff options
Diffstat (limited to 'mysql-test/main/group_by.result')
| -rw-r--r-- | mysql-test/main/group_by.result | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index ecd115c2dd0..47f399ecd3d 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -2884,6 +2884,52 @@ 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 +# +# # MDEV-16170 # Server crashes in Item_null_result::type_handler on SELECT with ROLLUP # @@ -2894,4 +2940,6 @@ f COUNT(*) 1 1 NULL 1 DROP TABLE t1; +# # End of 10.3 tests +# |