revision-id: 7907592eeb5d0fd8ac1064ef5f77eb274ddb0b5b (mariadb-10.1.43-249-g7907592eeb5) parent(s): e00bafaa6162ff8967a0d8eed6aa12806cfd4552 author: Varun Gupta committer: Varun Gupta timestamp: 2020-08-05 15:07:18 +0530 message: MDEV-23291: SUM column from a derived table returns invalid values The issue here was the read_set bitmap was not set for a field which was used as a reference in an inner select. We need to make sure that if we are in an inner select and we have references from outer select then we update the table bitmaps for such references. Introduced a function in the class Item_subselect that would update bitmaps of table for the references within a subquery that are defined in outer selects. --- mysql-test/r/view.result | 15 +++++++++++++++ mysql-test/t/view.test | 16 ++++++++++++++++ sql/item_subselect.cc | 20 ++++++++++++++++++++ sql/item_subselect.h | 1 + 4 files changed, 52 insertions(+) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index d73ef7c0ee4..ba09d694c23 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6274,5 +6274,20 @@ t1col1 t1col2 t1col3 drop view v1; drop table t1,t2; # +# MDEV-23291: SUM column from a derived table returns invalid values +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +CREATE view v1 AS +SELECT a as x, (select x) as y, (select y) as z FROM t1; +SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; +sum(z) +3 +SELECT sum(z) FROM v1; +sum(z) +3 +DROP TABLE t1; +DROP VIEW v1; +# # End of 10.1 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index f7c9ffbc99b..df589e83122 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6104,6 +6104,22 @@ select * from v1; drop view v1; drop table t1,t2; +--echo # +--echo # MDEV-23291: SUM column from a derived table returns invalid values +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); + +CREATE view v1 AS +SELECT a as x, (select x) as y, (select y) as z FROM t1; + +SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; +SELECT sum(z) FROM v1; + +DROP TABLE t1; +DROP VIEW v1; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 0b28c15027d..236f5433777 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -424,6 +424,26 @@ bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, return FALSE; } +/* + @brief + Update the table bitmaps for the outer references used within a subquery +*/ + +bool Item_subselect::update_table_bitmaps_processor(uchar *arg) +{ + List_iterator<Ref_to_outside> it(upper_refs); + Ref_to_outside *upper; + + while ((upper= it++)) + { + if (upper->item && + upper->item->walk(&Item::update_table_bitmaps_processor, FALSE, arg)) + return TRUE; + } + return FALSE; +} + + /* Adjust attributes after our parent select has been merged into grandparent diff --git a/sql/item_subselect.h b/sql/item_subselect.h index ee8b31f4f17..006bcfcaf88 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -231,6 +231,7 @@ class Item_subselect :public Item_result_field, @retval FALSE otherwise */ bool is_expensive_processor(uchar *arg) { return is_expensive(); } + bool update_table_bitmaps_processor(uchar *arg); /** Get the SELECT_LEX structure associated with this Item.