revision-id: f3fc8db00653371972c777a2ee40b20d9ea6b62b (mariadb-10.2.31-712-gf3fc8db0065) parent(s): 7c9ce660cf715e06cf7b4b69e9c1011b01abdf74 author: Varun Gupta committer: Varun Gupta timestamp: 2021-01-27 14:32:52 +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 e48a99f6aff..ef4f0a48534 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6818,5 +6818,20 @@ DROP PROCEDURE sp1; 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.2 tests # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index b5ce6a1cabf..8cb00f7a6f4 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6543,6 +6543,22 @@ DROP PROCEDURE sp1; 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.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 802bfca64b7..6bd07c7d963 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -436,6 +436,26 @@ bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select, } +/* + @brief + Update the table bitmaps for the outer references used within a subquery +*/ + +bool Item_subselect::update_table_bitmaps_processor(void *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 2292c22480f..9116238c640 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -249,6 +249,7 @@ class Item_subselect :public Item_result_field, @retval FALSE otherwise */ bool is_expensive_processor(void *arg) { return is_expensive(); } + bool update_table_bitmaps_processor(void *arg); /** Get the SELECT_LEX structure associated with this Item.