Queries where TVCs are used are processed successufully.

TVCs can be used separately, with UNION/UNION ALL, in derived tables,
in views and in common table expressions.

Tests corrected.
Test results added.

1 files changed, 244 insertions, 0 deletions

diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test
new file mode 100644
index 00000000000..4aba843bbac
--- /dev/null
+++ b/mysql-test/t/table_value_constr.test
@@ -0,0 +1,244 @@
+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 using 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 using 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 using 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 using 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 using 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 using 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 using 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 using 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 using 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 using VALUES structure(s)
+
+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 # Derived table using VALUES structure(s)
+
+select * from (values (1,2),(3,4)) as t2;
+
+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;
+
+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)
+
+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;
+
+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;
+
+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;
+
+drop table t1;
\ No newline at end of file