summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--libmysqld/CMakeLists.txt1
-rw-r--r--mysql-test/r/cte_grant.result54
-rw-r--r--mysql-test/r/cte_nonrecursive.result748
-rw-r--r--mysql-test/t/cte_grant.test79
-rw-r--r--mysql-test/t/cte_nonrecursive.test436
-rw-r--r--sql/CMakeLists.txt1
-rw-r--r--sql/lex.h1
-rw-r--r--sql/share/errmsg-utf8.txt8
-rw-r--r--sql/sql_base.cc23
-rw-r--r--sql/sql_cte.cc601
-rw-r--r--sql/sql_cte.h178
-rw-r--r--sql/sql_derived.cc4
-rw-r--r--sql/sql_lex.cc41
-rw-r--r--sql/sql_lex.h41
-rw-r--r--sql/sql_parse.cc4
-rw-r--r--sql/sql_prepare.cc3
-rw-r--r--sql/sql_select.cc28
-rw-r--r--sql/sql_view.cc2
-rw-r--r--sql/sql_view.h4
-rw-r--r--sql/sql_yacc.yy132
-rw-r--r--sql/table.cc9
-rw-r--r--sql/table.h4
22 files changed, 2371 insertions, 31 deletions
diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt
index d4df327930d..2081e7558d5 100644
--- a/libmysqld/CMakeLists.txt
+++ b/libmysqld/CMakeLists.txt
@@ -109,6 +109,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc
../sql/table_cache.cc ../sql/mf_iocache_encr.cc
../sql/item_inetfunc.cc
../sql/wsrep_dummy.cc ../sql/encryption.cc
+ ../sql/sql_cte.cc
${GEN_SOURCES}
${MYSYS_LIBWRAP_SOURCE}
)
diff --git a/mysql-test/r/cte_grant.result b/mysql-test/r/cte_grant.result
new file mode 100644
index 00000000000..4b067f42ff2
--- /dev/null
+++ b/mysql-test/r/cte_grant.result
@@ -0,0 +1,54 @@
+create database mysqltest;
+create user mysqltest_1@localhost;
+create table mysqltest.t1 (a int, b int);
+insert into mysqltest.t1 values (2,10), (1,30);
+create table mysqltest.t2 (c int, d char(32));
+insert into mysqltest.t2 values (1,'xxx'), (1,'zzz');
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+grant select (c) on mysqltest.t2 to mysqltest_1@localhost;
+with t as (select c from mysqltest.t2 where c < 2)
+select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
+c b
+1 30
+1 30
+select t.c,t.d,t1.b
+from (select c,d from mysqltest.t2 where c < 2) as t, mysqltest.t1
+where t.c=t1.a;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 't2'
+with t as (select c,d from mysqltest.t2 where c < 2)
+select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 't2'
+create view mysqltest.v1(f1,f2) as
+with t as (select c from mysqltest.t2 where c < 2)
+select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
+create view mysqltest.v2(c,d) as
+with t as (select a from mysqltest.t1 where a>=3)
+select t.a,b from t,mysqltest.t1 where mysqltest.t1.a = t.a;
+grant select on mysqltest.v1 to mysqltest_1@localhost;
+grant select (c) on mysqltest.v2 to mysqltest_1@localhost;
+grant create view on mysqltest.* to mysqltest_1@localhost;
+create view mysqltest.v3(c,d) as
+with t as (select c from mysqltest.t2 where c < 2)
+select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
+create view mysqltest.v4(f1,f2,f3) as
+with t as (select c,d from mysqltest.t2 where c < 2)
+select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 't2'
+select * from mysqltest.v1;
+f1 f2
+1 30
+1 30
+select c from mysqltest.v2;
+c
+select d from mysqltest.v2;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'd' in table 'v2'
+select * from mysqltest.v3;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v3'
+grant select on mysqltest.v3 to mysqltest_1@localhost;
+select * from mysqltest.v3;
+c d
+1 30
+1 30
+revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
+drop database mysqltest;
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
new file mode 100644
index 00000000000..df641156e61
--- /dev/null
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -0,0 +1,748 @@
+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 SUBQUERY 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 SUBQUERY 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 SUBQUERY t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary
+2 SUBQUERY 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
+# 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 SUBQUERY 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)
+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)
+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 PRIMARY <derived2> ALL NULL NULL NULL NULL 32
+2 DERIVED t2 ALL NULL NULL NULL NULL 4
+2 DERIVED 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 <derived2> ALL NULL NULL NULL NULL 8
+2 DERIVED t2 ALL NULL NULL NULL NULL 4 Using where
+2 DERIVED <derived3> ref key0 key0 5 test.t2.c 2
+3 SUBQUERY 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 `t1`.`a` AS `c` from `t1` where (`t1`.`b` >= 'c'))select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where ((`r1`.`c` = `r2`.`c`) and (`r2`.`c` = 4)) latin1 latin1_swedish_ci
+select * from v4;
+c d
+4 4
+4 4
+4 4
+4 4
+explain
+select * from v4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 64
+2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where
+2 DERIVED 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 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 HY000: The definition of the table 't' refers to the table 's' defined later in a non-recursive WITH clause
+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: Recursive queries in WITH clause are not supported yet
+with 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: Recursive queries in WITH clause are not supported yet
+with 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: Recursive queries in WITH clause are not supported yet
+with 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 s as (select t1.a from s,t1 where t1.a=s.a and t1.b<'c')
+select * from s);
+ERROR HY000: Recursive queries in WITH clause are not supported yet
+#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'
+drop table t1,t2;
diff --git a/mysql-test/t/cte_grant.test b/mysql-test/t/cte_grant.test
new file mode 100644
index 00000000000..44fd4a0bc6e
--- /dev/null
+++ b/mysql-test/t/cte_grant.test
@@ -0,0 +1,79 @@
+# Can't test with embedded server
+-- source include/not_embedded.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+connect (root,localhost,root,,test);
+connection root;
+
+--disable_warnings
+create database mysqltest;
+--enable_warnings
+
+create user mysqltest_1@localhost;
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+
+connection root;
+
+create table mysqltest.t1 (a int, b int);
+insert into mysqltest.t1 values (2,10), (1,30);
+create table mysqltest.t2 (c int, d char(32));
+insert into mysqltest.t2 values (1,'xxx'), (1,'zzz');
+
+grant select on mysqltest.t1 to mysqltest_1@localhost;
+grant select (c) on mysqltest.t2 to mysqltest_1@localhost;
+
+connection user1;
+with t as (select c from mysqltest.t2 where c < 2)
+select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
+--error ER_COLUMNACCESS_DENIED_ERROR
+select t.c,t.d,t1.b
+from (select c,d from mysqltest.t2 where c < 2) as t, mysqltest.t1
+where t.c=t1.a;
+--error ER_COLUMNACCESS_DENIED_ERROR
+with t as (select c,d from mysqltest.t2 where c < 2)
+select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a;
+
+connection root;
+
+create view mysqltest.v1(f1,f2) as
+with t as (select c from mysqltest.t2 where c < 2)
+select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
+create view mysqltest.v2(c,d) as
+with t as (select a from mysqltest.t1 where a>=3)
+select t.a,b from t,mysqltest.t1 where mysqltest.t1.a = t.a;
+
+grant select on mysqltest.v1 to mysqltest_1@localhost;
+grant select (c) on mysqltest.v2 to mysqltest_1@localhost;
+grant create view on mysqltest.* to mysqltest_1@localhost;
+
+connection user1;
+
+create view mysqltest.v3(c,d) as
+with t as (select c from mysqltest.t2 where c < 2)
+select t.c,t1.b from t,mysqltest.t1 where t.c=t1.a;
+--error ER_COLUMNACCESS_DENIED_ERROR
+create view mysqltest.v4(f1,f2,f3) as
+with t as (select c,d from mysqltest.t2 where c < 2)
+select t.c,t.d,t1.b from t,mysqltest.t1 where t.c=t1.a;
+
+select * from mysqltest.v1;
+
+select c from mysqltest.v2;
+# there are no privileges on column 'd'
+--error ER_COLUMNACCESS_DENIED_ERROR
+select d from mysqltest.v2;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from mysqltest.v3;
+connection root;
+grant select on mysqltest.v3 to mysqltest_1@localhost;
+connection user1;
+select * from mysqltest.v3;
+
+connection root;
+revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
+drop user mysqltest_1@localhost;
+drop database mysqltest; \ No newline at end of file
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
new file mode 100644
index 00000000000..5a6e07e0c0c
--- /dev/null
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -0,0 +1,436 @@
+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 # 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_WRONG_ORDER_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;
+
+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_QUERY_IN_WITH_CLAUSE
+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_QUERY_IN_WITH_CLAUSE
+with 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_QUERY_IN_WITH_CLAUSE
+with 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_QUERY_IN_WITH_CLAUSE
+with 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 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;
+
+drop table t1,t2;
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 9b0017c9124..da1d54ef81d 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -137,6 +137,7 @@ SET (SQL_SOURCE
my_json_writer.cc my_json_writer.h
rpl_gtid.cc rpl_parallel.cc
sql_type.cc sql_type.h
+ sql_cte.cc sql_cte.h
${WSREP_SOURCES}
table_cache.cc encryption.cc
${CMAKE_CURRENT_BINARY_DIR}/sql_builtin.cc
diff --git a/sql/lex.h b/sql/lex.h
index 22ff4e6d360..da5fa2de137 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -470,6 +470,7 @@ static SYMBOL symbols[] = {
{ "REAL", SYM(REAL)},
{ "REBUILD", SYM(REBUILD_SYM)},
{ "RECOVER", SYM(RECOVER_SYM)},
+ { "RECURSIVE", SYM(RECURSIVE_SYM)},
{ "REDO_BUFFER_SIZE", SYM(REDO_BUFFER_SIZE_SYM)},
{ "REDOFILE", SYM(REDOFILE_SYM)},
{ "REDUNDANT", SYM(REDUNDANT_SYM)},
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 6003a0f44b1..d23b79a3301 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7146,3 +7146,11 @@ ER_COMMULTI_BADCONTEXT 0A000
ER_BAD_COMMAND_IN_MULTI
eng "Command '%s' is not allowed for COM_MULTI"
ukr "Команда '%s' не дозволена для COM_MULTI"
+ER_WITH_COL_WRONG_LIST
+ eng "WITH column list and SELECT field list have different column counts"
+ER_DUP_QUERY_NAME
+ eng "Duplicate query name in WITH clause"
+ER_WRONG_ORDER_IN_WITH_CLAUSE
+ eng "The definition of the table '%s' refers to the table '%s' defined later in a non-recursive WITH clause"
+ER_RECURSIVE_QUERY_IN_WITH_CLAUSE
+ eng "Recursive queries in WITH clause are not supported yet"
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 6962e759023..728fd44226e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -49,6 +49,7 @@
#include "transaction.h"
#include "sql_prepare.h"
#include "sql_statistics.h"
+#include "sql_cte.h"
#include <m_ctype.h>
#include <my_dir.h>
#include <hash.h>
@@ -3920,6 +3921,26 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables,
tables->table_name= tables->view_name.str;
tables->table_name_length= tables->view_name.length;
}
+ else if (tables->select_lex)
+ {
+ /*
+ Check whether 'tables' refers to a table defined in a with clause.
+ If so set the reference to the definition in tables->with.
+ */
+ if (!tables->with)
+ tables->with= tables->select_lex->find_table_def_in_with_clauses(tables);
+ /*
+ If 'tables' is defined in a with clause set the pointer to the
+ specification from its definition in tables->derived.
+ */
+ if (tables->with)
+ {
+ if (tables->set_as_with_table(thd, tables->with))
+ DBUG_RETURN(1);
+ else
+ goto end;
+ }
+ }
/*
If this TABLE_LIST object is a placeholder for an information_schema
table, create a temporary table to represent the information_schema
@@ -8354,7 +8375,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
temporary table. Thus in this case we can be sure that 'item' is an
Item_field.
*/
- if (any_privileges)
+ if (any_privileges && !tables->is_with_table() && !tables->is_derived())
{
DBUG_ASSERT((tables->field_translation == NULL && table) ||
tables->is_natural_join);
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
new file mode 100644
index 00000000000..1203a4ce0c8
--- /dev/null
+++ b/sql/sql_cte.cc
@@ -0,0 +1,601 @@
+#include "sql_class.h"
+#include "sql_lex.h"
+#include "sql_cte.h"
+#include "sql_view.h" // for make_valid_column_names
+#include "sql_parse.h"
+
+
+/**
+ @brief
+ Check dependencies between tables defined in a list of with clauses
+
+ @param
+ with_clauses_list Pointer to the first clause in the list
+
+ @details
+ The procedure just calls the method With_clause::check_dependencies
+ for each member of the given list.
+
+ @retval
+ false on success
+ true on failure
+*/
+
+bool check_dependencies_in_with_clauses(With_clause *with_clauses_list)
+{
+ for (With_clause *with_clause= with_clauses_list;
+ with_clause;
+ with_clause= with_clause->next_with_clause)
+ {
+ if (with_clause->check_dependencies())
+ return true;
+ }
+ return false;
+}
+
+
+/**
+ @brief
+ Check dependencies between tables defined in this with clause
+
+ @details
+ The method performs the following actions for this with clause:
+
+ 1. Test for definitions of the tables with the same name.
+ 2. For each table T defined in this with clause look for tables
+ from the same with clause that are used in the query that
+ specifies T and set the dependencies of T on these tables
+ in dependency_map.
+ 3. Build the transitive closure of the above direct dependencies
+ to find out all recursive definitions.
+ 4. If this with clause is not specified as recursive then
+ for each with table T defined in this with clause check whether
+ it is used in any definition that follows the definition of T.
+
+ @retval
+ true if an error is reported
+ false otherwise
+*/
+
+bool With_clause::check_dependencies()
+{
+ if (dependencies_are_checked)
+ return false;
+ /*
+ Look for for definitions with the same query name.
+ When found report an error and return true immediately.
+ For each table T defined in this with clause look for all other tables from
+ the same with with clause that are used in the specification of T.
+ For each such table set the dependency bit in the dependency map of
+ with element for T.
+ */
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ for (With_element *elem= first_elem;
+ elem != with_elem;
+ elem= elem->next_elem)
+ {
+ if (my_strcasecmp(system_charset_info, with_elem->query_name->str,
+ elem->query_name->str) == 0)
+ {
+ my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str);
+ return true;
+ }
+ }
+ with_elem->check_dependencies_in_unit(with_elem->spec);
+ }
+ /* Build the transitive closure of the direct dependencies found above */
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ table_map with_elem_map= with_elem->get_elem_map();
+ for (With_element *elem= first_elem; elem != NULL; elem= elem->next_elem)
+ {
+ if (elem->dependency_map & with_elem_map)
+ elem->dependency_map |= with_elem->dependency_map;
+ }
+ }
+
+ /*
+ Mark those elements where tables are defined with direct or indirect recursion.
+ Report an error when recursion (direct or indirect) is used to define a table.
+ */
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ if (with_elem->dependency_map & with_elem->get_elem_map())
+ with_elem->is_recursive= true;
+ }
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ if (with_elem->is_recursive)
+ {
+ my_error(ER_RECURSIVE_QUERY_IN_WITH_CLAUSE, MYF(0),
+ with_elem->query_name->str);
+ return true;
+ }
+ }
+
+ if (!with_recursive)
+ {
+ /*
+ For each with table T defined in this with clause check whether
+ it is used in any definition that follows the definition of T.
+ */
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ With_element *checked_elem= with_elem->next_elem;
+ for (uint i = with_elem->number+1;
+ i < elements;
+ i++, checked_elem= checked_elem->next_elem)
+ {
+ if (with_elem->check_dependency_on(checked_elem))
+ {
+ my_error(ER_WRONG_ORDER_IN_WITH_CLAUSE, MYF(0),
+ with_elem->query_name->str, checked_elem->query_name->str);
+ return true;
+ }
+ }
+ }
+ }
+
+ dependencies_are_checked= true;
+ return false;
+}
+
+
+/**
+ @brief
+ Check dependencies on the sibling with tables used in the given unit
+
+ @param unit The unit where the siblings are to be searched for
+
+ @details
+ The method recursively looks through all from lists encountered
+ the given unit. If it finds a reference to a table that is
+ defined in the same with clause to which this element belongs
+ the method set the bit of dependency on this table in the
+ dependency_map of this element.
+*/
+
+void With_element::check_dependencies_in_unit(st_select_lex_unit *unit)
+{
+ st_select_lex *sl= unit->first_select();
+ for (; sl; sl= sl->next_select())
+ {
+ for (TABLE_LIST *tbl= sl->table_list.first; tbl; tbl= tbl->next_local)
+ {
+ if (!tbl->with)
+ tbl->with= owner->find_table_def(tbl);
+ if (!tbl->with && tbl->select_lex)
+ tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl);
+ if (tbl->with && tbl->with->owner== this->owner)
+ set_dependency_on(tbl->with);
+ }
+ st_select_lex_unit *inner_unit= sl->first_inner_unit();
+ for (; inner_unit; inner_unit= inner_unit->next_unit())
+ check_dependencies_in_unit(inner_unit);
+ }
+}
+
+
+/**
+ @brief
+ Search for the definition of a table among the elements of this with clause
+
+ @param table The reference to the table that is looked for
+
+ @details
+ The function looks through the elements of this with clause trying to find
+ the definition of the given table. When it encounters the element with
+ the same query name as the table's name it returns this element. If no
+ such definitions are found the function returns NULL.
+
+ @retval
+ found with element if the search succeeded
+ NULL - otherwise
+*/
+
+With_element *With_clause::find_table_def(TABLE_LIST *table)
+{
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ if (my_strcasecmp(system_charset_info, with_elem->query_name->str, table->table_name) == 0)
+ {
+ return with_elem;
+ }
+ }
+ return NULL;
+}
+
+
+/**
+ @brief
+ Perform context analysis for all unreferenced tables defined in with clause
+
+ @param thd The context of the statement containing this with clause
+
+ @details
+ For each unreferenced table T defined in this with clause the method
+ calls the method With_element::prepare_unreferenced that performs
+ context analysis of the element with the definition of T.
+
+ @retval
+ false If context analysis does not report any error
+ true Otherwise
+*/
+
+bool With_clause::prepare_unreferenced_elements(THD *thd)
+{
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ if (!with_elem->is_referenced() && with_elem->prepare_unreferenced(thd))
+ return true;
+ }
+
+ return false;
+}
+
+
+/**
+ @brief
+ Save the specification of the given with table as a string
+
+ @param thd The context of the statement containing this with element
+ @param spec_start The beginning of the specification in the input string
+ @param spec_end The end of the specification in the input string
+
+ @details
+ The method creates for a string copy of the specification used in this element.
+ The method is called when the element is parsed. The copy may be used to
+ create clones of the specification whenever they are needed.
+
+ @retval
+ false on success
+ true on failure
+*/
+
+bool With_element::set_unparsed_spec(THD *thd, char *spec_start, char *spec_end)
+{
+ unparsed_spec.length= spec_end - spec_start;
+ unparsed_spec.str= (char*) thd->memdup(spec_start, unparsed_spec.length+1);
+ unparsed_spec.str[unparsed_spec.length]= '\0';
+
+ if (!unparsed_spec.str)
+ {
+ my_error(ER_OUTOFMEMORY, MYF(ME_FATALERROR),
+ static_cast<int>(unparsed_spec.length));
+ return true;
+ }
+ return false;
+}
+
+
+/**
+ @brief
+ Create a clone of the specification for the given with table
+
+ @param thd The context of the statement containing this with element
+ @param with_table The reference to the table defined in this element for which
+ the clone is created.
+
+ @details
+ The method creates a clone of the specification used in this element.
+ The clone is created for the given reference to the table defined by
+ this element.
+ The clone is created when the string with the specification saved in
+ unparsed_spec is fed into the parser as an input string. The parsing
+ this string a unit object representing the specification is build.
+ A chain of all table references occurred in the specification is also
+ formed.
+ The method includes the new unit and its sub-unit into hierarchy of
+ the units of the main query. I also insert the constructed chain of the
+ table references into the chain of all table references of the main query.
+
+ @note
+ Clones is created only for not first references to tables defined in
+ the with clause. They are necessary for merged specifications because
+ the optimizer handles any such specification as independent on the others.
+ When a table defined in the with clause is materialized in a temporary table
+ one could do without specification clones. However in this case they
+ are created as well, because currently different table references to a
+ the same temporary table cannot share the same definition structure.
+
+ @retval
+ pointer to the built clone if succeeds
+ NULL - otherwise
+*/
+
+st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
+ TABLE_LIST *with_table)
+{
+ LEX *lex;
+ st_select_lex_unit *res= NULL;
+ Query_arena backup;
+ Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
+
+ if (!(lex= (LEX*) new(thd->mem_root) st_lex_local))
+ {
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+ return res;
+ }
+ LEX *old_lex= thd->lex;
+ thd->lex= lex;
+
+ bool parse_status= false;
+ Parser_state parser_state;
+ TABLE_LIST *spec_tables;
+ TABLE_LIST *spec_tables_tail;
+ st_select_lex *with_select;
+
+ if (parser_state.init(thd, unparsed_spec.str, unparsed_spec.length))
+ goto err;
+ lex_start(thd);
+ with_select= &lex->select_lex;
+ with_select->select_number= ++thd->select_number;
+ parse_status= parse_sql(thd, &parser_state, 0);
+ if (parse_status)
+ goto err;
+ spec_tables= lex->query_tables;
+ spec_tables_tail= 0;
+ for (TABLE_LIST *tbl= spec_tables;
+ tbl;
+ tbl= tbl->next_global)
+ {
+ tbl->grant.privilege= with_table->grant.privilege;
+ spec_tables_tail= tbl;
+ }
+ if (spec_tables)
+ {
+ if (with_table->next_global)
+ {
+ spec_tables_tail->next_global= with_table->next_global;
+ with_table->next_global->prev_global= &spec_tables_tail->next_global;
+ }
+ else
+ {
+ old_lex->query_tables_last= &spec_tables_tail->next_global;
+ }
+ spec_tables->prev_global= &with_table->next_global;
+ with_table->next_global= spec_tables;
+ }
+ res= &lex->unit;
+
+ lex->unit.include_down(with_table->select_lex);
+ lex->unit.set_slave(with_select);
+ old_lex->all_selects_list=
+ (st_select_lex*) (lex->all_selects_list->
+ insert_chain_before(
+ (st_select_lex_node **) &(old_lex->all_selects_list),
+ with_select));
+ lex_end(lex);
+err:
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+ thd->lex= old_lex;
+ return res;
+}
+
+
+/**
+ @brief
+ Rename columns of the unit derived from the spec of this with element
+ @param thd The context of the statement containing the with element
+ @param unit The specification of the with element or its clone
+
+ @details
+ The method assumes that the parameter unit is either specification itself
+ of this with element or a clone of this specification. The looks through
+ the column list in this with element. It reports an error if the cardinality
+ of this list differs from the cardinality of select lists in 'unit'.
+ Otherwise it renames the columns of the first select list and sets the flag
+ unit->column_list_is_processed to true preventing renaming columns for the
+ second time.
+
+ @retval
+ true if an error was reported
+ false otherwise
+*/
+
+bool
+With_element::rename_columns_of_derived_unit(THD *thd,
+ st_select_lex_unit *unit)
+{
+ if (unit->columns_are_renamed)
+ return false;
+
+ st_select_lex *select= unit->first_select();
+
+ if (column_list.elements) // The column list is optional
+ {
+ List_iterator_fast<Item> it(select->item_list);
+ List_iterator_fast<LEX_STRING> nm(column_list);
+ Item *item;
+ LEX_STRING *name;
+
+ if (column_list.elements != select->item_list.elements)
+ {
+ my_error(ER_WITH_COL_WRONG_LIST, MYF(0));
+ return true;
+ }
+ /* Rename the columns of the first select in the unit */
+ while ((item= it++, name= nm++))
+ {
+ item->set_name(thd, name->str, (uint) name->length, system_charset_info);
+ item->is_autogenerated_name= false;
+ }
+ }
+
+ make_valid_column_names(thd, select->item_list);
+
+ unit->columns_are_renamed= true;
+
+ return false;
+}
+
+
+/**
+ @brief
+ Perform context analysis the definition of an unreferenced table
+
+ @param thd The context of the statement containing this with element
+
+ @details
+ The method assumes that this with element contains the definition
+ of a table that is not used anywhere. In this case one has to check
+ that context conditions are met.
+
+ @retval
+ true if an error was reported
+ false otherwise
+*/
+
+bool With_element::prepare_unreferenced(THD *thd)
+{
+ bool rc= false;
+ st_select_lex *first_sl= spec->first_select();
+
+ /* Prevent name resolution for field references out of with elements */
+ for (st_select_lex *sl= first_sl;
+ sl;
+ sl= sl->next_select())
+ sl->context.outer_context= 0;
+
+ thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED;
+ if (!spec->prepared &&
+ (spec->prepare(thd, 0, 0) ||
+ rename_columns_of_derived_unit(thd, spec) ||
+ check_duplicate_names(thd, first_sl->item_list, 1)))
+ rc= true;
+
+ thd->lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;
+ return rc;
+}
+
+
+/**
+ @brief
+ Search for the definition of the given table referred in this select node
+
+ @param table reference to the table whose definition is searched for
+
+ @details
+ The method looks for the definition the table whose reference is occurred
+ in the FROM list of this select node. First it searches for it in the
+ with clause attached to the unit this select node belongs to. If such a
+ definition is not found there the embedding units are looked through.
+
+ @retval
+ pointer to the found definition if the search has been successful
+ NULL - otherwise
+*/
+
+With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table)
+{
+ With_element *found= NULL;
+ for (st_select_lex *sl= this;
+ sl;
+ sl= sl->master_unit()->outer_select())
+ {
+ With_clause *with_clause=sl->get_with_clause();
+ if (with_clause && (found= with_clause->find_table_def(table)))
+ return found;
+ }
+ return found;
+}
+
+
+/**
+ @brief
+ Set the specifying unit in this reference to a with table
+
+ @details
+ The method assumes that the given element with_elem defines the table T
+ this table reference refers to.
+ If this is the first reference to T the method just sets its specification
+ in the field 'derived' as the unit that yields T. Otherwise the method
+ first creates a clone specification and sets rather this clone in this field.
+
+ @retval
+ false on success
+ true on failure
+*/
+
+bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
+{
+ with= with_elem;
+ if (!with_elem->is_referenced())
+ derived= with_elem->spec;
+ else
+ {
+ if(!(derived= with_elem->clone_parsed_spec(thd, this)))
+ return true;
+ derived->with_element= with_elem;
+ }
+ with_elem->inc_references();
+ return false;
+}
+
+
+/**
+ @brief
+ Print this with clause
+
+ @param str Where to print to
+ @param query_type The mode of printing
+
+ @details
+ The method prints a string representation of this clause in the
+ string str. The parameter query_type specifies the mode of printing.
+*/
+
+void With_clause::print(String *str, enum_query_type query_type)
+{
+ str->append(STRING_WITH_LEN("WITH "));
+ if (with_recursive)
+ str->append(STRING_WITH_LEN("RECURSIVE "));
+ for (With_element *with_elem= first_elem;
+ with_elem != NULL;
+ with_elem= with_elem->next_elem)
+ {
+ with_elem->print(str, query_type);
+ if (with_elem != first_elem)
+ str->append(", ");
+ }
+}
+
+
+/**
+ @brief
+ Print this with element
+
+ @param str Where to print to
+ @param query_type The mode of printing
+
+ @details
+ The method prints a string representation of this with element in the
+ string str. The parameter query_type specifies the mode of printing.
+*/
+
+void With_element::print(String *str, enum_query_type query_type)
+{
+ str->append(query_name);
+ str->append(STRING_WITH_LEN(" AS "));
+ str->append('(');
+ spec->print(str, query_type);
+ str->append(')');
+}
+
diff --git a/sql/sql_cte.h b/sql/sql_cte.h
new file mode 100644
index 00000000000..0cbc9247af9
--- /dev/null
+++ b/sql/sql_cte.h
@@ -0,0 +1,178 @@
+#ifndef SQL_CTE_INCLUDED
+#define SQL_CTE_INCLUDED
+#include "sql_list.h"
+#include "sql_lex.h"
+
+class With_clause;
+
+/**
+ @class With_clause
+ @brief Set of with_elements
+
+ It has a reference to the first with element from this with clause.
+ This reference allows to navigate through all the elements of the with clause.
+ It contains a reference to the unit to which this with clause is attached.
+ It also contains a flag saying whether this with clause was specified as recursive.
+*/
+
+class With_element : public Sql_alloc
+{
+private:
+ With_clause *owner; // with clause this object belongs to
+ With_element *next_elem; // next element in the with clause
+ uint number; // number of the element in the with clause (starting from 0)
+ /*
+ The map dependency_map has 1 in the i-th position if the query that
+ specifies this element contains a reference to the element number i
+ in the query FROM list.
+ */
+ table_map elem_map; // The map where with only one 1 set in this->number
+ table_map dependency_map;
+ /*
+ Total number of references to this element in the FROM lists of
+ the queries that are in the scope of the element (including
+ subqueries and specifications of other with elements).
+ */
+ uint references;
+ /*
+ Unparsed specification of the query that specifies this element.
+ It used to build clones of the specification if they are needed.
+ */
+ LEX_STRING unparsed_spec;
+
+ /* Return the map where 1 is set only in the position for this element */
+ table_map get_elem_map() { return 1 << number; }
+
+public:
+ /*
+ The name of the table introduced by this with elememt. The name
+ can be used in FROM lists of the queries in the scope of the element.
+ */
+ LEX_STRING *query_name;
+ /*
+ Optional list of column names to name the columns of the table introduced
+ by this with element. It is used in the case when the names are not
+ inherited from the query that specified the table. Otherwise the list is
+ always empty.
+ */
+ List <LEX_STRING> column_list;
+ /* The query that specifies the table introduced by this with element */
+ st_select_lex_unit *spec;
+ /*
+ Set to true is recursion is used (directly or indirectly)
+ for the definition of this element
+ */
+ bool is_recursive;
+
+ With_element(LEX_STRING *name,
+ List <LEX_STRING> list,
+ st_select_lex_unit *unit)
+ : next_elem(NULL), dependency_map(0), references(0),
+ query_name(name), column_list(list), spec(unit),
+ is_recursive(false) {}
+
+ void check_dependencies_in_unit(st_select_lex_unit *unit);
+
+ void set_dependency_on(With_element *with_elem)
+ { dependency_map|= with_elem->get_elem_map(); }
+
+ bool check_dependency_on(With_element *with_elem)
+ { return dependency_map & with_elem->get_elem_map(); }
+
+ bool set_unparsed_spec(THD *thd, char *spec_start, char *spec_end);
+
+ st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table);
+
+ bool is_referenced() { return references != 0; }
+
+ void inc_references() { references++; }
+
+ bool rename_columns_of_derived_unit(THD *thd, st_select_lex_unit *unit);
+
+ bool prepare_unreferenced(THD *thd);
+
+ void print(String *str, enum_query_type query_type);
+
+ friend class With_clause;
+};
+
+
+/**
+ @class With_element
+ @brief Definition of a CTE table
+
+ It contains a reference to the name of the table introduced by this with element,
+ and a reference to the unit that specificies this table. Also it contains
+ a reference to the with clause to which this element belongs to.
+*/
+
+class With_clause : public Sql_alloc
+{
+private:
+ st_select_lex_unit *owner; // the unit this with clause attached to
+ With_element *first_elem; // the first definition in this with clause
+ With_element **last_next; // here is set the link for the next added element
+ uint elements; // number of the elements/defintions in this with clauses
+ /*
+ The with clause immediately containing this with clause if there is any,
+ otherwise NULL. Now used only at parsing.
+ */
+ With_clause *embedding_with_clause;
+ /*
+ The next with the clause of the chain of with clauses encountered
+ in the current statement
+ */
+ With_clause *next_with_clause;
+ /* Set to true if dependencies between with elements have been checked */
+ bool dependencies_are_checked;
+
+public:
+ /* If true the specifier RECURSIVE is present in the with clause */
+ bool with_recursive;
+
+ With_clause(bool recursive_fl, With_clause *emb_with_clause)
+ : owner(NULL), first_elem(NULL), elements(0),
+ embedding_with_clause(emb_with_clause), next_with_clause(NULL),
+ dependencies_are_checked(false),
+ with_recursive(recursive_fl)
+ { last_next= &first_elem; }
+
+ /* Add a new element to the current with clause */
+ bool add_with_element(With_element *elem)
+ {
+ elem->owner= this;
+ elem->number= elements;
+ owner= elem->spec;
+ owner->with_element= elem;
+ *last_next= elem;
+ last_next= &elem->next_elem;
+ elements++;
+ return false;
+ }
+
+ /* Add this with clause to the list of with clauses used in the statement */
+ void add_to_list(With_clause ** &last_next)
+ {
+ *last_next= this;
+ last_next= &this->next_with_clause;
+ }
+
+ With_clause *pop() { return embedding_with_clause; }
+
+ bool check_dependencies();
+
+ With_element *find_table_def(TABLE_LIST *table);
+
+ With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
+
+ bool prepare_unreferenced_elements(THD *thd);
+
+ void print(String *str, enum_query_type query_type);
+
+ friend
+ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list);
+
+};
+
+
+#endif /* SQL_CTE_INCLUDED */
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index baba4a876b3..1ef83b3bf1f 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -30,6 +30,7 @@
#include "sql_base.h"
#include "sql_view.h" // check_duplicate_names
#include "sql_acl.h" // SELECT_ACL
+#include "sql_cte.h"
typedef bool (*dt_processor)(THD *thd, LEX *lex, TABLE_LIST *derived);
@@ -670,6 +671,9 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
// st_select_lex_unit::prepare correctly work for single select
if ((res= unit->prepare(thd, derived->derived_result, 0)))
goto exit;
+ if (derived->with &&
+ (res= derived->with->rename_columns_of_derived_unit(thd, unit)))
+ goto exit;
lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_DERIVED;
if ((res= check_duplicate_names(thd, unit->types, 0)))
goto exit;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b529a0835ca..f7cc9e87767 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -29,6 +29,7 @@
#include "sp_head.h"
#include "sp.h"
#include "sql_select.h"
+#include "sql_cte.h"
static int lex_one_token(YYSTYPE *yylval, THD *thd);
@@ -668,11 +669,15 @@ void lex_start(THD *thd)
/* 'parent_lex' is used in init_query() so it must be before it. */
lex->select_lex.parent_lex= lex;
lex->select_lex.init_query();
+ lex->curr_with_clause= 0;
+ lex->with_clauses_list= 0;
+ lex->with_clauses_list_last_next= &lex->with_clauses_list;
lex->value_list.empty();
lex->update_list.empty();
lex->set_var_list.empty();
lex->param_list.empty();
lex->view_list.empty();
+ lex->with_column_list.empty();
lex->with_persistent_for_clause= FALSE;
lex->column_list= NULL;
lex->index_list= NULL;
@@ -2065,6 +2070,9 @@ void st_select_lex_unit::init_query()
found_rows_for_union= 0;
insert_table_with_stored_vcol= 0;
derived= 0;
+ with_clause= 0;
+ with_element= 0;
+ columns_are_renamed= false;
}
void st_select_lex::init_query()
@@ -2248,6 +2256,37 @@ void st_select_lex_node::fast_exclude()
}
+/**
+ @brief
+ Insert a new chain of nodes into another chain before a particular link
+
+ @param in/out
+ ptr_pos_to_insert the address of the chain pointer pointing to the link
+ before which the subchain has to be inserted
+ @param
+ end_chain_node the last link of the subchain to be inserted
+
+ @details
+ The method inserts the chain of nodes starting from this node and ending
+ with the node nd_chain_node into another chain of nodes before the node
+ pointed to by *ptr_pos_to_insert.
+ It is assumed that ptr_pos_to_insert belongs to the chain where we insert.
+ So it must be updated.
+
+ @retval
+ The method returns the pointer to the first link of the inserted chain
+*/
+
+st_select_lex_node *st_select_lex_node:: insert_chain_before(
+ st_select_lex_node **ptr_pos_to_insert,
+ st_select_lex_node *end_chain_node)
+{
+ end_chain_node->link_next= *ptr_pos_to_insert;
+ (*ptr_pos_to_insert)->link_prev= &end_chain_node->link_next;
+ this->link_prev= ptr_pos_to_insert;
+ return this;
+}
+
/*
Exclude a node from the tree lex structure, but leave it in the global
list of nodes.
@@ -2637,6 +2676,8 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
void st_select_lex_unit::print(String *str, enum_query_type query_type)
{
bool union_all= !union_distinct;
+ if (with_clause)
+ with_clause->print(str, query_type);
for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
{
if (sl != first_select())
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index bda4ceb7b91..c3935130fb3 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -48,6 +48,8 @@ class Item_func_match;
class File_parser;
class Key_part_spec;
struct sql_digest_state;
+class With_clause;
+
#define ALLOC_ROOT_SET 1024
@@ -178,6 +180,7 @@ const LEX_STRING sp_data_access_name[]=
#define DERIVED_SUBQUERY 1
#define DERIVED_VIEW 2
+#define DERIVED_WITH 4
enum enum_view_create_mode
{
@@ -540,7 +543,9 @@ public:
List<String> *partition_names= 0,
LEX_STRING *option= 0);
virtual void set_lock_for_tables(thr_lock_type lock_type) {}
-
+ void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; }
+ st_select_lex_node *insert_chain_before(st_select_lex_node **ptr_pos_to_insert,
+ st_select_lex_node *end_chain_node);
friend class st_select_lex_unit;
friend bool mysql_new_select(LEX *lex, bool move_down);
friend bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table,
@@ -638,6 +643,10 @@ public:
derived tables/views handling.
*/
TABLE_LIST *derived;
+ /* With clause attached to this unit (if any) */
+ With_clause *with_clause;
+ /* With element where this unit is used as the specification (if any) */
+ With_element *with_element;
/* thread handler */
THD *thd;
/*
@@ -646,7 +655,7 @@ public:
*/
st_select_lex *fake_select_lex;
/**
- SELECT_LEX that stores LIMIT and OFFSET for UNION ALL when no
+ SELECT_LEX that stores LIMIT and OFFSET for UNION ALL when noq
fake_select_lex is used.
*/
st_select_lex *saved_fake_select_lex;
@@ -662,12 +671,15 @@ public:
*/
TABLE *insert_table_with_stored_vcol;
+ bool columns_are_renamed;
+
void init_query();
st_select_lex* outer_select();
st_select_lex* first_select()
{
return reinterpret_cast<st_select_lex*>(slave);
}
+ void set_with_clause(With_clause *with_cl) { with_clause= with_cl; }
st_select_lex_unit* next_unit()
{
return reinterpret_cast<st_select_lex_unit*>(next);
@@ -1062,6 +1074,19 @@ public:
void set_non_agg_field_used(bool val) { m_non_agg_field_used= val; }
void set_agg_func_used(bool val) { m_agg_func_used= val; }
+ void set_with_clause(With_clause *with_clause)
+ {
+ master_unit()->with_clause= with_clause;
+ }
+ With_clause *get_with_clause()
+ {
+ return master_unit()->with_clause;
+ }
+ With_element *get_with_element()
+ {
+ return master_unit()->with_element;
+ }
+ With_element *find_table_def_in_with_clauses(TABLE_LIST *table);
private:
bool m_non_agg_field_used;
@@ -2409,7 +2434,16 @@ struct LEX: public Query_tables_list
SELECT_LEX *current_select;
/* list of all SELECT_LEX */
SELECT_LEX *all_selects_list;
-
+ /* current with clause in parsing if any, otherwise 0*/
+ With_clause *curr_with_clause;
+ /* pointer to the first with clause in the current statemant */
+ With_clause *with_clauses_list;
+ /*
+ (*with_clauses_list_last_next) contains a pointer to the last
+ with clause in the current statement
+ */
+ With_clause **with_clauses_list_last_next;
+
/* Query Plan Footprint of a currently running select */
Explain_query *explain;
@@ -2475,6 +2509,7 @@ public:
List<Item_func_set_user_var> set_var_list; // in-query assignment list
List<Item_param> param_list;
List<LEX_STRING> view_list; // view list (list of field names in view)
+ List<LEX_STRING> with_column_list; // list of column names in with_list_element
List<LEX_STRING> *column_list; // list of column names (in ANALYZE)
List<LEX_STRING> *index_list; // list of index names (in ANALYZE)
/*
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6211bcc02a8..6bd0e793a67 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -92,6 +92,7 @@
#include "transaction.h"
#include "sql_audit.h"
#include "sql_prepare.h"
+#include "sql_cte.h"
#include "debug_sync.h"
#include "probes_mysql.h"
#include "set_var.h"
@@ -6246,6 +6247,9 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables)
new (thd->mem_root) Item_int(thd,
(ulonglong) thd->variables.select_limit);
}
+ if (check_dependencies_in_with_clauses(lex->with_clauses_list))
+ return 1;
+
if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
{
if (lex->describe)
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index c315f3cfa4e..3e1340d8b3c 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -102,6 +102,7 @@ When one supplies long data for a placeholder:
#include "sql_acl.h" // *_ACL
#include "sql_derived.h" // mysql_derived_prepare,
// mysql_handle_derived
+#include "sql_cte.h"
#include "sql_cursor.h"
#include "sql_show.h"
#include "sql_repl.h"
@@ -1506,6 +1507,8 @@ static int mysql_test_select(Prepared_statement *stmt,
lex->select_lex.context.resolve_in_select_list= TRUE;
ulong privilege= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL;
+ if (check_dependencies_in_with_clauses(lex->with_clauses_list))
+ goto error;
if (tables)
{
if (check_table_access(thd, privilege, tables, FALSE, UINT_MAX, FALSE))
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4ce29f1d666..cbac7c1bcc2 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -53,6 +53,7 @@
#include "log_slow.h"
#include "sql_derived.h"
#include "sql_statistics.h"
+#include "sql_cte.h"
#include "debug_sync.h" // DEBUG_SYNC
#include <m_ctype.h>
@@ -828,6 +829,10 @@ JOIN::prepare(Item ***rref_pointer_array,
DBUG_RETURN(-1); /* purecov: inspected */
thd->lex->allow_sum_func= save_allow_sum_func;
}
+
+ With_clause *with_clause=select_lex->get_with_clause();
+ if (with_clause && with_clause->prepare_unreferenced_elements(thd))
+ DBUG_RETURN(1);
int res= check_and_do_in_subquery_rewrites(this);
@@ -24192,9 +24197,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table,
/* There should be no attempts to save query plans for merged selects */
DBUG_ASSERT(!join->select_lex->master_unit()->derived ||
- join->select_lex->master_unit()->derived->is_materialized_derived());
-
- explain= NULL;
+ join->select_lex->master_unit()->derived->is_materialized_derived() ||
+ join->select_lex->master_unit()->derived->is_with_table());
/* Don't log this into the slow query log */
@@ -24704,11 +24708,19 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str,
}
else if (derived)
{
- // A derived table
- str->append('(');
- derived->print(str, query_type);
- str->append(')');
- cmp_name= ""; // Force printing of alias
+ if (!derived->derived->is_with_table())
+ {
+ // A derived table
+ str->append('(');
+ derived->print(str, query_type);
+ str->append(')');
+ cmp_name= ""; // Force printing of alias
+ }
+ else
+ {
+ append_identifier(thd, str, table_name, table_name_length);
+ cmp_name= table_name;
+ }
}
else
{
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 4dac4f91f74..b063e9200e5 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -167,7 +167,7 @@ err:
@param item_list List of Items which should be checked
*/
-static void make_valid_column_names(THD *thd, List<Item> &item_list)
+void make_valid_column_names(THD *thd, List<Item> &item_list)
{
Item *item;
uint name_len;
diff --git a/sql/sql_view.h b/sql/sql_view.h
index 9c75643fd48..b9eb92198f8 100644
--- a/sql/sql_view.h
+++ b/sql/sql_view.h
@@ -56,8 +56,12 @@ bool check_duplicate_names(THD *thd, List<Item>& item_list,
bool mysql_rename_view(THD *thd, const char *new_db, const char *new_name,
TABLE_LIST *view);
+void make_valid_column_names(THD *thd, List<Item> &item_list);
+
#define VIEW_ANY_ACL (SELECT_ACL | UPDATE_ACL | INSERT_ACL | DELETE_ACL)
extern const LEX_STRING view_type;
+void make_valid_column_names(List<Item> &item_list);
+
#endif /* SQL_VIEW_INCLUDED */
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 6b896eabde7..1870b3f719f 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -54,6 +54,7 @@
#include "sql_handler.h" // Sql_cmd_handler_*
#include "sql_signal.h"
#include "sql_get_diagnostics.h" // Sql_cmd_get_diagnostics
+#include "sql_cte.h"
#include "event_parse_data.h"
#include "create_options.h"
#include <myisam.h>
@@ -988,6 +989,8 @@ bool LEX::set_bincmp(CHARSET_INFO *cs, bool bin)
class sp_label *splabel;
class sp_name *spname;
class sp_variable *spvar;
+ class With_clause *with_clause;
+
handlerton *db_type;
st_select_lex *select_lex;
struct p_elem_val *p_elem_value;
@@ -1485,6 +1488,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token REAL /* SQL-2003-R */
%token REBUILD_SYM
%token RECOVER_SYM
+%token RECURSIVE_SYM
%token REDOFILE_SYM
%token REDO_BUFFER_SIZE_SYM
%token REDUNDANT_SYM
@@ -1769,6 +1773,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
case_stmt_body opt_bin_mod
opt_if_exists_table_element opt_if_not_exists_table_element
opt_into opt_procedure_clause
+ opt_recursive
%type <object_ddl_options>
create_or_replace
@@ -2011,6 +2016,10 @@ END_OF_INPUT
THEN_SYM WHEN_SYM DIV_SYM MOD_SYM OR2_SYM AND_AND_SYM DELETE_SYM
ROLE_SYM
+%type <with_clause> opt_with_clause with_clause
+
+%type <lex_str_ptr> query_name
+
%%
@@ -8457,10 +8466,11 @@ opt_ignore_leaves:
select:
- select_init
+ opt_with_clause select_init
{
LEX *lex= Lex;
lex->sql_command= SQLCOM_SELECT;
+ lex->current_select->set_with_clause($1);
}
;
@@ -10886,20 +10896,20 @@ table_factor:
and our parser. Possibly this rule could be replaced by our
query_expression_body.
*/
- | '(' get_select_lex select_derived_union ')' opt_table_alias
+ | '('opt_with_clause get_select_lex select_derived_union ')' opt_table_alias
{
- /* Use $2 instead of Lex->current_select as derived table will
+ /* Use $3 instead of Lex->current_select as derived table will
alter value of Lex->current_select. */
- if (!($3 || $5) && $2->embedding &&
- !$2->embedding->nested_join->join_list.elements)
+ if (!($4 || $6) && $3->embedding &&
+ !$3->embedding->nested_join->join_list.elements)
{
- /* we have a derived table ($3 == NULL) but no alias,
+ /* we have a derived table ($4 == NULL) but no alias,
Since we are nested in further parentheses so we
can pass NULL to the outer level parentheses
Permits parsing of "((((select ...))) as xyz)" */
$$= 0;
}
- else if (!$3)
+ else if (!$4)
{
/* Handle case of derived table, alias may be NULL if there
are no outer parentheses, add_table_to_list() will throw
@@ -10907,12 +10917,13 @@ table_factor:
LEX *lex=Lex;
SELECT_LEX *sel= lex->current_select;
SELECT_LEX_UNIT *unit= sel->master_unit();
+ unit->set_with_clause($2);
lex->current_select= sel= unit->outer_select();
Table_ident *ti= new (thd->mem_root) Table_ident(unit);
if (ti == NULL)
MYSQL_YYABORT;
if (!($$= sel->add_table_to_list(lex->thd,
- ti, $5, 0,
+ ti, $6, 0,
TL_READ, MDL_SHARED_READ)))
MYSQL_YYABORT;
@@ -10920,11 +10931,11 @@ table_factor:
lex->pop_context();
lex->nest_level--;
}
- /*else if (($3->select_lex &&
- $3->select_lex->master_unit()->is_union() &&
- ($3->select_lex->master_unit()->first_select() ==
- $3->select_lex || !$3->lifted)) || $5)*/
- else if ($5 != NULL)
+ /*else if (($4->select_lex &&
+ $4->select_lex->master_unit()->is_union() &&
+ ($4->select_lex->master_unit()->first_select() ==
+ $4->select_lex || !$4->lifted)) || $6)*/
+ else if ($6 != NULL)
{
/*
Tables with or without joins within parentheses cannot
@@ -10937,7 +10948,7 @@ table_factor:
{
/* nested join: FROM (t1 JOIN t2 ...),
nest_level is the same as in the outer query */
- $$= $3;
+ $$= $4;
}
/*
Fields in derived table can be used in upper select in
@@ -13736,8 +13747,93 @@ temporal_literal:
;
+opt_with_clause:
+ /*empty */ { $$= 0; }
+ | with_clause
+ {
+ $$= $1;
+ Lex->derived_tables|= DERIVED_WITH;
+ }
+ ;
+
+
+with_clause:
+ WITH opt_recursive
+ {
+ With_clause *with_clause=
+ new With_clause($2, Lex->curr_with_clause);
+ if (with_clause == NULL)
+ MYSQL_YYABORT;
+ Lex->curr_with_clause= with_clause;
+ with_clause->add_to_list(Lex->with_clauses_list_last_next);
+ }
+ with_list
+ {
+ $$= Lex->curr_with_clause;
+ Lex->curr_with_clause= Lex->curr_with_clause->pop();
+ }
+ ;
+
+
+opt_recursive:
+ /*empty*/ { $$= 0; }
+ | RECURSIVE_SYM { $$= 1; }
+ ;
+
+with_list:
+ with_list_element
+ | with_list ',' with_list_element
+ ;
+
+
+with_list_element:
+ query_name
+ opt_with_column_list
+ AS '(' remember_name subselect remember_end ')'
+ {
+ With_element *elem= new With_element($1, Lex->with_column_list, $6->master_unit());
+ if (elem == NULL || Lex->curr_with_clause->add_with_element(elem))
+ MYSQL_YYABORT;
+ Lex->with_column_list.empty();
+ if (elem->set_unparsed_spec(thd, $5+1, $7))
+ MYSQL_YYABORT;
+ }
+ ;
+
+opt_with_column_list:
+ /* empty */
+ {}
+ | '(' with_column_list ')'
+ ;
+
+
+with_column_list:
+ ident
+ {
+ Lex->with_column_list.push_back((LEX_STRING*)
+ thd->memdup(&$1, sizeof(LEX_STRING)));
+ }
+ | with_column_list ',' ident
+ {
+ Lex->with_column_list.push_back((LEX_STRING*)
+ thd->memdup(&$3, sizeof(LEX_STRING)));
+ }
+ ;
+
+
+query_name:
+ ident
+ {
+ $$= (LEX_STRING *) thd->memdup(&$1, sizeof(LEX_STRING));
+ if ($$ == NULL)
+ MYSQL_YYABORT;
+ }
+ ;
+
+
+
/**********************************************************************
** Creating different items.
**********************************************************************/
@@ -16020,9 +16116,10 @@ query_expression_body:
/* Corresponds to <query expression> in the SQL:2003 standard. */
subselect:
- subselect_start query_expression_body subselect_end
+ subselect_start opt_with_clause query_expression_body subselect_end
{
- $$= $2;
+ $3->set_with_clause($2);
+ $$= $3;
}
;
@@ -16249,7 +16346,7 @@ view_select:
lex->parsing_options.allows_derived= FALSE;
lex->create_view_select.str= (char *) YYLIP->get_cpp_ptr();
}
- view_select_aux view_check_option
+ opt_with_clause view_select_aux view_check_option
{
LEX *lex= Lex;
uint len= YYLIP->get_cpp_ptr() - lex->create_view_select.str;
@@ -16261,6 +16358,7 @@ view_select:
lex->parsing_options.allows_select_into= TRUE;
lex->parsing_options.allows_select_procedure= TRUE;
lex->parsing_options.allows_derived= TRUE;
+ lex->current_select->set_with_clause($2);
}
;
diff --git a/sql/table.cc b/sql/table.cc
index 98421ec2aac..07e2876f5ba 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7281,7 +7281,9 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
*/
if (is_merged_derived())
{
- if (is_view() || unit->prepared)
+ if (is_view() ||
+ (unit->prepared &&
+ !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)))
create_field_translation(thd);
}
@@ -7423,6 +7425,11 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock)
}
}
+bool TABLE_LIST::is_with_table()
+{
+ return derived && derived->with_element;
+}
+
uint TABLE_SHARE::actual_n_key_parts(THD *thd)
{
return use_ext_keys &&
diff --git a/sql/table.h b/sql/table.h
index c45e86b695e..562eae99500 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -48,6 +48,7 @@ class ACL_internal_schema_access;
class ACL_internal_table_access;
class Field;
class Table_statistics;
+class With_element;
class TDC_element;
/*
@@ -1840,6 +1841,7 @@ struct TABLE_LIST
derived tables. Use TABLE_LIST::is_anonymous_derived_table().
*/
st_select_lex_unit *derived; /* SELECT_LEX_UNIT of derived table */
+ With_element *with; /* With element of with_table */
ST_SCHEMA_TABLE *schema_table; /* Information_schema table */
st_select_lex *schema_select_lex;
/*
@@ -2204,6 +2206,7 @@ struct TABLE_LIST
{
return (derived_type & DTYPE_TABLE);
}
+ bool is_with_table();
inline void set_view()
{
derived_type= DTYPE_VIEW;
@@ -2244,6 +2247,7 @@ struct TABLE_LIST
{
derived_type|= DTYPE_MULTITABLE;
}
+ bool set_as_with_table(THD *thd, With_element *with_elem);
void reset_const_table();
bool handle_derived(LEX *lex, uint phases);
View Lorry Controller Status