diff options
Diffstat (limited to 'mysql-test/t/table_value_constr.test')
| -rw-r--r-- | mysql-test/t/table_value_constr.test | 811 |
1 files changed, 711 insertions, 100 deletions
diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test index 4aba843bbac..0021c27486c 100644 --- a/mysql-test/t/table_value_constr.test +++ b/mysql-test/t/table_value_constr.test @@ -1,6 +1,7 @@ create table t1 (a int, b int); -insert into t1 values (1,2),(4,6),(9,7),(1,1),(2,5),(7,8); +insert into t1 values (1,2),(4,6),(9,7), + (1,1),(2,5),(7,8); --echo # just VALUES @@ -10,164 +11,443 @@ values (1,2), (3,4), (5.6,0); values ("abc", "def"); ---echo # UNION using VALUES structure(s) +--echo # UNION that uses VALUES structure(s) -select 1,2 union values (1,2); - -values (1,2) union select 1,2; - -select 1,2 union values (1,2),(3,4),(5,6),(7,8); - -select 3,7 union values (1,2),(3,4),(5,6); - -select 3,7,4 union values (1,2,5),(4,5,6); - -select 1,2 union values (1,7),(3,6.5); - -select 1,2 union values (1,2.0),(3,6); - -select 1.8,2 union values (1,2),(3,6); - -values (1,2.4),(3,6) union select 2.8,9; - -values (1,2),(3,4),(5,6),(7,8) union select 5,6; - -select "ab","cdf" union values ("al","zl"),("we","q"); - -values ("ab", "cdf") union select "ab","cdf"; +select 1,2 +union +values (1,2); -values (1,2) union values (1,2),(5,6); +values (1,2) +union +select 1,2; -values (1,2) union values (3,4),(5,6); +select 1,2 +union +values (1,2),(3,4),(5,6),(7,8); -values (1,2) union values (1,2) union values (4,5); +select 3,7 +union +values (1,2),(3,4),(5,6); ---echo # UNION ALL using VALUES structure +select 3,7,4 +union +values (1,2,5),(4,5,6); -values (1,2),(3,4) union all select 5,6; +select 1,2 +union +values (1,7),(3,6.5); -values (1,2),(3,4) union all select 1,2; +select 1,2 +union +values (1,2.0),(3,6); -select 5,6 union all values (1,2),(3,4); +select 1.8,2 +union +values (1,2),(3,6); -select 1,2 union all values (1,2),(3,4); +values (1,2.4),(3,6) +union +select 2.8,9; -values (1,2) union all values (1,2),(5,6); +values (1,2),(3,4),(5,6),(7,8) +union +select 5,6; -values (1,2) union all values (3,4),(5,6); +select "ab","cdf" +union +values ("al","zl"),("we","q"); -values (1,2) union all values (1,2) union all values (4,5); +values ("ab", "cdf") +union +select "ab","cdf"; -values (1,2) union all values (1,2) union values (1,2); +values (1,2) +union +values (1,2),(5,6); -values (1,2) union values (1,2) union all values (1,2); +values (1,2) +union +values (3,4),(5,6); ---echo # EXCEPT using VALUES structure(s) +values (1,2) +union +values (1,2) +union values (4,5); -select 1,2 except values (3,4),(5,6); +--echo # UNION ALL that uses VALUES structure -select 1,2 except values (1,2),(3,4); +values (1,2),(3,4) +union all +select 5,6; -values (1,2),(3,4) except select 5,6; +values (1,2),(3,4) +union all +select 1,2; -values (1,2),(3,4) except select 1,2; +select 5,6 +union all +values (1,2),(3,4); -values (1,2),(3,4) except values (5,6); +select 1,2 +union all +values (1,2),(3,4); -values (1,2),(3,4) except values (1,2); +values (1,2) +union all +values (1,2),(5,6); ---echo # INTERSECT using VALUES structure(s) +values (1,2) +union all +values (3,4),(5,6); -select 1,2 intersect values (3,4),(5,6); +values (1,2) +union all +values (1,2) +union all +values (4,5); -select 1,2 intersect values (1,2),(3,4); +values (1,2) +union all +values (1,2) +union values (1,2); -values (1,2),(3,4) intersect select 5,6; +values (1,2) +union +values (1,2) +union all +values (1,2); -values (1,2),(3,4) intersect select 1,2; +--echo # EXCEPT that uses VALUES structure(s) -values (1,2),(3,4) intersect values (5,6); +select 1,2 +except +values (3,4),(5,6); -values (1,2),(3,4) intersect values (1,2); +select 1,2 +except +values (1,2),(3,4); ---echo # combination of different structures using VALUES structures : UNION + EXCEPT +values (1,2),(3,4) +except +select 5,6; -values (1,2),(3,4) except select 1,2 union values (1,2); +values (1,2),(3,4) +except +select 1,2; -values (1,2),(3,4) except values (1,2) union values (1,2); +values (1,2),(3,4) +except +values (5,6); -values (1,2),(3,4) except values (1,2) union values (3,4); +values (1,2),(3,4) +except +values (1,2); -values (1,2),(3,4) union values (1,2) except values (1,2); +--echo # INTERSECT that uses VALUES structure(s) ---echo # combination of different structures using VALUES structures : UNION ALL + EXCEPT +select 1,2 +intersect +values (3,4),(5,6); -values (1,2),(3,4) except select 1,2 union all values (1,2); +select 1,2 +intersect +values (1,2),(3,4); -values (1,2),(3,4) except values (1,2) union all values (1,2); +values (1,2),(3,4) +intersect +select 5,6; -values (1,2),(3,4) except values (1,2) union all values (3,4); +values (1,2),(3,4) +intersect +select 1,2; -values (1,2),(3,4) union all values (1,2) except values (1,2); +values (1,2),(3,4) +intersect +values (5,6); ---echo # combination of different structures using VALUES structures : UNION + INTERSECT +values (1,2),(3,4) +intersect +values (1,2); -values (1,2),(3,4) intersect select 1,2 union values (1,2); +--echo # combination of different structures that uses VALUES structures : UNION + EXCEPT -values (1,2),(3,4) intersect values (1,2) union values (1,2); +values (1,2),(3,4) +except +select 1,2 +union values (1,2); -values (1,2),(3,4) intersect values (1,2) union values (3,4); +values (1,2),(3,4) +except +values (1,2) +union +values (1,2); -values (1,2),(3,4) union values (1,2) intersect values (1,2); +values (1,2),(3,4) +except +values (1,2) +union +values (3,4); ---echo # combination of different structures using VALUES structures : UNION ALL + INTERSECT +values (1,2),(3,4) +union +values (1,2) +except +values (1,2); -values (1,2),(3,4) intersect select 1,2 union all values (1,2); +--echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT -values (1,2),(3,4) intersect values (1,2) union all values (1,2); +values (1,2),(3,4) +except +select 1,2 +union all +values (1,2); -values (1,2),(3,4) intersect values (1,2) union all values (3,4); +values (1,2),(3,4) +except +values (1,2) +union all +values (1,2); -values (1,2),(3,4) union all values (1,2) intersect values (1,2); +values (1,2),(3,4) +except +values (1,2) +union all +values (3,4); ---echo # combination of different structures using VALUES structures : UNION + UNION ALL +values (1,2),(3,4) +union all +values (1,2) +except +values (1,2); -values (1,2),(3,4) union all select 1,2 union values (1,2); +--echo # combination of different structures that uses VALUES structures : UNION + INTERSECT -values (1,2),(3,4) union all values (1,2) union values (1,2); +values (1,2),(3,4) +intersect +select 1,2 +union +values (1,2); -values (1,2),(3,4) union all values (1,2) union values (3,4); +values (1,2),(3,4) +intersect +values (1,2) +union +values (1,2); -values (1,2),(3,4) union values (1,2) union all values (1,2); +values (1,2),(3,4) +intersect +values (1,2) +union +values (3,4); -values (1,2) union values (1,2) union all values (1,2); +values (1,2),(3,4) +union +values (1,2) +intersect +values (1,2); ---echo # CTE using VALUES structure(s) +--echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT -with t2 as (values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +intersect +select 1,2 +union all +values (1,2); -with t2 as (select 1,2 union values (1,2)) select * from t2; +values (1,2),(3,4) +intersect +values (1,2) +union all +values (1,2); -with t2 as (select 1,2 union values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +intersect +values (1,2) +union all +values (3,4); -with t2 as (values (1,2) union select 1,2) select * from t2; +values (1,2),(3,4) +union all +values (1,2) +intersect +values (1,2); -with t2 as (values (1,2),(3,4) union select 1,2) select * from t2; +--echo # combination of different structures that uses VALUES structures : UNION + UNION ALL -with t2 as (values (5,6) union values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +union all +select 1,2 +union +values (1,2); -with t2 as (values (1,2) union values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +union all +values (1,2) +union +values (1,2); -with t2 as (select 1,2 union all values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +union all +values (1,2) +union +values (3,4); -with t2 as (values (1,2),(3,4) union all select 1,2) select * from t2; +values (1,2),(3,4) +union +values (1,2) +union all +values (1,2); -with t2 as (values (1,2) union all values (1,2),(3,4)) select * from t2; +values (1,2) +union +values (1,2) +union all +values (1,2); ---echo # Derived table using VALUES structure(s) +--echo # CTE that uses VALUES structure(s) : non-recursive CTE + +with t2 as +( + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + select 1,2 + union + values (1,2) +) +select * from t2; + +with t2 as +( + select 1,2 + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2) + union + select 1,2 +) +select * from t2; + +with t2 as +( + values (1,2),(3,4) + union + select 1,2 +) +select * from t2; + +with t2 as +( + values (5,6) + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2) + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + select 1,2 + union all + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2),(3,4) + union all + select 1,2 +) +select * from t2; + +with t2 as +( + values (1,2) + union all + values (1,2),(3,4) +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor + +with recursive t2(a,b) as +( + values(1,1) + union + select t1.a, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +with recursive t2(a,b) as +( + values(1,1) + union + select t1.a+1, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors + +with recursive t2(a,b) as +( + values(1,1) + union + values (3,4) + union + select t2.a+1, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL + +with recursive t2(a,b,st) as +( + values(1,1,1) + union all + select t2.a, t1.b, t2.st+1 + from t1,t2 + where t1.a=t2.a and st<3 +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements) + +with recursive fact(n,f) as +( + values(1,1) + union + select n+1,f*n from fact where n < 10 +) +select * from fact; + +--echo # Derived table that uses VALUES structure(s) : singe VALUES structure select * from (values (1,2),(3,4)) as t2; +--echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) + select * from (select 1,2 union values (1,2)) as t2; select * from (select 1,2 union values (1,2),(3,4)) as t2; @@ -180,13 +460,15 @@ select * from (values (5,6) union values (1,2),(3,4)) as t2; select * from (values (1,2) union values (1,2),(3,4)) as t2; +--echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) + select * from (select 1,2 union all values (1,2),(3,4)) as t2; select * from (values (1,2),(3,4) union all select 1,2) as t2; select * from (values (1,2) union all values (1,2),(3,4)) as t2; ---echo # CREATE VIEW using VALUES structure(s) +--echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure let $drop_view= drop view v1; let $select_view= select * from v1; @@ -196,49 +478,378 @@ create view v1 as values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as select 1,2 union values (1,2); +--echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s) + +create view v1 as + select 1,2 + union + values (1,2); eval $select_view; eval $drop_view; -create view v1 as select 1,2 union values (1,2),(3,4); +create view v1 as + select 1,2 + union + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as values (1,2) union select 1,2; +create view v1 as + values (1,2) + union + select 1,2; eval $select_view; eval $drop_view; -create view v1 as values (1,2),(3,4) union select 1,2; +create view v1 as + values (1,2),(3,4) + union + select 1,2; eval $select_view; eval $drop_view; -create view v1 as values (5,6) union values (1,2),(3,4); +create view v1 as + values (5,6) + union + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as values (1,2) union values (1,2),(3,4); +--echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s) + +create view v1 as + values (1,2) + union + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as select 1,2 union all values (1,2),(3,4); +create view v1 as + select 1,2 + union all + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as values (1,2),(3,4) union all select 1,2; +create view v1 as + values (1,2),(3,4) + union all + select 1,2; eval $select_view; eval $drop_view; -create view v1 as values (1,2) union all values (1,2),(3,4); +create view v1 as + values (1,2) + union all + values (1,2),(3,4); eval $select_view; eval $drop_view; +--echo # prepare statement that uses VALUES structure(s): single VALUES structure + +prepare stmt1 from " +values (1,2); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) + +prepare stmt1 from " + select 1,2 + union + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (1,2),(3,4) + union + select 1,2; +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + select 1,2 + union + values (3,4) + union + values (1,2); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (5,6) + union + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +prepare stmt1 from " + select 1,2 + union + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (1,2),(3,4) + union all + select 1,2; +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + select 1,2 + union all + values (3,4) + union all + values (1,2); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (1,2) + union all + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # explain query that uses VALUES structure(s): single VALUES structure + +explain +values (1,2); + +explain format=json +values (1,2); + +--echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s) + +explain +select 1,2 +union +values (1,2),(3,4); + +explain +values (1,2),(3,4) +union +select 1,2; + +explain +values (5,6) +union +values (1,2),(3,4); + +explain format=json +select 1,2 +union +values (1,2),(3,4); + +explain format=json +values (1,2),(3,4) +union +select 1,2; + +explain format=json +values (5,6) +union +values (1,2),(3,4); + +explain +select 1,2 +union +values (3,4) +union +values (1,2); + +explain format=json +select 1,2 +union +values (3,4) +union +values (1,2); + +--echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +explain +select 1,2 +union +values (1,2),(3,4); + +explain +values (1,2),(3,4) +union all +select 1,2; + +explain +values (1,2) +union all +values (1,2),(3,4); + +explain format=json +values (1,2),(3,4) +union all +select 1,2; + +explain format=json +select 1,2 +union +values (1,2),(3,4); + +explain format=json +values (1,2) +union all +values (1,2),(3,4); + +explain +select 1,2 +union all +values (3,4) +union all +values (1,2); + +explain format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); + +--echo # analyze query that uses VALUES structure(s): single VALUES structure + +analyze +values (1,2); + +analyze format=json +values (1,2); + +--echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s) + +analyze +select 1,2 +union +values (1,2),(3,4); + +analyze +values (1,2),(3,4) +union +select 1,2; + +analyze +values (5,6) +union +values (1,2),(3,4); + +analyze format=json +select 1,2 +union +values (1,2),(3,4); + +analyze format=json +values (1,2),(3,4) +union +select 1,2; + +analyze format=json +values (5,6) +union +values (1,2),(3,4); + +analyze +select 1,2 +union +values (3,4) +union +values (1,2); + +analyze format=json +select 1,2 +union +values (3,4) +union +values (1,2); + +--echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +analyze +select 1,2 +union +values (1,2),(3,4); + +analyze +values (1,2),(3,4) +union all +select 1,2; + +analyze +values (1,2) +union all +values (1,2),(3,4); + +analyze format=json +values (1,2),(3,4) +union all +select 1,2; + +analyze format=json +select 1,2 +union +values (1,2),(3,4); + +analyze format=json +values (1,2) +union all +values (1,2),(3,4); + +analyze +select 1,2 +union all +values (3,4) +union all +values (1,2); + +analyze format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); + drop table t1;
\ No newline at end of file |