revision-id: 7ab00c4c7c165107ccf7a1311042a45bfa6a7c62 (mariadb-10.2.31-1295-g7ab00c4) parent(s): 07145ea7a08eed9bd86781849865fa106a247789 author: Igor Babaev committer: Igor Babaev timestamp: 2022-01-05 22:24:23 -0800 message: MDEV-25086 Stored Procedure Crashes Server The cause of this bug is the same as of the bug MDEV-24454. This bug manifested itself at the second execution of the queries that contained a set function whose only argument was outer reference to a column of a mergeable view or derived table or CTE. The first execution of such query worked fine, but the second execution of the query caused a crash of the server because the aggregation select for the used set function was determined incorrectly at the name resolution phase of the second execution. --- mysql-test/r/derived_view.result | 130 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/derived_view.test | 111 +++++++++++++++++++++++++++++++++ sql/item.cc | 3 +- 3 files changed, 243 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index d8ee508..0d9b7e1 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -3456,4 +3456,134 @@ a 3 drop view v1; drop table t1; +# +# MDEV-24454 Second execution of SELECT containing set function +# MDEV-25086: whose only argument is an outer reference to a column +# of mergeable view/derived/table/CTE +# +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +drop view v1; +drop table t1,t2; +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT +(SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, +GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; +CREATE PROCEDURE p1() +SELECT * FROM v3; +CALL p1(); +f2 f3 +3 aa +CALL p1(); +f2 f3 +3 aa +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; # End of 10.2 tests diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 89ada40..0f3d9b2 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -2265,4 +2265,115 @@ select * from ((select a from t1 limit 2) order by a desc) dt; drop view v1; drop table t1; +--echo # +--echo # MDEV-24454 Second execution of SELECT containing set function +--echo # MDEV-25086: whose only argument is an outer reference to a column +--echo # of mergeable view/derived/table/CTE +--echo # + +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +drop view v1; +drop table t1,t2; + +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT + (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, + GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; + +CREATE PROCEDURE p1() + SELECT * FROM v3; +CALL p1(); +CALL p1(); + +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; + --echo # End of 10.2 tests diff --git a/sql/item.cc b/sql/item.cc index 17c56fe..109ca4e 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5617,7 +5617,8 @@ bool Item_field::fix_fields(THD *thd, Item **reference) goto mark_non_agg_field; } - if (thd->lex->in_sum_func && + if (!thd->lex->current_select->no_wrap_view_item && + thd->lex->in_sum_func && thd->lex->in_sum_func->nest_level == select->nest_level) set_if_bigger(thd->lex->in_sum_func->max_arg_level,