revision-id: 79ac9485f8fb86243c31d6e7895dfbe3c2f369ca (mariadb-10.2.31-781-g79ac948) parent(s): dc6667805dd4567693fcc01666da7d1277050097 author: Igor Babaev committer: Igor Babaev timestamp: 2021-03-09 14:51:52 -0800 message: MDEV-24597 Explicit column name error in CTE of UNION This bug manifested itself when executing queries with multiple reference to a CTE specified by a query expression with union and having its column names explicitly declared. In this case the server returned a bogus error message about unknown column name. It happened because while for the first reference to the CTE the names of the columns returned by the CTE specification were properly changed to match the CTE definition for the other references it was not done. This was a consequence of not quite complete code of the function TABLE_LIST::set_as_with_table that forgot to set the reference to the CTE definition for unit structures representing non-first CTE references. Approved by dmitry.shulga@mariadb.com --- mysql-test/r/cte_nonrecursive.result | 42 ++++++++++++++++++++++++++++++++++-- mysql-test/t/cte_nonrecursive.test | 31 ++++++++++++++++++++++++++ sql/sql_cte.cc | 1 + 3 files changed, 72 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index a47fdcd..da954c1 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1, t r2 where r1.c=r2.c and r2.c=4; show create view v4; View Create View character_set_client collation_connection -v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci select * from v4; c d 4 4 @@ -1126,7 +1126,7 @@ NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union9,12> ALL NULL NULL NULL NULL NULL NULL NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t 1`.`a` > 1 and `test`.`t1`.`a` < 7) `cte_e2` +Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2` drop table t1; # # MDEV-13753: embedded CTE in a VIEW created in prepared statement @@ -1725,4 +1725,42 @@ drop table db1.t1; drop database db1; create database test; use test; +# +# MDEV-24597: CTE with union used multiple times in query +# +with cte(a) as +(select 1 as d union select 2 as d) +select a from cte as r1 +union +select a from cte as r2; +a +1 +2 +create table t1 (a int, b int) engine=myisam; +insert into t1 values +(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44), +(5,50), (4,40), (3,33), (4,42), (4,43), (5,51); +with cte(c) as +(select a from t1 where b < 30 union select a from t1 where b > 40) +select * from cte as r1, cte as r2 where r1.c = r2.c; +c c +1 1 +2 2 +7 7 +4 4 +5 5 +with cte(a,c) as +( +select a, count(*) from t1 group by a having count(*) = 1 +union +select a, count(*) from t1 group by a having count(*) = 3 +) +select a, c from cte as r1 where a < 3 +union +select a, c from cte as r2 where a > 4; +a c +1 1 +2 1 +7 3 +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 0174ddb..c2ebc92 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1230,4 +1230,35 @@ drop database db1; create database test; use test; +--echo # +--echo # MDEV-24597: CTE with union used multiple times in query +--echo # + +with cte(a) as +(select 1 as d union select 2 as d) +select a from cte as r1 +union +select a from cte as r2; + +create table t1 (a int, b int) engine=myisam; +insert into t1 values +(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44), +(5,50), (4,40), (3,33), (4,42), (4,43), (5,51); + +with cte(c) as +(select a from t1 where b < 30 union select a from t1 where b > 40) +select * from cte as r1, cte as r2 where r1.c = r2.c; + +with cte(a,c) as +( + select a, count(*) from t1 group by a having count(*) = 1 + union + select a, count(*) from t1 group by a having count(*) = 3 +) +select a, c from cte as r1 where a < 3 +union +select a, c from cte as r2 where a > 4; + +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index e07a525..4929c2e 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1124,6 +1124,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) { if(!(derived= with_elem->clone_parsed_spec(thd, this))) return true; + derived->with_element= with_elem; } derived->first_select()->linkage= DERIVED_TABLE_TYPE; with_elem->inc_references();