diff options
Diffstat (limited to 'mysql-test/main/cte_nonrecursive.result')
| -rw-r--r-- | mysql-test/main/cte_nonrecursive.result | 1464 |
1 files changed, 1464 insertions, 0 deletions
diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result new file mode 100644 index 00000000000..534a386fe12 --- /dev/null +++ b/mysql-test/main/cte_nonrecursive.result @@ -0,0 +1,1464 @@ +create table t1 (a int, b varchar(32)); +insert into t1 values +(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd'); +insert into t1 values +(3,'eee'), (7,'bb'), (1,'fff'), (4,'ggg'); +create table t2 (c int); +insert into t2 values +(2), (4), (5), (3); +# select certain field in the specification of t +with t as (select a from t1 where b >= 'c') +select * from t2,t where t2.c=t.a; +c a +4 4 +3 3 +4 4 +select * from t2, (select a from t1 where b >= 'c') as t +where t2.c=t.a; +c a +4 4 +3 3 +4 4 +explain +with t as (select a from t1 where b >= 'c') +select * from t2,t where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select * from t2, (select a from t1 where b >= 'c') as t +where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# select '*' in the specification of t +with t as (select * from t1 where b >= 'c') +select * from t2,t where t2.c=t.a; +c a b +4 4 dd +3 3 eee +4 4 ggg +select * from t2, (select * from t1 where b >= 'c') as t +where t2.c=t.a; +c a b +4 4 dd +3 3 eee +4 4 ggg +explain +with t as (select * from t1 where b >= 'c') +select * from t2,t where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select * from t2, (select * from t1 where b >= 'c') as t +where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# rename fields returned by the specication when defining t +with t(f1,f2) as (select * from t1 where b >= 'c') +select * from t2,t where t2.c=t.f1; +c f1 f2 +4 4 dd +3 3 eee +4 4 ggg +explain +with t(f1,f2) as (select * from t1 where b >= 'c') +select * from t2,t where t2.c=t.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# materialized query specifying t +with t as (select a, count(*) from t1 where b >= 'c' group by a) +select * from t2,t where t2.c=t.a; +c a count(*) +4 4 2 +3 3 1 +select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t +where t2.c=t.a; +c a count(*) +4 4 2 +3 3 1 +explain +with t as (select a, count(*) from t1 where b >= 'c' group by a) +select * from t2,t where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +explain +select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t +where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +# specivication of t contains having +with t as (select a, count(*) from t1 where b >= 'c' + group by a having count(*)=1 ) +select * from t2,t where t2.c=t.a; +c a count(*) +3 3 1 +select * from t2, (select a, count(*) from t1 where b >= 'c' + group by a having count(*)=1) t +where t2.c=t.a; +c a count(*) +3 3 1 +# main query contains having +with t as (select * from t2 where c <= 4) +select a, count(*) from t1,t where t1.a=t.c group by a having count(*)=1; +a count(*) +3 1 +select a, count(*) from t1, (select * from t2 where c <= 4) t +where t1.a=t.c group by a having count(*)=1; +a count(*) +3 1 +# main query contains group by + order by +with t as (select * from t2 where c <= 4 ) +select a, count(*) from t1,t where t1.a=t.c group by a order by count(*); +a count(*) +3 1 +4 3 +select a, count(*) from t1, (select * from t2 where c <= 4 ) t +where t1.a=t.c group by a order by count(*); +a count(*) +3 1 +4 3 +# main query contains group by + order by + limit +with t as (select * from t2 where c <= 4 ) +select a, count(*) from t1,t +where t1.a=t.c group by a order by count(*) desc limit 1; +a count(*) +4 3 +select a, count(*) from t1, (select * from t2 where c <= 4 ) t +where t1.a=t.c group by a order by count(*) desc limit 1; +a count(*) +4 3 +# t is used in a subquery +with t as (select a from t1 where a<5) +select * from t2 where c in (select a from t); +c +4 +3 +select * from t2 +where c in (select a from (select a from t1 where a<5) as t); +c +4 +3 +explain +with t as (select a from t1 where a<5) +select * from t2 where c in (select a from t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where +explain +select * from t2 +where c in (select a from (select a from t1 where a<5) as t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where +# materialized t is used in a subquery +with t as (select count(*) as c from t1 where b >= 'c' group by a) +select * from t2 where c in (select c from t); +c +2 +select * from t2 +where c in (select c from (select count(*) as c from t1 +where b >= 'c' group by a) as t); +c +2 +explain +with t as (select count(*) as c from t1 where b >= 'c' group by a) +select * from t2 where c in (select c from t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived2> ref key0 key0 8 test.t2.c 2 Using where; FirstMatch(t2) +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +explain +select * from t2 +where c in (select c from (select count(*) as c from t1 +where b >= 'c' group by a) as t); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived3> ref key0 key0 8 test.t2.c 2 Using where; FirstMatch(t2) +3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +# two references to t specified by a query +# selecting a field: both in main query +with t as (select a from t1 where b >= 'c') +select * from t as r1, t as r2 where r1.a=r2.a; +a a +1 1 +1 1 +4 4 +4 4 +3 3 +1 1 +1 1 +4 4 +4 4 +select * from (select a from t1 where b >= 'c') as r1, +(select a from t1 where b >= 'c') as r2 +where r1.a=r2.a; +a a +1 1 +1 1 +4 4 +4 4 +3 3 +1 1 +1 1 +4 4 +4 4 +explain +with t as (select a from t1 where b >= 'c') +select * from t as r1, t as r2 where r1.a=r2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select * from (select a from t1 where b >= 'c') as r1, +(select a from t1 where b >= 'c') as r2 +where r1.a=r2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# two references to materialized t: both in main query +with t as (select distinct a from t1 where b >= 'c') +select * from t as r1, t as r2 where r1.a=r2.a; +a a +1 1 +4 4 +3 3 +select * from (select distinct a from t1 where b >= 'c') as r1, +(select distinct a from t1 where b >= 'c') as r2 +where r1.a=r2.a; +a a +1 1 +4 4 +3 3 +explain +with t as (select distinct a from t1 where b >= 'c') +select * from t as r1, t as r2 where r1.a=r2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 +3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary +explain +select * from (select distinct a from t1 where b >= 'c') as r1, +(select distinct a from t1 where b >= 'c') as r2 +where r1.a=r2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 +3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary +# two references to t specified by a query +# selecting all fields: both in main query +with t as (select * from t1 where b >= 'c') +select * from t as r1, t as r2 where r1.a=r2.a; +a b a b +1 ccc 1 ccc +1 fff 1 ccc +4 dd 4 dd +4 ggg 4 dd +3 eee 3 eee +1 ccc 1 fff +1 fff 1 fff +4 dd 4 ggg +4 ggg 4 ggg +select * from (select * from t1 where b >= 'c') as r1, +(select * from t1 where b >= 'c') as r2 +where r1.a=r2.a; +a b a b +1 ccc 1 ccc +1 fff 1 ccc +4 dd 4 dd +4 ggg 4 dd +3 eee 3 eee +1 ccc 1 fff +1 fff 1 fff +4 dd 4 ggg +4 ggg 4 ggg +explain +with t as (select * from t1 where b >= 'c') +select * from t as r1, t as r2 where r1.a=r2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select * from (select * from t1 where b >= 'c') as r1, +(select * from t1 where b >= 'c') as r2 +where r1.a=r2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# two references to t specifying explicitly column names +with t(c) as (select a from t1 where b >= 'c') +select * from t r1, t r2 where r1.c=r2.c; +c c +1 1 +1 1 +4 4 +4 4 +3 3 +1 1 +1 1 +4 4 +4 4 +# t two references of t used in different parts of a union +with t as (select a from t1 where b >= 'c') +select * from t where a < 2 +union +select * from t where a >= 4; +a +1 +4 +select * from (select a from t1 where b >= 'c') as t +where t.a < 2 +union +select * from (select a from t1 where b >= 'c') as t +where t.a >= 4; +a +1 +4 +explain +with t as (select a from t1 where b >= 'c') +select * from t where a < 2 +union +select * from t where a >= 4; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +3 UNION t1 ALL NULL NULL NULL NULL 8 Using where +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL +explain +select * from (select a from t1 where b >= 'c') as t +where t.a < 2 +union +select * from (select a from t1 where b >= 'c') as t +where t.a >= 4; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where +3 UNION t1 ALL NULL NULL NULL NULL 8 Using where +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL +# specification of t contains union +with t as (select a from t1 where b >= 'f' +union +select c as a from t2 where c < 4) +select * from t2,t where t2.c=t.a; +c a +2 2 +4 4 +3 3 +select * from t2, +(select a from t1 where b >= 'f' +union +select c as a from t2 where c < 4) as t +where t2.c=t.a; +c a +2 2 +4 4 +3 3 +explain +with t as (select a from t1 where b >= 'f' +union +select c as a from t2 where c < 4) +select * from t2,t where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where +3 UNION t2 ALL NULL NULL NULL NULL 4 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +explain +select * from t2, +(select a from t1 where b >= 'f' +union +select c as a from t2 where c < 4) as t +where t2.c=t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where +3 UNION t2 ALL NULL NULL NULL NULL 4 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +# t is defined in the with clause of a subquery +select t1.a,t1.b from t1,t2 +where t1.a>t2.c and +t2.c in (with t as (select * from t1 where t1.a<5) +select t2.c from t2,t where t2.c=t.a); +a b +4 aaaa +7 bb +7 bb +4 dd +7 bb +7 bb +4 ggg +select t1.a,t1.b from t1,t2 +where t1.a>t2.c and +t2.c in (select t2.c +from t2,(select * from t1 where t1.a<5) as t +where t2.c=t.a); +a b +4 aaaa +7 bb +7 bb +4 dd +7 bb +7 bb +4 ggg +explain +select t1.a,t1.b from t1,t2 +where t1.a>t2.c and +t2.c in (with t as (select * from t1 where t1.a<5) +select t2.c from t2,t where t2.c=t.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select t1.a,t1.b from t1,t2 +where t1.a>t2.c and +t2.c in (select t2.c +from t2,(select * from t1 where t1.a<5) as t +where t2.c=t.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# two different definitions of t: one in the with clause of the main query, +# the other in the with clause of a subquery +with t as (select c from t2 where c >= 4) +select t1.a,t1.b from t1,t +where t1.a=t.c and +t.c in (with t as (select * from t1 where t1.a<5) +select t2.c from t2,t where t2.c=t.a); +a b +4 aaaa +4 dd +4 ggg +select t1.a,t1.b from t1, (select c from t2 where c >= 4) as t +where t1.a=t.c and +t.c in (select t2.c from t2, (select * from t1 where t1.a<5) as t +where t2.c=t.a); +a b +4 aaaa +4 dd +4 ggg +explain +with t as (select c from t2 where c >= 4) +select t1.a,t1.b from t1,t +where t1.a=t.c and +t.c in (with t as (select * from t1 where t1.a<5) +select t2.c from t2,t where t2.c=t.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select t1.a,t1.b from t1, (select c from t2 where c >= 4) as t +where t1.a=t.c and +t.c in (select t2.c from t2, (select * from t1 where t1.a<5) as t +where t2.c=t.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# another with table tt is defined in the with clause of a subquery +# from the specification of t +with t as (select * from t1 +where a>2 and +b in (with tt as (select * from t2 where t2.c<5) +select t1.b from t1,tt where t1.a=tt.c)) +select t.a, count(*) from t1,t where t1.a=t.a group by t.a; +a count(*) +3 1 +4 9 +select t.a, count(*) +from t1, +(select * from t1 +where a>2 and +b in (select t1.b +from t1, +(select * from t2 where t2.c<5) as tt +where t1.a=tt.c)) as t +where t1.a=t.a group by t.a; +a count(*) +3 1 +4 9 +explain +with t as (select * from t1 +where a>2 and +b in (with tt as (select * from t2 where t2.c<5) +select t1.b from t1,tt where t1.a=tt.c)) +select t.a, count(*) from t1,t where t1.a=t.a group by t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 35 func 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +select t.a, count(*) +from t1, +(select * from t1 +where a>2 and +b in (select t1.b +from t1, +(select * from t2 where t2.c<5) as tt +where t1.a=tt.c)) as t +where t1.a=t.a group by t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 35 func 1 +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where +3 MATERIALIZED t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# with clause in the specification of a derived table +select * +from t1, +(with t as (select a from t1 where b >= 'c') +select * from t2,t where t2.c=t.a) as tt +where t1.b > 'f' and tt.a=t1.a; +a b c a +4 ggg 4 4 +4 ggg 4 4 +select * +from t1, +(select * from t2, +(select a from t1 where b >= 'c') as t +where t2.c=t.a) as tt +where t1.b > 'f' and tt.a=t1.a; +a b c a +4 ggg 4 4 +4 ggg 4 4 +explain +select * +from t1, +(with t as (select a from t1 where b >= 'c') +select * from t2,t where t2.c=t.a) as tt +where t1.b > 'f' and tt.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (incremental, BNL join) +explain +select * +from t1, +(select * from t2, +(select a from t1 where b >= 'c') as t +where t2.c=t.a) as tt +where t1.b > 'f' and tt.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (incremental, BNL join) +# with claused in the specification of a view +create view v1 as +with t as (select a from t1 where b >= 'c') +select * from t2,t where t2.c=t.a; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +select * from v1; +c a +4 4 +3 3 +4 4 +explain +select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# with claused in the specification of a materialized view +create view v2 as +with t as (select a, count(*) from t1 where b >= 'c' group by a) +select * from t2,t where t2.c=t.a; +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +select * from v2; +c a count(*) +4 4 2 +3 3 1 +explain +select * from v2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY <derived3> ref key0 key0 5 test.t2.c 2 +3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort +# with clause in the specification of a view that whose definition +# table alias for a with table +create view v3 as +with t(c) as (select a from t1 where b >= 'c') +select * from t r1 where r1.c=4; +show create view v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci +select * from v3; +c +4 +4 +# with clause in the specification of a view that whose definition +# two table aliases for for the same with table +create view v4(c,d) as +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 +select * from v4; +c d +4 4 +4 4 +4 4 +4 4 +explain +select * from v4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +drop view v1,v2,v3,v4; +# currently any views containing with clause are not updatable +create view v1(a) as +with t as (select a from t1 where b >= 'c') +select t.a from t2,t where t2.c=t.a; +update v1 set a=0 where a > 4; +ERROR HY000: The target table v1 of the UPDATE is not updatable +drop view v1; +# prepare of a query containing a definition of a with table t +prepare stmt1 from " +with t as (select a from t1 where b >= 'c') + select * from t2,t where t2.c=t.a; +"; +execute stmt1; +c a +4 4 +3 3 +4 4 +execute stmt1; +c a +4 4 +3 3 +4 4 +deallocate prepare stmt1; +# prepare of a query containing a definition of a materialized t +prepare stmt1 from " +with t as (select a, count(*) from t1 where b >= 'c' group by a) + select * from t2,t where t2.c=t.a; +"; +execute stmt1; +c a count(*) +4 4 2 +3 3 1 +execute stmt1; +c a count(*) +4 4 2 +3 3 1 +deallocate prepare stmt1; +# prepare of a query containing two references to with table t +prepare stmt1 from " +with t as (select * from t1 where b >= 'c') + select * from t as r1, t as r2 where r1.a=r2.a; +"; +execute stmt1; +a b a b +1 ccc 1 ccc +1 fff 1 ccc +4 dd 4 dd +4 ggg 4 dd +3 eee 3 eee +1 ccc 1 fff +1 fff 1 fff +4 dd 4 ggg +4 ggg 4 ggg +execute stmt1; +a b a b +1 ccc 1 ccc +1 fff 1 ccc +4 dd 4 dd +4 ggg 4 dd +3 eee 3 eee +1 ccc 1 fff +1 fff 1 fff +4 dd 4 ggg +4 ggg 4 ggg +deallocate prepare stmt1; +with t(f) as (select * from t1 where b >= 'c') +select * from t2,t where t2.c=t.f1; +ERROR HY000: WITH column list and SELECT field list have different column counts +with t(f1,f1) as (select * from t1 where b >= 'c') +select * from t2,t where t2.c=t.f1; +ERROR 42S21: Duplicate column name 'f1' +with t as (select * from t2 where c>3), +t as (select a from t1 where a>2) +select * from t,t1 where t1.a=t.c; +ERROR HY000: Duplicate query name `t` in WITH clause +with t as (select a from s where a<5), +s as (select a from t1 where b>='d') +select * from t,s where t.a=s.a; +ERROR 42S02: Table 'test.s' doesn't exist +with recursive +t as (select a from s where a<5), +s as (select a from t1 where b>='d') +select * from t,s where t.a=s.a; +a a +4 4 +4 4 +3 3 +1 1 +4 4 +4 4 +with recursive t as (select * from s where a>2), +s as (select a from t1,r where t1.a>r.c), +r as (select c from t,t2 where t.a=t2.c) +select * from r where r.c<7; +ERROR HY000: No anchors for recursive WITH element 't' +with recursive +t as (select * from s where a>2), +s as (select a from t1,r where t1.a>r.c), +r as (select c from t,t2 where t.a=t2.c) +select * from r where r.c<7; +ERROR HY000: No anchors for recursive WITH element 't' +with recursive +t as (select * from t1 +where a in (select c from s where b<='ccc') and b>'b'), +s as (select * from t1,t2 +where t1.a=t2.c and t1.c in (select a from t where a<5)) +select * from s where s.b>'aaa'; +ERROR HY000: No anchors for recursive WITH element 't' +with recursive +t as (select * from t1 where b>'aaa' and b <='d') +select t.b from t,t2 +where t.a=t2.c and +t2.c in (with recursive +s as (select t1.a from s,t1 where t1.a=s.a and t1.b<'c') +select * from s); +ERROR HY000: No anchors for recursive WITH element 's' +#erroneous definition of unreferenced with table t +with t as (select count(*) from t1 where d>='f' group by a) +select t1.b from t2,t1 where t1.a = t2.c; +ERROR 42S22: Unknown column 'd' in 'where clause' +with t as (select count(*) from t1 where b>='f' group by a) +select t1.b from t2,t1 where t1.a = t2.c; +b +aaaa +dd +eee +ggg +#erroneous definition of s referring to unreferenced t +with t(d) as (select count(*) from t1 where b<='ccc' group by b), +s as (select * from t1 where a in (select t2.d from t2,t where t2.c=t.d)) +select t1.b from t1,t2 where t1.a=t2.c; +ERROR 42S22: Unknown column 't2.d' in 'field list' +with t(d) as (select count(*) from t1 where b<='ccc' group by b), +s as (select * from t1 where a in (select t2.c from t2,t where t2.c=t.c)) +select t1.b from t1,t2 where t1.a=t2.c; +ERROR 42S22: Unknown column 't.c' in 'where clause' +with t(d) as (select count(*) from t1 where b<='ccc' group by b), +s as (select * from t1 where a in (select t2.c from t2,t where t2.c=t.d)) +select t1.b from t1,t2 where t1.a=t2.c; +b +aaaa +dd +eee +ggg +#erroneous definition of unreferenced with table t +with t(f) as (select * from t1 where b >= 'c') +select t1.b from t2,t1 where t1.a = t2.c; +ERROR HY000: WITH column list and SELECT field list have different column counts +#erroneous definition of unreferenced with table t +with t(f1,f1) as (select * from t1 where b >= 'c') +select t1.b from t2,t1 where t1.a = t2.c; +ERROR 42S21: Duplicate column name 'f1' +# explain for query with unreferenced with table +explain +with t as (select a from t1 where b >= 'c') +select t1.b from t2,t1 where t1.a = t2.c; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +explain +with t as (select a, count(*) from t1 where b >= 'c' group by a) +select t1.b from t2,t1 where t1.a = t2.c; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 +1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +# too many with elements in with clause +with s65 as (select * from t1), s64 as (select * from t1) , s63 as (select * from t1) , s62 as (select * from t1) , s61 as (select * from t1) , s60 as (select * from t1) , s59 as (select * from t1) , s58 as (select * from t1) , s57 as (select * from t1) , s56 as (select * from t1) , s55 as (select * from t1) , s54 as (select * from t1) , s53 as (select * from t1) , s52 as (select * from t1) , s51 as (select * from t1) , s50 as (select * from t1) , s49 as (select * from t1) , s48 as (select * from t1) , s47 as (select * from t1) , s46 as (select * from t1) , s45 as (select * from t1) , s44 as (select * from t1) , s43 as (select * from t1) , s42 as (select * from t1) , s41 as (select * from t1) , s40 as (select * from t1) , s39 as (select * from t1) , s38 as (select * from t1) , s37 as (select * from t1) , s36 as (select * from t1) , s35 as (select * from t1) , s34 as (select * from t1) , s33 as (select * from t1) , s32 as (select * from t1) , s31 as (select * from t1) , s30 as (select * from t1) , s29 as (select * from t1) , s28 as (select * from t1) , s27 as (select * from t1) , s26 as (select * from t1) , s25 as (select * from t1) , s24 as (select * from t1) , s23 as (select * from t1) , s22 as (select * from t1) , s21 as (select * from t1) , s20 as (select * from t1) , s19 as (select * from t1) , s18 as (select * from t1) , s17 as (select * from t1) , s16 as (select * from t1) , s15 as (select * from t1) , s14 as (select * from t1) , s13 as (select * from t1) , s12 as (select * from t1) , s11 as (select * from t1) , s10 as (select * from t1) , s9 as (select * from t1) , s8 as (select * from t1) , s7 as (select * from t1) , s6 as (select * from t1) , s5 as (select * from t1) , s4 as (select * from t1) , s3 as (select * from t1) , s2 as (select * from t1) , s1 as (select * from t1) select * from s65; +ERROR HY000: Too many WITH elements in WITH clause +drop table t1,t2; +# +# Bug mdev-9937: View used in the specification of with table +# refers to the base table with the same name +# +create table t1 (a int); +insert into t1 values (20), (30), (10); +create view v1 as select * from t1 where a > 10; +with t1 as (select * from v1) select * from t1; +a +20 +30 +drop view v1; +drop table t1; +# +# Bug mdev-10058: Invalid derived table with WITH clause +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1 +SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1; +a +1 +2 +3 +DROP TABLE t1,t2,t3; +# +# Bug mdev-10344: the WITH clause of the query refers to a view that uses +# a base table with the same name as a CTE table from the clause +# +create table ten(a int primary key); +insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k(a int primary key); +insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; +create view v1 as select * from ten; +select * from v1; +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +drop view v1; +drop table ten, one_k; +# +# MDEV-10057 : Crash with EXPLAIN + WITH + constant query +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1; +1 +1 +EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +DROP TABLE t1; +# +# MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined table +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1 +explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +DROP TABLE t1,t2,t3; +# +# MDEV-10729: Server crashes in st_select_lex::set_explain_type +# +CREATE TABLE t1 (i1 INT, KEY(i1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(8); +CREATE TABLE t2 (a2 INT, b2 INT, KEY(b2)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8,7); +CREATE TABLE t3 (i3 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (2),(6); +SELECT * FROM t1, t2 WHERE a2 = i1 and b2 >= i1 AND i1 IN ( SELECT i3 FROM t3 ) +UNION +SELECT * FROM t1, t2 WHERE a2 = i1 and b2 >= i1 AND i1 IN ( SELECT i3 FROM t3 ) +; +i1 a2 b2 +DROP TABLE t1,t2,t3; +# +# MDEV-10923: mergeable CTE used twice in the query +# +create table employees ( +name varchar(32), +dept varchar(32), +country varchar(8) +); +insert into employees +values +('Sergei Golubchik', 'Development', 'DE'), +('Claudio Nanni', 'Support', 'ES'), +('Sergei Petrunia', 'Development', 'RU'); +with eng as +( +select * from employees +where dept in ('Development','Support') +), +eu_eng as +( +select * from eng where country IN ('DE','ES','RU') +) +select * from eu_eng T1 +where +not exists (select 1 from eu_eng T2 +where T2.country=T1.country +and T2.name <> T1.name); +name dept country +Sergei Golubchik Development DE +Claudio Nanni Support ES +Sergei Petrunia Development RU +drop table employees; +# +# MDEV-11818: EXPLAIN EXTENDED for a query with optimized away CTE table +# +CREATE TABLE t1 (i INT, c VARCHAR(3)); +INSERT INTO t1 VALUES (1,'foo'); +EXPLAIN EXTENDED +WITH cte AS ( SELECT * FROM t1 ) SELECT i FROM cte; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1003 with cte as (select `test`.`t1`.`i` AS `i`,`test`.`t1`.`c` AS `c` from `test`.`t1`)select 1 AS `i` from dual +DROP TABLE t1; +# +# MDEV-12185: view defintion contains WITH clause with +# several specifications of CTE +# +with +alias1 as (select 1 as one), +alias2 as (select 2 as two) +select one, two from alias1, alias2; +one two +1 2 +create view v1 as +with +alias1 as (select 1 as one), +alias2 as (select 2 as two) +select one, two from alias1, alias2; +select * from v1; +one two +1 2 +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with alias1 as (select 1 AS `one`), alias2 as (select 2 AS `two`)select `alias1`.`one` AS `one`,`alias2`.`two` AS `two` from (`alias1` join `alias2`) latin1 latin1_swedish_ci +drop view v1; +# +# MDEV-12440: the same CTE table is used twice +# +create table t1 (a int, b varchar(32)); +insert into t1 values +(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd'); +# cte2 is used in the main query and in the spec for ct3 +with +cte1 as (select * from t1 where b >= 'c'), +cte2 as (select * from cte1 where a < 7), +cte3 as (select * from cte2 where a > 1) +select * from cte2, cte3 where cte2.a = cte3.a; +a b a b +4 dd 4 dd +# cte2 is used twice in the spec for ct3 +with +cte1 as (select * from t1 where b >= 'b'), +cte2 as (select * from cte1 where b > 'c'), +cte3 as (select * from cte2 where a > 1 union select * from cte2 where a > 1) +select * from cte3; +a b +4 dd +drop table t1; +# +# MDEV-12558: CTE with the same name as temporary table +# +CREATE TABLE t ENGINE=MyISAM AS SELECT 1 AS i; +CREATE TEMPORARY TABLE cte ENGINE=MyISAM AS SELECT 2 AS f; +WITH cte AS ( SELECT i FROM t ) SELECT * FROM cte; +i +1 +WITH cte AS ( SELECT i FROM t GROUP BY i) SELECT * FROM cte; +i +1 +SELECT * FROM cte; +f +2 +DROP TABLE cte; +DROP TABLE t; +# +# MDEV-13107: SHOW TABLE STATUS, SHOW CREATE VIEW +# for CTEs that use derived tables +# +create table t1(a int) engine=myisam; +insert into t1 values (3), (1), (2); +create table t2 (b int) engine=myisam; +insert into t2 values (2), (10); +create view v1 as +with t as (select s.a from (select t1.a from t1) s), +r as(select t.a from t2, t where t2.b=t.a) +select a from r; +create view v2 as +with t as (select s.a from (select t1.a from t1) s), +r as(select t.a from t2, t where t2.b=t.a) +select a from t1; +show table status; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `s`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `s`), r as (select `t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`b` = `t`.`a`)select `r`.`a` AS `a` from `r` latin1 latin1_swedish_ci +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `s`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `s`), r as (select `t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`b` = `t`.`a`)select `test`.`t1`.`a` AS `a` from `test`.`t1` latin1 latin1_swedish_ci +select * from v1; +a +2 +select * from v2; +a +3 +1 +2 +prepare stmt1 from "select * from v1"; +execute stmt1; +a +2 +execute stmt1; +a +2 +prepare stmt2 from "select * from v2"; +execute stmt2; +a +3 +1 +2 +execute stmt2; +a +3 +1 +2 +deallocate prepare stmt1; +deallocate prepare stmt2; +drop view v1,v2; +drop table t1,t2; +# +# MDEV-13796: UNION of two materialized CTEs +# +CREATE TABLE t1 (id int, k int); +CREATE TABLE t2 (id int); +INSERT INTO t1 VALUES (3,5), (1,7), (4,3); +INSERT INTO t2 VALUES (4), (3), (2); +WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id), +d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id) +SELECT * FROM d1 UNION SELECT * FROM d2; +SUM(k) +8 +explain WITH d1 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id), +d2 AS (SELECT SUM(k) FROM t1, t2 as t2 WHERE t1.id = t2.id) +SELECT * FROM d1 UNION SELECT * FROM d2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +4 UNION <derived3> ALL NULL NULL NULL NULL 9 +3 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1,t2; +# +# MDEV-13780: tower of embedding CTEs with multiple usage of them +# +create table t1 (a int); +insert into t1 values (3), (2), (4), (7), (1), (2), (5); +with cte_e as +( +with cte_o as +( +with cte_i as (select * from t1 where a < 7) +select * from cte_i where a > 1 +) +select * from cte_o as cto_o1 where a < 3 +union +select * from cte_o as cto_o2 where a > 4 +) +select * from cte_e as cte_e1 where a > 1 +union +select * from cte_e as cte_e2; +a +2 +5 +explain extended with cte_e as +( +with cte_o as +( +with cte_i as (select * from t1 where a < 7) +select * from cte_i where a > 1 +) +select * from cte_o as cto_o1 where a < 3 +union +select * from cte_o as cto_o2 where a > 4 +) +select * from cte_e as cte_e1 where a > 1 +union +select * from cte_e as cte_e2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 14 100.00 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using where +5 UNION t1 ALL NULL NULL NULL NULL 7 100.00 Using where +NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL +6 UNION <derived9> ALL NULL NULL NULL NULL 14 100.00 +9 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using where +12 UNION t1 ALL NULL NULL NULL NULL 7 100.00 Using where +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#4 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#2 */ 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#5 */ 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#1 */ select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union /* select#6 */ select `cte_e2`.`a` AS `a` from (with cte_o as (with cte_i as (/* select#11 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#10 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#9 */ 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#12 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7) `cte_e2` +drop table t1; +# +# MDEV-13753: embedded CTE in a VIEW created in prepared statement +# +SET @sql_query = " + CREATE OR REPLACE VIEW cte_test AS + WITH cte1 AS ( SELECT 1 as a from dual ) + , cte2 AS ( SELECT * FROM cte1 ) + SELECT * FROM cte2; +"; +PREPARE stmt FROM @sql_query; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +SHOW CREATE VIEW cte_test; +View Create View character_set_client collation_connection +cte_test CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cte_test` AS with cte1 as (select 1 AS `a`), cte2 as (select `cte1`.`a` AS `a` from `cte1`)select `cte2`.`a` AS `a` from `cte2` latin1 latin1_swedish_ci +SELECT * FROM cte_test; +a +1 +DROP VIEW cte_test; +# +# mdev-14755 : PS for query using CTE in select with subquery +# +create table t1 (a int); +insert into t1 values +(7), (2), (8), (1), (3), (2), (7), (5), (4), (7), (9), (8); +with cte as +(select a from t1 where a between 4 and 7 group by a) +(select a from cte where exists( select a from t1 where cte.a=t1.a )) +union +(select a from t1 where a < 2); +a +7 +5 +4 +1 +prepare stmt from "with cte as +(select a from t1 where a between 4 and 7 group by a) +(select a from cte where exists( select a from t1 where cte.a=t1.a )) +union +(select a from t1 where a < 2)"; +execute stmt; +a +7 +5 +4 +1 +execute stmt; +a +7 +5 +4 +1 +deallocate prepare stmt; +with cte as +(select a from t1 where a between 4 and 7 group by a) +(select a from t1 where a < 2) +union +(select a from cte where exists( select a from t1 where cte.a=t1.a )); +a +1 +7 +5 +4 +prepare stmt from "with cte as +(select a from t1 where a between 4 and 7 group by a) +(select a from t1 where a < 2) +union +(select a from cte where exists( select a from t1 where cte.a=t1.a ))"; +execute stmt; +a +1 +7 +5 +4 +execute stmt; +a +1 +7 +5 +4 +deallocate prepare stmt; +with cte as +(select a from t1 where a between 4 and 7) +(select a from t1 where a < 2) +union +(select a from cte where exists( select a from t1 where cte.a=t1.a )); +a +1 +7 +5 +4 +prepare stmt from "with cte as +(select a from t1 where a between 4 and 7) +(select a from t1 where a < 2) +union +(select a from cte where exists( select a from t1 where cte.a=t1.a ))"; +execute stmt; +a +1 +7 +5 +4 +execute stmt; +a +1 +7 +5 +4 +deallocate prepare stmt; +with cte as +(select a from t1 where a between 4 and 7) +(select a from cte +where exists( select a from t1 where t1.a < 2 and cte.a=t1.a )) +union +(select a from cte where exists( select a from t1 where cte.a=t1.a )); +a +7 +5 +4 +prepare stmt from "with cte as +(select a from t1 where a between 4 and 7) +(select a from cte +where exists( select a from t1 where t1.a < 2 and cte.a=t1.a )) +union +(select a from cte where exists( select a from t1 where cte.a=t1.a ))"; +execute stmt; +a +7 +5 +4 +execute stmt; +a +7 +5 +4 +deallocate prepare stmt; +drop table t1; +# +# MDEV-14852: CTE using temporary table in query +# with two references to the CTE +# +create temporary table t1 (i int); +insert into t1 values (5),(4),(1),(2),(3); +with +c1 as (select i from t1), +c2 as (select i from c1 where c1.i=2) +select i from c1 where i > 3 union select i from c2; +i +5 +4 +2 +drop table t1; +create table t1 (term char(10)); +create temporary table t2 (term char(10)); +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); +with c1 as (select * from t1), c2 as (select * from t2) +(select * from c1 left outer join c2 on c1.term = c2.term) +union all +(select * from c1 right outer join c2 on c1.term = c2.term +where c1.term is null); +term term +TERM02 TERM02 +TERM03 TERM03 +TERM01 NULL +NULL TERM04 +drop table t1,t2; +# +# MDEV-14969: view using subquery with attached CTE +# +create table region ( +r_regionkey int, +r_name char(25), +primary key (r_regionkey) +); +insert into region values +(0,'AFRICA'), (1,'AMERICA'), (2,'ASIA'), (3,'EUROPE'), (4,'MIDDLE EAST'); +create table nation ( +n_nationkey int, +n_name char(25), +n_regionkey int, +primary key (n_nationkey), +key i_n_regionkey (n_regionkey) +); +insert into nation values +(0,'ALGERIA',0), (1,'ARGENTINA',1), (2,'BRAZIL',1), (3,'CANADA',1), +(4,'EGYPT',4), (5,'ETHIOPIA',0), (6,'FRANCE',3), (7,'GERMANY',3), +(8,'INDIA',2), (9,'INDONESIA',2), (10,'IRAN',4), (11,'IRAQ',4), +(12,'JAPAN',2), (13,'JORDAN',4), (14,'KENYA',0), (15,'MOROCCO',0), +(16,'MOZAMBIQUE',0), (17,'PERU',1), (18,'CHINA',2), (19,'ROMANIA',3), +(20,'SAUDI ARABIA',4), (21,'VIETNAM',2), (22,'RUSSIA',3), +(23,'UNITED KINGDOM',3), (24,'UNITED STATES',1); +select * from nation n ,region r +where n.n_regionkey = r.r_regionkey and +r.r_regionkey in +(with t as (select * from region where r_regionkey <= 3 ) +select r_regionkey from t where r_name <> "ASIA"); +n_nationkey n_name n_regionkey r_regionkey r_name +0 ALGERIA 0 0 AFRICA +5 ETHIOPIA 0 0 AFRICA +14 KENYA 0 0 AFRICA +15 MOROCCO 0 0 AFRICA +16 MOZAMBIQUE 0 0 AFRICA +1 ARGENTINA 1 1 AMERICA +2 BRAZIL 1 1 AMERICA +3 CANADA 1 1 AMERICA +17 PERU 1 1 AMERICA +24 UNITED STATES 1 1 AMERICA +6 FRANCE 3 3 EUROPE +7 GERMANY 3 3 EUROPE +19 ROMANIA 3 3 EUROPE +22 RUSSIA 3 3 EUROPE +23 UNITED KINGDOM 3 3 EUROPE +create view v as +select * from nation n ,region r +where n.n_regionkey = r.r_regionkey and +r.r_regionkey in +(with t as (select * from region where r_regionkey <= 3) +select r_regionkey from t where r_name <> "ASIA"); +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci +select * from v; +n_nationkey n_name n_regionkey r_regionkey r_name +0 ALGERIA 0 0 AFRICA +5 ETHIOPIA 0 0 AFRICA +14 KENYA 0 0 AFRICA +15 MOROCCO 0 0 AFRICA +16 MOZAMBIQUE 0 0 AFRICA +1 ARGENTINA 1 1 AMERICA +2 BRAZIL 1 1 AMERICA +3 CANADA 1 1 AMERICA +17 PERU 1 1 AMERICA +24 UNITED STATES 1 1 AMERICA +6 FRANCE 3 3 EUROPE +7 GERMANY 3 3 EUROPE +19 ROMANIA 3 3 EUROPE +22 RUSSIA 3 3 EUROPE +23 UNITED KINGDOM 3 3 EUROPE +drop view v; +drop table region, nation; +# +# MDEV-15120: cte name used with database name +# +WITH cte AS (SELECT 1 AS a) SELECT test.cte.a FROM test.cte; +ERROR 42S02: Table 'test.cte' doesn't exist +CREATE DATABASE db1; +USE db1; +WITH cte AS (SELECT 1 AS a) SELECT db1.cte.a FROM db1.cte; +ERROR 42S02: Table 'db1.cte' doesn't exist +DROP DATABASE db1; +USE test; +# +# MDEV-15119: CTE c2 specified after CTE c1 and is used in +# CTE c3 that is embedded into the spec of c1 +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +WITH c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3), +c2 AS (SELECT * FROM t1) +SELECT * FROM c1; +ERROR 42S02: Table 'test.c2' doesn't exist +WITH RECURSIVE c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3), +c2 AS (SELECT * FROM t1) +SELECT * FROM c1; +i +1 +2 +3 +DROP TABLE t1; +# +# MDEV-14297: Lost name of a explicitly named CTE column used in +# the non-recursive CTE via prepared statement +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1),(2),(3); +PREPARE stmt FROM "WITH cte(a) AS (SELECT 1) SELECT * FROM cte"; +EXECUTE stmt; +a +1 +DEALLOCATE PREPARE stmt; +PREPARE stmt FROM "CREATE VIEW v1 AS WITH cte(a) AS (SELECT 1) SELECT * FROM cte"; +EXECUTE stmt; +SELECT * FROM v1; +a +1 +DEALLOCATE PREPARE stmt; +PREPARE stmt FROM "CREATE VIEW v2 AS WITH cte(a) AS (SELECT * FROM t1) SELECT * FROM cte"; +EXECUTE stmt; +SELECT * FROM v2; +a +1 +2 +3 +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +DROP VIEW v1,v2; +# +# MDEV-15478: Lost name of a explicitly named CTE column used in +# the non-recursive CTE defined with UNION +# +CREATE TABLE t1 (x int, y int); +INSERT INTO t1 VALUES (1,2),(2,7),(3,3); +WITH cte(a) AS (SELECT 1 UNION SELECT 2) SELECT * FROM cte; +a +1 +2 +WITH cte(a) AS (SELECT 1 UNION SELECT 2) SELECT a FROM cte; +a +1 +2 +WITH cte(a) AS (SELECT 1 UNION ALL SELECT 1) SELECT a FROM cte; +a +1 +1 +WITH cte(a) AS (SELECT x from t1 UNION SELECT 4) SELECT a FROM cte; +a +1 +2 +3 +4 +WITH cte(a) AS (SELECT 4 UNION SELECT x FROM t1 UNION SELECT 5) +SELECT a FROM cte; +a +4 +1 +2 +3 +5 +WITH cte(a,b) AS (SELECT 4,5 UNION SELECT 4,3) SELECT a,b FROM cte; +a b +4 5 +4 3 +DROP TABLE t1; |