[Commits] 7a1e338fbe6: MDEV-23826: ORDER BY in view definition leads to wrong result with GROUP BY on query using view

varun varunraiko1803 at gmail.com
Mon Sep 28 10:52:45 EEST 2020


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); }


More information about the commits mailing list