revision-id: 7a1e338fbe69dcaf3f1e000c1948422426852886 (mariadb-10.2.31-462-g7a1e338fbe6) parent(s): 7c5519c12d46ead947d341cbdcbb6fbbe4d4fe1b author: Varun Gupta committer: Varun Gupta timestamp: 2020-09-28 13:17:39 +0530 message: MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view Introduced val_time_packed and val_datetime_packed functions for Item_direct_view_ref to make sure to get the value from the item it is referring to. The issue for incorrect result was that the item was getting its value from the temporary table rather than from the view. --- mysql-test/r/group_by.result | 44 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/group_by.test | 33 +++++++++++++++++++++++++++++++++ sql/item.cc | 17 +++++++++++++++++ sql/item.h | 2 ++ 4 files changed, 96 insertions(+) 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..2791f672dc1 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 diff --git a/sql/item.cc b/sql/item.cc index 994d45a9dc3..16db9bb3ebf 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8896,6 +8896,23 @@ bool Item_direct_view_ref::val_bool_result() } +longlong Item_direct_view_ref::val_time_packed() +{ + DBUG_ASSERT(fixed); + longlong tmp = (*ref)->val_time_packed(); + null_value= (*ref)->null_value; + return tmp; +} + +longlong Item_direct_view_ref::val_datetime_packed() +{ + DBUG_ASSERT(fixed); + longlong tmp = (*ref)->val_datetime_packed(); + null_value= (*ref)->null_value; + return tmp; +} + + bool Item_default_value::eq(const Item *item, bool binary_cmp) const { return item->type() == DEFAULT_VALUE_ITEM && diff --git a/sql/item.h b/sql/item.h index 4a761bfd70a..a51df45365b 100644 --- a/sql/item.h +++ b/sql/item.h @@ -5012,6 +5012,8 @@ class Item_direct_view_ref :public Item_direct_ref String *str_result(String* tmp); my_decimal *val_decimal_result(my_decimal *val); bool val_bool_result(); + longlong val_time_packed(); + longlong val_datetime_packed(); Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return get_item_copy<Item_direct_view_ref>(thd, mem_root, this); }