Create 'main' test directory and move 't' and 'r' there

1 files changed, 0 insertions, 1046 deletions

diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test
deleted file mode 100644
index 578f8943fbb..00000000000
--- a/mysql-test/t/table_value_constr.test
+++ /dev/null
@@ -1,1046 +0,0 @@
-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;