revision-id: 4746d353f0fcbb64fcda485f7b01e0135be61db8 (mariadb-10.1.43-249-g4746d353f0f) parent(s): e00bafaa6162ff8967a0d8eed6aa12806cfd4552 author: Varun Gupta committer: Varun Gupta timestamp: 2020-08-05 14:08:00 +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 an item in the select list of a subquery which is used as an outer reference in an outer select. Introduced a function in the class Item_subselect that would update bitmaps of table for the references from within a subquery that is used in outer selects. --- mysql-test/r/view.result | 15 +++++++++++++++ mysql-test/t/view.test | 16 ++++++++++++++++ sql/item_subselect.cc | 21 +++++++++++++++++++++ sql/item_subselect.h | 1 + 4 files changed, 53 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..b6a3d10a790 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -424,6 +424,27 @@ bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, return FALSE; } +/* + @brief + Update the table bitmaps for the reference of a subquery used in upper + level selects. +*/ + +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.