diff options
Diffstat (limited to 'mysql-test/main/table_value_constr.test')
| -rw-r--r-- | mysql-test/main/table_value_constr.test | 1046 |
1 files changed, 1046 insertions, 0 deletions
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test new file mode 100644 index 00000000000..578f8943fbb --- /dev/null +++ b/mysql-test/main/table_value_constr.test @@ -0,0 +1,1046 @@ +create table t1 (a int, b int); + +insert into t1 values (1,2),(4,6),(9,7), + (1,1),(2,5),(7,8); + +--echo # just VALUES + +values (1,2); + +values (1,2), (3,4), (5.6,0); + +values ("abc", "def"); + +--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"; + +values (1,2) +union +values (1,2),(5,6); + +values (1,2) +union +values (3,4),(5,6); + +values (1,2) +union +values (1,2) +union values (4,5); + +--echo # UNION ALL that uses VALUES structure + +values (1,2),(3,4) +union all +select 5,6; + +values (1,2),(3,4) +union all +select 1,2; + +select 5,6 +union all +values (1,2),(3,4); + +select 1,2 +union all +values (1,2),(3,4); + +values (1,2) +union all +values (1,2),(5,6); + +values (1,2) +union all +values (3,4),(5,6); + +values (1,2) +union all +values (1,2) +union all +values (4,5); + +values (1,2) +union all +values (1,2) +union values (1,2); + +values (1,2) +union +values (1,2) +union all +values (1,2); + +--echo # EXCEPT that uses VALUES structure(s) + +select 1,2 +except +values (3,4),(5,6); + +select 1,2 +except +values (1,2),(3,4); + +values (1,2),(3,4) +except +select 5,6; + +values (1,2),(3,4) +except +select 1,2; + +values (1,2),(3,4) +except +values (5,6); + +values (1,2),(3,4) +except +values (1,2); + +--echo # INTERSECT that uses VALUES structure(s) + +select 1,2 +intersect +values (3,4),(5,6); + +select 1,2 +intersect +values (1,2),(3,4); + +values (1,2),(3,4) +intersect +select 5,6; + +values (1,2),(3,4) +intersect +select 1,2; + +values (1,2),(3,4) +intersect +values (5,6); + +values (1,2),(3,4) +intersect +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION + EXCEPT + +values (1,2),(3,4) +except +select 1,2 +union values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union +values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union +values (3,4); + +values (1,2),(3,4) +union +values (1,2) +except +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT + +values (1,2),(3,4) +except +select 1,2 +union all +values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union all +values (1,2); + +values (1,2),(3,4) +except +values (1,2) +union all +values (3,4); + +values (1,2),(3,4) +union all +values (1,2) +except +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION + INTERSECT + +values (1,2),(3,4) +intersect +select 1,2 +union +values (1,2); + +values (1,2),(3,4) +intersect +values (1,2) +union +values (1,2); + +values (1,2),(3,4) +intersect +values (1,2) +union +values (3,4); + +values (1,2),(3,4) +union +values (1,2) +intersect +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT + +values (1,2),(3,4) +intersect +select 1,2 +union all +values (1,2); + +values (1,2),(3,4) +intersect +values (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) +union all +values (1,2) +intersect +values (1,2); + +--echo # combination of different structures that uses VALUES structures : UNION + UNION ALL + +values (1,2),(3,4) +union all +select 1,2 +union +values (1,2); + +values (1,2),(3,4) +union all +values (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) +union +values (1,2) +union all +values (1,2); + +values (1,2) +union +values (1,2) +union all +values (1,2); + +--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; + +select * from (values (1,2) union select 1,2) as t2; + +select * from (values (1,2),(3,4) union select 1,2) as t2; + +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 that uses VALUES structure(s) : singe VALUES structure + +let $drop_view= drop view v1; +let $select_view= select * from v1; + +create view v1 as values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +--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); + +eval $select_view; +eval $drop_view; + +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; + +eval $select_view; +eval $drop_view; + +create view v1 as + values (5,6) + union + values (1,2),(3,4); + +eval $select_view; +eval $drop_view; + +--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); + +eval $select_view; +eval $drop_view; + +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); + +eval $select_view; +eval $drop_view; + +--echo # IN-subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a in (values (1)); +let $subst_query= +select * from t1 +where a in (select * from (values (1)) as tvc_0); +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a in (values (1) union select 2); +let $subst_query= +select * from t1 +where a in (select * from (values (1)) as tvc_0 union + select 2); +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a in (select 2 union values (1)); +let $subst_query= +select * from t1 +where a in (select 2 union + select * from (values (1)) tvc_0); +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # IN-subquery with VALUES structure(s) : UNION ALL +let $query= +select * from t1 +where a in (values (1) union all select b from t1); +let $subst_query= +select * from t1 +where a in (select * from (values (1)) as tvc_0 union all + select b from t1); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # NOT IN subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a not in (values (1),(2)); +let $subst_query= +select * from t1 +where a not in (select * from (values (1),(2)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a not in (values (1) union select 2); +let $subst_query= +select * from t1 +where a not in (select * from (values (1)) as tvc_0 union + select 2); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a not in (select 2 union values (1)); +let $subst_query= +select * from t1 +where a not in (select 2 union + select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ANY-subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a = any (values (1),(2)); +let $subst_query= +select * from t1 +where a = any (select * from (values (1),(2)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a = any (values (1) union select 2); +let $subst_query= +select * from t1 +where a = any (select * from (values (1)) as tvc_0 union + select 2); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a = any (select 2 union values (1)); +let $subst_query= +select * from t1 +where a = any (select 2 union + select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ALL-subquery with VALUES structure(s) : simple case +let $query= +select * from t1 +where a = all (values (1)); +let $subst_query= +select * from t1 +where a = all (select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place +let $query= +select * from t1 +where a = all (values (1) union select 1); +let $subst_query= +select * from t1 +where a = all (select * from (values (1)) as tvc_0 union + select 1); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place +let $query= +select * from t1 +where a = any (select 1 union values (1)); +let $subst_query= +select * from t1 +where a = any (select 1 union + select * from (values (1)) as tvc_0); + +eval $query; +eval $subst_query; +eval explain extended $query; +eval explain extended $subst_query; + +--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); + +--echo # different number of values in TVC +--error ER_WRONG_NUMBER_OF_VALUES_IN_TVC +values (1,2),(3,4,5); + +--echo # illegal parameter data types in TVC +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +values (1,point(1,1)),(1,1); +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +values (1,point(1,1)+1); + +--echo # field reference in TVC +--error ER_FIELD_REFERENCE_IN_TVC +select * from (values (1), (b), (2)) as new_tvc; +--error ER_FIELD_REFERENCE_IN_TVC +select * from (values (1), (t1.b), (2)) as new_tvc; + +drop table t1; |