mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
ad5b9c207c
This is a limitation of the view protocol. Tests were fixed with workaround (via disable/enable service connection)
2028 lines
51 KiB
Text
2028 lines
51 KiB
Text
--source include/default_optimizer_switch.inc
|
|
|
|
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);
|
|
|
|
--echo # 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;
|
|
select * from t2, (select a from t1 where b >= 'c') as t
|
|
where t2.c=t.a;
|
|
explain
|
|
with t as (select a from t1 where b >= 'c')
|
|
select * from t2,t where t2.c=t.a;
|
|
explain
|
|
select * from t2, (select a from t1 where b >= 'c') as t
|
|
where t2.c=t.a;
|
|
|
|
--echo # select '*' in the specification of t
|
|
with t as (select * from t1 where b >= 'c')
|
|
select * from t2,t where t2.c=t.a;
|
|
select * from t2, (select * from t1 where b >= 'c') as t
|
|
where t2.c=t.a;
|
|
explain
|
|
with t as (select * from t1 where b >= 'c')
|
|
select * from t2,t where t2.c=t.a;
|
|
explain
|
|
select * from t2, (select * from t1 where b >= 'c') as t
|
|
where t2.c=t.a;
|
|
|
|
--echo # 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;
|
|
explain
|
|
with t(f1,f2) as (select * from t1 where b >= 'c')
|
|
select * from t2,t where t2.c=t.f1;
|
|
|
|
--echo # 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;
|
|
select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t
|
|
where t2.c=t.a;
|
|
explain
|
|
with t as (select a, count(*) from t1 where b >= 'c' group by a)
|
|
select * from t2,t where t2.c=t.a;
|
|
explain
|
|
select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t
|
|
where t2.c=t.a;
|
|
|
|
--echo # 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;
|
|
select * from t2, (select a, count(*) from t1 where b >= 'c'
|
|
group by a having count(*)=1) t
|
|
where t2.c=t.a;
|
|
|
|
--echo # 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;
|
|
select a, count(*) from t1, (select * from t2 where c <= 4) t
|
|
where t1.a=t.c group by a having count(*)=1;
|
|
|
|
--echo # 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(*);
|
|
select a, count(*) from t1, (select * from t2 where c <= 4 ) t
|
|
where t1.a=t.c group by a order by count(*);
|
|
|
|
--echo # 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;
|
|
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;
|
|
|
|
|
|
--echo # 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);
|
|
select * from t2
|
|
where c in (select a from (select a from t1 where a<5) as t);
|
|
explain
|
|
with t as (select a from t1 where a<5)
|
|
select * from t2 where c in (select a from t);
|
|
explain
|
|
select * from t2
|
|
where c in (select a from (select a from t1 where a<5) as t);
|
|
|
|
--echo # 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);
|
|
select * from t2
|
|
where c in (select c from (select count(*) as c from t1
|
|
where b >= 'c' group by a) as t);
|
|
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);
|
|
explain
|
|
select * from t2
|
|
where c in (select c from (select count(*) as c from t1
|
|
where b >= 'c' group by a) as t);
|
|
|
|
--echo # two references to t specified by a query
|
|
--echo # 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;
|
|
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;
|
|
explain
|
|
with t as (select a from t1 where b >= 'c')
|
|
select * from t as r1, t as r2 where r1.a=r2.a;
|
|
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;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
|
|
--echo # two references to t specified by a query
|
|
--echo # 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;
|
|
select * from (select * from t1 where b >= 'c') as r1,
|
|
(select * from t1 where b >= 'c') as r2
|
|
where r1.a=r2.a;
|
|
explain
|
|
with t as (select * from t1 where b >= 'c')
|
|
select * from t as r1, t as r2 where r1.a=r2.a;
|
|
explain
|
|
select * from (select * from t1 where b >= 'c') as r1,
|
|
(select * from t1 where b >= 'c') as r2
|
|
where r1.a=r2.a;
|
|
|
|
--echo # 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;
|
|
|
|
--echo # 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;
|
|
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;
|
|
explain
|
|
with t as (select a from t1 where b >= 'c')
|
|
select * from t where a < 2
|
|
union
|
|
select * from t where a >= 4;
|
|
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;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
|
|
--echo # 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);
|
|
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);
|
|
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);
|
|
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);
|
|
|
|
--echo # two different definitions of t: one in the with clause of the main query,
|
|
--echo # 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);
|
|
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);
|
|
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);
|
|
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);
|
|
|
|
--echo # another with table tt is defined in the with clause of a subquery
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
|
|
--echo # 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;
|
|
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;
|
|
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;
|
|
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;
|
|
|
|
--echo # 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;
|
|
select * from v1;
|
|
explain
|
|
select * from v1;
|
|
|
|
--echo # 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;
|
|
select * from v2;
|
|
explain
|
|
select * from v2;
|
|
|
|
--echo # with clause in the specification of a view that whose definition
|
|
--echo # 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;
|
|
select * from v3;
|
|
|
|
--echo # with clause in the specification of a view that whose definition
|
|
--echo # 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;
|
|
select * from v4;
|
|
explain
|
|
select * from v4;
|
|
|
|
drop view v1,v2,v3,v4;
|
|
|
|
|
|
--echo # 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;
|
|
--error ER_NON_UPDATABLE_TABLE
|
|
update v1 set a=0 where a > 4;
|
|
drop view v1;
|
|
|
|
|
|
--echo # 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;
|
|
execute stmt1;
|
|
deallocate prepare stmt1;
|
|
|
|
--echo # 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;
|
|
execute stmt1;
|
|
deallocate prepare stmt1;
|
|
|
|
--echo # 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;
|
|
execute stmt1;
|
|
deallocate prepare stmt1;
|
|
|
|
--ERROR ER_WITH_COL_WRONG_LIST
|
|
with t(f) as (select * from t1 where b >= 'c')
|
|
select * from t2,t where t2.c=t.f1;
|
|
|
|
--ERROR ER_DUP_FIELDNAME
|
|
with t(f1,f1) as (select * from t1 where b >= 'c')
|
|
select * from t2,t where t2.c=t.f1;
|
|
|
|
--ERROR ER_DUP_QUERY_NAME
|
|
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 ER_NO_SUCH_TABLE
|
|
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;
|
|
|
|
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;
|
|
|
|
--ERROR ER_RECURSIVE_WITHOUT_ANCHORS
|
|
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 ER_RECURSIVE_WITHOUT_ANCHORS
|
|
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 ER_RECURSIVE_WITHOUT_ANCHORS
|
|
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 ER_RECURSIVE_WITHOUT_ANCHORS
|
|
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);
|
|
--echo #erroneous definition of unreferenced with table t
|
|
--ERROR ER_BAD_FIELD_ERROR
|
|
with t as (select count(*) from t1 where d>='f' group by a)
|
|
select t1.b from t2,t1 where t1.a = t2.c;
|
|
|
|
with t as (select count(*) from t1 where b>='f' group by a)
|
|
select t1.b from t2,t1 where t1.a = t2.c;
|
|
|
|
--echo #erroneous definition of s referring to unreferenced t
|
|
--ERROR ER_BAD_FIELD_ERROR
|
|
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 ER_BAD_FIELD_ERROR
|
|
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;
|
|
|
|
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;
|
|
|
|
--echo #erroneous definition of unreferenced with table t
|
|
--ERROR ER_WITH_COL_WRONG_LIST
|
|
with t(f) as (select * from t1 where b >= 'c')
|
|
select t1.b from t2,t1 where t1.a = t2.c;
|
|
|
|
--echo #erroneous definition of unreferenced with table t
|
|
--ERROR ER_DUP_FIELDNAME
|
|
with t(f1,f1) as (select * from t1 where b >= 'c')
|
|
select t1.b from t2,t1 where t1.a = t2.c;
|
|
|
|
--echo # 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;
|
|
|
|
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;
|
|
|
|
--echo # too many with elements in with clause
|
|
let $m= 65;
|
|
let $i= $m;
|
|
dec $i;
|
|
let $q= with s$m as (select * from t1);
|
|
while ($i)
|
|
{
|
|
let $q= $q, s$i as (select * from t1) ;
|
|
dec $i;
|
|
}
|
|
let $q= $q select * from s$m;
|
|
--ERROR ER_TOO_MANY_DEFINITIONS_IN_WITH_CLAUSE
|
|
eval $q;
|
|
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # Bug mdev-9937: View used in the specification of with table
|
|
--echo # refers to the base table with the same name
|
|
--echo #
|
|
|
|
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;
|
|
|
|
drop view v1;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # Bug mdev-10058: Invalid derived table with WITH clause
|
|
--echo #
|
|
|
|
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);
|
|
|
|
--ERROR ER_PARSE_ERROR
|
|
SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
|
|
|
|
SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
|
|
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
--echo #
|
|
--echo # Bug mdev-10344: the WITH clause of the query refers to a view that uses
|
|
--echo # a base table with the same name as a CTE table from the clause
|
|
--echo #
|
|
|
|
|
|
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;
|
|
|
|
drop view v1;
|
|
drop table ten, one_k;
|
|
|
|
--echo #
|
|
--echo # MDEV-10057 : Crash with EXPLAIN + WITH + constant query
|
|
--echo #
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1;
|
|
EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined table
|
|
--echo #
|
|
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);
|
|
--error ER_PARSE_ERROR
|
|
EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
|
|
explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
--echo #
|
|
--echo # MDEV-10729: Server crashes in st_select_lex::set_explain_type
|
|
--echo #
|
|
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 )
|
|
;
|
|
DROP TABLE t1,t2,t3;
|
|
|
|
--echo #
|
|
--echo # MDEV-10923: mergeable CTE used twice in the query
|
|
--echo #
|
|
|
|
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);
|
|
|
|
drop table employees;
|
|
|
|
--echo #
|
|
--echo # MDEV-11818: EXPLAIN EXTENDED for a query with optimized away CTE table
|
|
--echo #
|
|
|
|
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;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-12185: view defintion contains WITH clause with
|
|
--echo # several specifications of CTE
|
|
--echo #
|
|
|
|
with
|
|
alias1 as (select 1 as one),
|
|
alias2 as (select 2 as two)
|
|
select one, two from alias1, alias2;
|
|
|
|
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;
|
|
show create view v1;
|
|
|
|
drop view v1;
|
|
|
|
--echo #
|
|
--echo # MDEV-12440: the same CTE table is used twice
|
|
--echo #
|
|
|
|
create table t1 (a int, b varchar(32));
|
|
insert into t1 values
|
|
(4,'aaaa' ), (7,'bb'), (1,'ccc'), (4,'dd');
|
|
|
|
--echo # 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;
|
|
|
|
--echo # 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;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-12558: CTE with the same name as temporary table
|
|
--echo #
|
|
|
|
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;
|
|
WITH cte AS ( SELECT i FROM t GROUP BY i) SELECT * FROM cte;
|
|
|
|
SELECT * FROM cte;
|
|
|
|
DROP TABLE cte;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # MDEV-13107: SHOW TABLE STATUS, SHOW CREATE VIEW
|
|
--echo # for CTEs that use derived tables
|
|
--echo #
|
|
|
|
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;
|
|
|
|
--disable_result_log
|
|
show table status;
|
|
--enable_result_log
|
|
|
|
show create view v1;
|
|
show create view v2;
|
|
|
|
select * from v1;
|
|
select * from v2;
|
|
|
|
prepare stmt1 from "select * from v1";
|
|
execute stmt1;
|
|
execute stmt1;
|
|
prepare stmt2 from "select * from v2";
|
|
execute stmt2;
|
|
execute stmt2;
|
|
|
|
deallocate prepare stmt1;
|
|
deallocate prepare stmt2;
|
|
|
|
drop view v1,v2;
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-13796: UNION of two materialized CTEs
|
|
--echo #
|
|
|
|
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);
|
|
|
|
let $q=
|
|
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;
|
|
|
|
eval $q;
|
|
eval explain $q;
|
|
|
|
DROP TABLE t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-13780: tower of embedding CTEs with multiple usage of them
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (3), (2), (4), (7), (1), (2), (5);
|
|
|
|
let $q=
|
|
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;
|
|
|
|
eval $q;
|
|
eval explain extended $q;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-13753: embedded CTE in a VIEW created in prepared statement
|
|
--echo #
|
|
|
|
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;
|
|
SELECT * FROM cte_test;
|
|
|
|
DROP VIEW cte_test;
|
|
|
|
--echo #
|
|
--echo # mdev-14755 : PS for query using CTE in select with subquery
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values
|
|
(7), (2), (8), (1), (3), (2), (7), (5), (4), (7), (9), (8);
|
|
|
|
let $q1=
|
|
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);
|
|
|
|
--sorted_result
|
|
eval $q1;
|
|
eval prepare stmt from "$q1";
|
|
--sorted_result
|
|
execute stmt;
|
|
--sorted_result
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
|
|
let $q2=
|
|
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 ));
|
|
|
|
--sorted_result
|
|
eval $q2;
|
|
eval prepare stmt from "$q2";
|
|
--sorted_result
|
|
execute stmt;
|
|
--sorted_result
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
|
|
let $q3=
|
|
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 ));
|
|
|
|
--sorted_result
|
|
eval $q3;
|
|
eval prepare stmt from "$q3";
|
|
--sorted_result
|
|
execute stmt;
|
|
--sorted_result
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
|
|
let $q4=
|
|
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 ));
|
|
|
|
--sorted_result
|
|
eval $q4;
|
|
eval prepare stmt from "$q4";
|
|
--sorted_result
|
|
execute stmt;
|
|
--sorted_result
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-14852: CTE using temporary table in query
|
|
--echo # with two references to the CTE
|
|
--echo #
|
|
|
|
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;
|
|
|
|
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);
|
|
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-14969: view using subquery with attached CTE
|
|
--echo #
|
|
|
|
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");
|
|
|
|
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;
|
|
select * from v;
|
|
|
|
drop view v;
|
|
drop table region, nation;
|
|
|
|
--echo #
|
|
--echo # MDEV-15120: cte name used with database name
|
|
--echo #
|
|
|
|
--error ER_NO_SUCH_TABLE
|
|
WITH cte AS (SELECT 1 AS a) SELECT test.cte.a FROM test.cte;
|
|
|
|
CREATE DATABASE db1;
|
|
USE db1;
|
|
|
|
--error ER_NO_SUCH_TABLE
|
|
WITH cte AS (SELECT 1 AS a) SELECT db1.cte.a FROM db1.cte;
|
|
|
|
DROP DATABASE db1;
|
|
USE test;
|
|
|
|
--echo #
|
|
--echo # MDEV-15119: CTE c2 specified after CTE c1 and is used in
|
|
--echo # CTE c3 that is embedded into the spec of c1
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (i int);
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
|
|
--error ER_NO_SUCH_TABLE
|
|
WITH c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3),
|
|
c2 AS (SELECT * FROM t1)
|
|
SELECT * FROM c1;
|
|
|
|
WITH RECURSIVE c1 AS (WITH c3 AS (SELECT * FROM c2) SELECT * FROM c3),
|
|
c2 AS (SELECT * FROM t1)
|
|
SELECT * FROM c1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-14297: Lost name of a explicitly named CTE column used in
|
|
--echo # the non-recursive CTE via prepared statement
|
|
--echo #
|
|
|
|
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;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
PREPARE stmt FROM "CREATE VIEW v1 AS WITH cte(a) AS (SELECT 1) SELECT * FROM cte";
|
|
EXECUTE stmt;
|
|
SELECT * FROM v1;
|
|
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;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
DROP TABLE t1;
|
|
DROP VIEW v1,v2;
|
|
|
|
--echo #
|
|
--echo # MDEV-15478: Lost name of a explicitly named CTE column used in
|
|
--echo # the non-recursive CTE defined with UNION
|
|
--echo #
|
|
|
|
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;
|
|
|
|
WITH cte(a) AS (SELECT 1 UNION SELECT 2) SELECT a FROM cte;
|
|
|
|
WITH cte(a) AS (SELECT 1 UNION ALL SELECT 1) SELECT a FROM cte;
|
|
|
|
WITH cte(a) AS (SELECT x from t1 UNION SELECT 4) SELECT a FROM cte;
|
|
|
|
WITH cte(a) AS (SELECT 4 UNION SELECT x FROM t1 UNION SELECT 5)
|
|
SELECT a FROM cte;
|
|
|
|
WITH cte(a,b) AS (SELECT 4,5 UNION SELECT 4,3) SELECT a,b FROM cte;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-16353: unreferenced CTE specified by query with UNION
|
|
--echo #
|
|
|
|
with cte as
|
|
(select 1 union select 2 union select 3)
|
|
select 1 as f;
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (2), (1), (7), (1), (4);
|
|
|
|
with cte as
|
|
(select * from t1 where a < 2 union select * from t1 where a > 5)
|
|
select 2 as f;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-16473: query with CTE when no database is set
|
|
--echo #
|
|
|
|
create database db_mdev_16473;
|
|
use db_mdev_16473;
|
|
drop database db_mdev_16473;
|
|
|
|
--disable_service_connection
|
|
--echo # Now no default database is set
|
|
select database();
|
|
--enable_service_connection
|
|
|
|
with cte as (select 1 as a) select * from cte;
|
|
|
|
create database db_mdev_16473;
|
|
create table db_mdev_16473.t1 (a int);
|
|
insert into db_mdev_16473.t1 values (2), (7), (3), (1);
|
|
with cte as (select * from db_mdev_16473.t1) select * from cte;
|
|
|
|
--error ER_NO_DB_ERROR
|
|
with cte as (select * from db_mdev_16473.t1)
|
|
select * from cte, t1 as t where cte.a=t.a;
|
|
with cte as (select * from db_mdev_16473.t1)
|
|
select * from cte, db_mdev_16473.t1 as t where cte.a=t.a;
|
|
|
|
drop database db_mdev_16473;
|
|
|
|
use test;
|
|
|
|
--echo #
|
|
--echo # MDEV-17154: using parameter markers for PS within CTEs more than once
|
|
--echo # using local variables in SP within CTEs more than once
|
|
--echo #
|
|
|
|
prepare stmt from "
|
|
with cte(c) as (select ? ) select r.c, s.c+10 from cte as r, cte as s;
|
|
";
|
|
set @a=2;
|
|
execute stmt using @a;
|
|
set @a=5;
|
|
execute stmt using @a;
|
|
deallocate prepare stmt;
|
|
|
|
prepare stmt from "
|
|
with cte(c) as (select ? ) select c from cte union select c+10 from cte;
|
|
";
|
|
set @a=2;
|
|
execute stmt using @a;
|
|
set @a=5;
|
|
execute stmt using @a;
|
|
deallocate prepare stmt;
|
|
|
|
prepare stmt from "
|
|
with cte_e(a,b) as
|
|
(
|
|
with cte_o(c) as (select ?)
|
|
select r.c+10, s.c+20 from cte_o as r, cte_o as s
|
|
)
|
|
select * from cte_e as cte_e1 where a > 12
|
|
union all
|
|
select * from cte_e as cte_e2;
|
|
";
|
|
set @a=2;
|
|
execute stmt using @a;
|
|
set @a=5;
|
|
execute stmt using @a;
|
|
deallocate prepare stmt;
|
|
|
|
create table t1 (a int, b int);
|
|
insert into t1 values
|
|
(3,33), (1,17), (7,72), (4,45), (2,27), (3,35), (4,47), (3,38), (2,22);
|
|
|
|
prepare stmt from "
|
|
with cte as (select * from t1 where a < ? and b > ?)
|
|
select r.a, r.b+10, s.a, s.b+20 from cte as r, cte as s where r.a=s.a+1;
|
|
";
|
|
set @a=4, @b=20;
|
|
execute stmt using @a,@b;
|
|
set @a=5, @b=20;
|
|
execute stmt using @a,@b;
|
|
deallocate prepare stmt;
|
|
|
|
delimiter |;
|
|
|
|
create procedure p1()
|
|
begin
|
|
declare i int;
|
|
set i = 0;
|
|
while i < 4 do
|
|
insert into t1
|
|
with cte(a) as (select i) select r.a-1, s.a+1 from cte as r, cte as s;
|
|
set i = i+1;
|
|
end while;
|
|
end|
|
|
|
|
create procedure p2(in i int)
|
|
begin
|
|
insert into t1
|
|
with cte(a) as (select i) select r.a-1, s.a+1 from cte as r, cte as s;
|
|
end|
|
|
|
|
delimiter ;|
|
|
|
|
delete from t1;
|
|
call p1();
|
|
select * from t1;
|
|
call p1();
|
|
select * from t1;
|
|
|
|
delete from t1;
|
|
call p2(3);
|
|
select * from t1;
|
|
call p2(7);
|
|
select * from t1;
|
|
|
|
drop procedure p1;
|
|
drop procedure p2;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-17107: PS for CREATE OR REPLACE VIEW defined by SELECT with CTEs
|
|
--echo #
|
|
|
|
create table t1(a int);
|
|
insert into t1 values (3), (1), (2);
|
|
create table t2 (b int);
|
|
insert into t2 values (2), (10);
|
|
|
|
prepare stmt from
|
|
"create or replace 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;";
|
|
|
|
execute stmt;
|
|
select * from v1;
|
|
|
|
drop view v1;
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-19112: CTE usage when information_schema is set as default db
|
|
--echo #
|
|
|
|
with t as (select 1 as t ) select * from t;
|
|
|
|
use information_schema;
|
|
with t as (select 1 as t) select * from t;
|
|
with columns as (select 1 as t) select * from columns;
|
|
|
|
use test;
|
|
|
|
--echo #
|
|
--echo # MDEV-18460: Server crashed in strmake / tdc_create_key /
|
|
--echo # THD::create_tmp_table_def_key
|
|
--echo #
|
|
|
|
--connect con1,localhost,root,,
|
|
--change_user root,,
|
|
|
|
CREATE TEMPORARY TABLE test.t (a INT);
|
|
WITH cte AS (SELECT 1) SELECT * FROM cte;
|
|
WITH t AS (SELECT 1) SELECT * FROM t;
|
|
--error ER_NO_DB_ERROR
|
|
WITH cte AS (SELECT 1) SELECT * FROM t;
|
|
DROP TABLE test.t;
|
|
|
|
--connection default
|
|
--disconnect con1
|
|
|
|
--echo #
|
|
--echo # MDEV-22781: create view with CTE without default database
|
|
--echo #
|
|
|
|
create database db;
|
|
use db;
|
|
drop database db;
|
|
create database db1;
|
|
create table db1.t1 (a int);
|
|
insert into db1.t1 values (3),(7),(1);
|
|
|
|
create view db1.v1 as with t as (select * from db1.t1) select * from t;
|
|
show create view db1.v1;
|
|
--disable_service_connection
|
|
select * from db1.v1;
|
|
--enable_service_connection
|
|
drop view db1.v1;
|
|
|
|
prepare stmt from "
|
|
create view db1.v1 as with t as (select * from db1.t1) select * from t;
|
|
";
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
show create view db1.v1;
|
|
--disable_service_connection
|
|
select * from db1.v1;
|
|
--enable_service_connection
|
|
drop view db1.v1;
|
|
|
|
drop table db1.t1;
|
|
drop database db1;
|
|
|
|
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 #
|
|
--echo # MDEV-23886: Stored Function returning the result of a query
|
|
--echo # that uses CTE over a table twice
|
|
--echo #
|
|
|
|
create table t1 (c1 int);
|
|
insert into t1 values (1),(2),(6);
|
|
|
|
create function f1() returns int return
|
|
( with cte1 as (select c1 from t1)
|
|
select sum(c1) from
|
|
(select * from cte1 union all select * from cte1) dt
|
|
);
|
|
select f1();
|
|
|
|
create function f2() returns int return
|
|
( with cte1 as (select c1 from t1)
|
|
select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1
|
|
);
|
|
select f2();
|
|
|
|
create function f3() returns int return
|
|
( with cte1 as (select c1 from t1)
|
|
select
|
|
case
|
|
when exists(select 1 from cte1 where c1 between 1 and 2) then 1
|
|
when exists(select 1 from cte1 where c1 between 5 and 6) then 2
|
|
else 0
|
|
end
|
|
);
|
|
select f3();
|
|
|
|
create view v1 as (select c1 from t1);
|
|
|
|
create function f4() returns int return
|
|
( select sum(c1) from
|
|
(select * from v1 union all select * from v1) dt
|
|
);
|
|
select f4();
|
|
|
|
create function f5() returns int return
|
|
( select sum(s.c1) from v1 as s, v1 as t where s.c1=t.c1
|
|
);
|
|
select f5();
|
|
|
|
create view v2(s) as
|
|
with cte1 as (select c1 from t1)
|
|
select sum(c1) from (select * from cte1 union all select * from cte1) dt;
|
|
|
|
create function f6() returns int return
|
|
(select s from v2);
|
|
select f6();
|
|
|
|
create function f7() returns int return
|
|
( select r.s from v2 as r, v2 as t where r.s=t.s
|
|
);
|
|
select f7();
|
|
|
|
select f5() + f6();
|
|
|
|
prepare stmt from "select f5() + f6();";
|
|
execute stmt;
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
|
|
drop function f1;
|
|
drop function f2;
|
|
drop function f3;
|
|
drop function f4;
|
|
drop function f5;
|
|
drop function f6;
|
|
drop function f7;
|
|
|
|
drop view v1;
|
|
drop view v2;
|
|
|
|
create table t2 (a int, b int);
|
|
|
|
insert into t2
|
|
with cte1 as (select c1 from t1)
|
|
select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5;
|
|
|
|
select * from t2;
|
|
|
|
delimiter |;
|
|
|
|
create procedure p1()
|
|
begin
|
|
insert into t2
|
|
with cte1 as (select c1 from t1)
|
|
select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
|
|
end |
|
|
|
|
delimiter ;|
|
|
|
|
call p1();
|
|
select * from t2;
|
|
|
|
drop procedure p1;
|
|
|
|
--echo # checking CTE resolution for queries with hanging CTEs
|
|
|
|
with
|
|
cte1(a) as (select * from t1 where c1 <= 2),
|
|
cte2(b) as (select * from cte1 where a >= 2),
|
|
cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
|
|
select * from cte3;
|
|
|
|
select * from t2;
|
|
|
|
with
|
|
cte1(a) as (select * from t1 where c1 <= 2),
|
|
cte2(b) as (select * from cte1 where a >= 2),
|
|
cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
|
|
select * from t2;
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
with
|
|
cte1(a) as (select * from t1 where c1 <= 2),
|
|
cte2(b) as (select * from cte1 where c1 >= 2),
|
|
cte3 as (select * from cte1,cte2 where cte1.a < cte2.b)
|
|
select * from t2;
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
with
|
|
cte1(a) as (select * from t1 where c1 <= 2),
|
|
cte2(b) as (select * from cte1 where a >= 2),
|
|
cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1)
|
|
select * from t2;
|
|
|
|
with
|
|
cte1 as (select * from t1 where c1 <= 2),
|
|
cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
|
|
select * from cte2;
|
|
|
|
with
|
|
cte1 as (select * from t1 where c1 <= 2),
|
|
cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
|
|
select * from t2;
|
|
|
|
--error ER_NON_UNIQ_ERROR
|
|
with
|
|
cte1 as (select * from t1 where c1 <= 2),
|
|
cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1)
|
|
select * from t2;
|
|
|
|
with cte3 as
|
|
( with cte2(a,b) as
|
|
( with cte1 as (select * from t1 where c1 <= 2)
|
|
select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
|
|
select r1.a,r2.b from cte2 as r1, cte2 as r2)
|
|
select * from cte3;
|
|
|
|
with cte3 as
|
|
( with cte2(a,b) as
|
|
( with cte1 as (select * from t1 where c1 <= 2)
|
|
select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
|
|
select r1.a,r2.b from cte2 as r1, cte2 as r2)
|
|
select * from t2;
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
with cte3 as
|
|
( with cte2(a,b) as
|
|
( with cte1 as (select * from t1 where c1 <= 2)
|
|
select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1)
|
|
select r1.c1,r2.c1 from cte2 as r1, cte2 as r2)
|
|
select * from t2;
|
|
|
|
delimiter |;
|
|
|
|
create procedure p1()
|
|
begin
|
|
insert into t2
|
|
with cte1 as (select c1 from t1)
|
|
select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
|
|
end |
|
|
|
|
delimiter ;|
|
|
|
|
call p1();
|
|
select * from t2;
|
|
|
|
drop procedure p1;
|
|
|
|
delimiter |;
|
|
|
|
create procedure p1()
|
|
begin
|
|
insert into t2
|
|
with cte1 as (select a from t1)
|
|
select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2;
|
|
end |
|
|
|
|
delimiter ;|
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
call p1();
|
|
|
|
drop procedure p1;
|
|
|
|
drop table t1,t2;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-20411: SP containing only one SELECT with WITH clause
|
|
--echo #
|
|
|
|
create procedure sp1 ()
|
|
with cte as (select 1 as a) select * from cte;
|
|
call sp1();
|
|
call sp1();
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5);
|
|
|
|
create procedure sp2 ()
|
|
with cte as (select * from t1) select * from cte;
|
|
call sp2();
|
|
call sp2();
|
|
|
|
create procedure sp3 ()
|
|
with cte as (select * from t1 group by a) select * from cte;
|
|
call sp3();
|
|
call sp3();
|
|
|
|
drop procedure sp1;
|
|
drop procedure sp2;
|
|
drop procedure sp3;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-26095: missing RECURSIVE for the recursive definition of CTE
|
|
--echo # embedded into another CTE definition
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (5), (7);
|
|
|
|
with cte_e as (
|
|
with recursive cte_r as (
|
|
select a from t1 union select a+1 as a from cte_r r where a < 10
|
|
) select * from cte_r
|
|
) select * from cte_e;
|
|
|
|
--ERROR ER_NO_SUCH_TABLE
|
|
with cte_e as (
|
|
with cte_r as (
|
|
select a from t1 union select a+1 as a from cte_r r where a < 10
|
|
) select * from cte_r
|
|
) select * from cte_e;
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-26025: query with two usage of a CTE executing via PS /SP
|
|
--echo #
|
|
|
|
create table t1 (a int, b int);
|
|
insert into t1 value (1,3), (3,2), (1,3), (4,1);
|
|
|
|
let $q=
|
|
with
|
|
cte1 as ( select a,b from t1 where a = 1 AND b = 3 ),
|
|
cte2 as ( select a,b from cte1 ),
|
|
cte3 as ( select a,b from cte2 )
|
|
select * from cte3, cte2;
|
|
|
|
eval prepare stmt from "$q";
|
|
execute stmt;
|
|
execute stmt;
|
|
deallocate prepare stmt;
|
|
|
|
eval create procedure sp() $q;
|
|
|
|
call sp();
|
|
call sp();
|
|
|
|
drop procedure sp;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-26825: query with two usage of CTE that refers to another CTE
|
|
--echo # with stored function using a base table.
|
|
--echo #
|
|
|
|
create table t1 (id int primary key);
|
|
insert into t1 values
|
|
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
|
|
|
|
create function f(in_id int) returns integer
|
|
return (select id from t1 where t1.id = in_id);
|
|
|
|
with c1 as (select id from t1 where f(id)=id group by id),
|
|
c2 as (select id from c1 as pt group by id)
|
|
select id from c2 as s1 union select id from c2 as s2;
|
|
|
|
with c1 as (select id from t1 as r where f(id)=id group by id),
|
|
c2 as (select id from c1 as pt group by id)
|
|
select id from c2 as s1 union select id from c2 as s2;
|
|
|
|
create function g() returns int return (select count(*) from t1);
|
|
create procedure sp1()
|
|
|
|
with c1 as (select id from t1 a where g() > 10),
|
|
c2 as (select id from c1)
|
|
select id from c2 as s1 union select id from c2 as s2;
|
|
|
|
call sp1();
|
|
call sp1();
|
|
|
|
drop procedure sp1;
|
|
drop function g;
|
|
|
|
drop function f;
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-27086: union using CTEs in CREATE TABLE
|
|
--echo #
|
|
|
|
create or replace temporary table tmp as
|
|
with cte1 as (select 1 as a), cte2 as (select 2 as a)
|
|
select * from cte1 union select * from cte2;
|
|
select * from tmp;
|
|
|
|
create table t1 as
|
|
with cte1 as (select 1 as a), cte2 as (select 2 as a)
|
|
select * from cte1 union select * from cte2;
|
|
select * from t1;
|
|
|
|
insert into t1 values (3);
|
|
|
|
create table t2 as
|
|
with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2)
|
|
select * from cte1 union select * from cte2;
|
|
select * from t2;
|
|
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-26470: CTE in WITH clause of subquery used in DELETE
|
|
--echo #
|
|
|
|
create table t1 (a int);
|
|
insert into t1 values (3), (7), (1), (5);
|
|
|
|
create table t2 (b int);
|
|
insert into t2 values (4), (1), (3), (2);
|
|
|
|
delete from t1
|
|
where a in (with cte(a) as (select * from t2 where b <=2) select a from cte);
|
|
select * from t1;
|
|
|
|
insert into t1 values (1), (3);
|
|
|
|
delete t1 from t1, t2
|
|
where t1.a=t2.b or
|
|
t1.a in (with cte(a) as (select b+1 from t2) select * from cte);
|
|
select * from t1;
|
|
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-25766: Unused CTE lead to a crash in
|
|
--echo # find_field_in_tables/find_order_in_list
|
|
--echo #
|
|
|
|
create table t1 (f1 INTEGER);
|
|
|
|
create view v1 as
|
|
select
|
|
subq_0.c4 as c2,
|
|
subq_0.c4 as c4
|
|
from
|
|
(select
|
|
ref_0.f1 as c4
|
|
from
|
|
t1 as ref_0
|
|
where (select 1)
|
|
) as subq_0
|
|
order by c2, c4 desc;
|
|
|
|
WITH
|
|
unused_with AS (select
|
|
subq_0.c4 as c6
|
|
from
|
|
(select
|
|
11 as c4
|
|
from
|
|
v1 as ref_0
|
|
) as subq_0,
|
|
v1 as ref_2
|
|
)
|
|
select 1 ;
|
|
|
|
drop view v1;
|
|
drop table t1;
|
|
|
|
--echo # End of 10.2 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-21673: several references to CTE that uses
|
|
--echo # local variables / parameters of SP
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (col1 int);
|
|
CREATE TABLE t2 (col1 int, col2 date, col3 varchar(16), col4 int);
|
|
CREATE TABLE t3 (col1 int, col2 date);
|
|
CREATE TABLE t4 (col1 int, col2 date);
|
|
INSERT INTO t1 VALUES (3), (7), (9), (1);
|
|
INSERT INTO t2 VALUES
|
|
(3,'2019-09-01','AAA',2), (7,'2019-10-01','AAA',4), (3,'2019-10-01','AAA',8),
|
|
(1,'2019-10-01','BBB',9), (1,'2019-10-01','AAA',4), (1,'2019-10-01','AAA',6);
|
|
INSERT INTO t3 VALUES
|
|
(4,'2018-10-01'), (6,'2018-10-01'), (4,'2017-10-01'), (7,'2017-10-01');
|
|
INSERT INTO t4 VALUES
|
|
(5,'2018-10-01'), (8,'2017-10-01'), (4,'2017-10-01');
|
|
|
|
DELIMITER |;
|
|
|
|
CREATE OR REPLACE PROCEDURE SP1()
|
|
BEGIN
|
|
DECLARE p_date date;
|
|
DECLARE p_var2 varchar(16);
|
|
SET p_date='2019-10-01';
|
|
SET p_var2='AAA';
|
|
WITH cte_first(col) AS
|
|
(
|
|
SELECT DISTINCT col4
|
|
FROM t1, t2
|
|
WHERE t2.col1 = t1.col1 AND t2.col2 = p_date AND t2.col3 = p_var2
|
|
),
|
|
cte2 AS
|
|
(
|
|
SELECT DISTINCT col2
|
|
FROM t3
|
|
WHERE col1 IN ( SELECT col FROM cte_first )
|
|
),
|
|
cte3 AS (
|
|
SELECT distinct t4.col1
|
|
FROM cte2, t4
|
|
WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first )
|
|
)
|
|
SELECT * FROM cte3;
|
|
END|
|
|
|
|
CREATE PROCEDURE SP2(IN d date)
|
|
BEGIN
|
|
DECLARE p_var2 varchar(16);
|
|
SET p_var2='AAA';
|
|
WITH cte_first(col) AS
|
|
(
|
|
SELECT DISTINCT col4
|
|
FROM t1, t2
|
|
WHERE t2.col1 = t1.col1 AND t2.col2 = d AND t2.col3 = p_var2
|
|
),
|
|
cte2 AS
|
|
(
|
|
SELECT DISTINCT col2
|
|
FROM t3
|
|
WHERE col1 IN ( SELECT col FROM cte_first )
|
|
),
|
|
cte3 AS (
|
|
SELECT distinct t4.col1
|
|
FROM cte2, t4
|
|
WHERE t4.col2 = cte2.col2 AND t4.col1 IN ( SELECT col FROM cte_first )
|
|
)
|
|
SELECT * FROM cte3;
|
|
END|
|
|
|
|
DELIMITER ;|
|
|
|
|
|
|
CREATE TABLE t AS
|
|
SELECT col4 AS col
|
|
FROM t1, t2
|
|
WHERE t2.col1 = t1.col1 AND t2.col2 ='2019-10-01' AND t2.col3 = 'AAA';
|
|
SELECT * FROM t;
|
|
|
|
CREATE TABLE tt AS
|
|
SELECT col2
|
|
FROM t3
|
|
WHERE col1 IN ( SELECT col FROM t );
|
|
SELECT * FROM tt;
|
|
|
|
SELECT t4.col1
|
|
FROM tt, t4
|
|
WHERE t4.col2 = tt.col2 AND t4.col1 IN ( SELECT col FROM t );
|
|
|
|
DROP TABLE t,tt;
|
|
|
|
CALL SP1();
|
|
CALL SP1();
|
|
|
|
CALL SP2('2019-10-01');
|
|
CALL SP2('2019-10-01');
|
|
|
|
DROP PROCEDURE SP1;
|
|
DROP PROCEDURE SP2;
|
|
DROP TABLE t1,t2,t3,t4;
|
|
|
|
--echo # End of 10.3 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-20730: Syntax error on SELECT INTO @variable with CTE
|
|
--echo #
|
|
|
|
with data as (select 1 as id)
|
|
select id into @myid from data;
|
|
set @save_sql_mode = @@sql_mode;
|
|
set sql_mode="oracle";
|
|
with data as (select 1 as id)
|
|
select id into @myid from data;
|
|
set sql_mode= @save_sql_mode;
|
|
|
|
--echo #
|
|
--echo # MDEV-31995 Bogus error executing PS for query using CTE with renaming of columns
|
|
--echo #
|
|
|
|
create table t1 (a int, b int);
|
|
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
|
|
create table t2 (a int, b int);
|
|
insert into t2 values (3,1),(3,2),(3,3),(4,1),(4,2);
|
|
|
|
let $q=
|
|
with cte (c1,c2) as
|
|
(select a as col1, sum(b) as col2 from t1 group by col1)
|
|
select * from cte;
|
|
|
|
eval $q;
|
|
|
|
eval prepare st from "$q";
|
|
execute st;
|
|
execute st;
|
|
drop prepare st;
|
|
|
|
eval create procedure sp() $q;
|
|
call sp();
|
|
call sp();
|
|
drop procedure sp;
|
|
|
|
let $q=
|
|
with cte (c1,c2) as
|
|
(select a as col1, sum(b) as col2 from t1 order by col1)
|
|
select * from cte;
|
|
|
|
eval $q;
|
|
|
|
eval prepare st from "$q";
|
|
execute st;
|
|
execute st;
|
|
drop prepare st;
|
|
|
|
eval create procedure sp() $q;
|
|
call sp();
|
|
call sp();
|
|
drop procedure sp;
|
|
|
|
let $q=
|
|
with cte (c1,c2) as
|
|
(select a as col1, sum(b) as col2 from t1 where a > 1 group by col1
|
|
union select a as col3, sum(b) as col4 from t2 where b > 2 group by col3),
|
|
cte2 (c3, c4) as
|
|
(select a as col5, sum(b) as col6 from t1 where a <= 1 group by col5
|
|
union select a as col7, sum(b) as col8 from t2 where b <= 2 group by col7)
|
|
select * from cte where c1=1 union select * from cte2 where c3=3;
|
|
|
|
eval $q;
|
|
|
|
eval prepare st from "$q";
|
|
execute st;
|
|
execute st;
|
|
drop prepare st;
|
|
|
|
eval create procedure sp() $q;
|
|
call sp();
|
|
call sp();
|
|
drop procedure sp;
|
|
|
|
let $q=
|
|
with cte (c1,c2) as (select * from t1)
|
|
select cte.c1+1 as col1 , cte.c2 as col2 from cte where cte.c1 > 1
|
|
union
|
|
select cte.c1 as col3, cte.c2+1 as col4 from cte where cte.c1 < 0;
|
|
|
|
eval $q;
|
|
|
|
eval prepare st from "$q";
|
|
execute st;
|
|
execute st;
|
|
--echo save this to the end to test errors >drop prepare st;
|
|
|
|
eval create procedure sp() $q;
|
|
call sp();
|
|
call sp();
|
|
drop procedure sp;
|
|
|
|
insert into t1 select * from t2;
|
|
|
|
let $q=
|
|
with cte (c1, c2)
|
|
as (select a, sum(b) from t1 where b > 1 group by a having sum(b) < 5)
|
|
select * from cte where c1 < 4 and c2 > 1;
|
|
|
|
eval $q;
|
|
|
|
--echo # Check pushdown conditions in JSON output
|
|
--source include/analyze-format.inc
|
|
eval explain format=json $q;
|
|
|
|
alter table t1 add column c int;
|
|
|
|
--error ER_WITH_COL_WRONG_LIST
|
|
execute st;
|
|
|
|
drop prepare st;
|
|
drop table t1,t2;
|
|
|
|
--echo Test out recursive CTEs
|
|
|
|
create table distances (src char(1), dest char(1), distance int);
|
|
create table city_population (city char(1), population int);
|
|
INSERT INTO `distances` VALUES ('A','A',0),('B','A',593),('C','A',800),
|
|
('D','A',221),('E','A',707),('F','A',869),('G','A',225),('H','A',519),
|
|
('A','B',919),('B','B',0),('C','B',440),('D','B',79),('E','B',79),
|
|
('F','B',154),('G','B',537),('H','B',220),('A','C',491),('B','C',794),
|
|
('C','C',0),('D','C',100),('E','C',350),('F','C',748),('G','C',712),
|
|
('H','C',315),('A','D',440),('B','D',256),('C','D',958),('D','D',0),
|
|
('E','D',255),('F','D',161),('G','D',63),('H','D',831),('A','E',968),
|
|
('B','E',345),('C','E',823),('D','E',81),('E','E',0),('F','E',436),
|
|
('G','E',373),('H','E',558),('A','F',670),('B','F',677),('C','F',375),
|
|
('D','F',843),('E','F',90),('F','F',0),('G','F',328),('H','F',881),
|
|
('A','G',422),('B','G',467),('C','G',67),('D','G',936),('E','G',480),
|
|
('F','G',592),('G','G',0),('H','G',819),('A','H',537),('B','H',229),
|
|
('C','H',534),('D','H',984),('E','H',319),('F','H',643),('G','H',257),
|
|
('H','H',0);
|
|
insert into city_population values ('A', 5000), ('B', 6000), ('C', 100000),
|
|
('D', 80000), ('E', 7000), ('F', 1000), ('G', 100), ('H', -80000);
|
|
|
|
--echo #find the biggest city within 300 kellikams of 'E'
|
|
let $q=
|
|
with recursive travel (src, path, dest, distance, population) as (
|
|
select city, cast('' as varchar(10)), city,
|
|
0, population
|
|
from city_population where city='E'
|
|
union all
|
|
select src.src, concat(src.path, dst.dest), dst.dest,
|
|
src.distance + dst.distance, dstc.population
|
|
from travel src
|
|
join distances dst on src.dest != dst.dest
|
|
join city_population dstc on dst.dest = dstc.city
|
|
where dst.src = src.dest and src.distance + dst.distance < 300
|
|
and length(path) < 10
|
|
)
|
|
select * from travel where dest != 'E' order by population desc, distance
|
|
limit 1;
|
|
|
|
eval $q;
|
|
|
|
eval prepare st from "$q";
|
|
execute st;
|
|
execute st;
|
|
drop prepare st;
|
|
|
|
eval create procedure sp() $q;
|
|
call sp();
|
|
call sp();
|
|
drop procedure sp;
|
|
|
|
drop table distances, city_population;
|
|
|
|
--echo #
|
|
--echo # MDEV-28615: Multi-table UPDATE over derived table containing
|
|
--echo # row that uses subquery with hanging CTE
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a int) ENGINE=MYISAM;
|
|
INSERT INTO t1 VALUES (3), (7), (1);
|
|
|
|
--error ER_OPERAND_COLUMNS
|
|
UPDATE
|
|
(SELECT (5, (WITH cte AS (SELECT 1) SELECT a FROM t1))) dt
|
|
JOIN t1 t
|
|
ON t.a=dt.a
|
|
SET t.a = 1;
|
|
|
|
UPDATE
|
|
(SELECT a FROM t1
|
|
WHERE (5, (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)) <=
|
|
(5,a)) dt
|
|
JOIN t1 t
|
|
ON t.a=dt.a
|
|
SET t.a = 1;
|
|
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-31657: CTE with the same name as base table used twice
|
|
--echo # in another CTE
|
|
--echo #
|
|
|
|
create table t (a int);
|
|
insert into t values (3), (7), (1);
|
|
|
|
let $q1=
|
|
with
|
|
t as (select * from t),
|
|
cte as (select t1.a as t1a, t2.a as t2a from t as t1, t as t2 where t1.a=t2.a)
|
|
select * from cte;
|
|
|
|
eval $q1;
|
|
|
|
create table s (a int);
|
|
insert into s values (1), (4), (7);
|
|
|
|
let $q2=
|
|
with
|
|
t as (select * from t),
|
|
s as (select a-1 as a from s),
|
|
cte as (select t.a as ta, s.a as sa from t, s where t.a=s.a
|
|
union
|
|
select t.a+1, s.a+1 from t, s where t.a=s.a+1)
|
|
select * from cte;
|
|
|
|
eval $q2;
|
|
|
|
let $q3=
|
|
with
|
|
t as (select * from t),
|
|
cte as (select t.a as ta, s.a as sa from t, s where t.a=s.a
|
|
union
|
|
select t.a+1, s.a+1 from t, s where t.a=s.a),
|
|
s as (select a+10 as a from s)
|
|
select * from cte;
|
|
|
|
eval $q3;
|
|
|
|
drop table t,s;
|
|
|
|
--ERROR ER_NO_SUCH_TABLE
|
|
eval $q1;
|
|
|
|
--echo # End of 10.4 tests
|