MDEV-15975 PL/SQL parser does not understand historical queries

Merging the following features from sql_yacc.yy to sql_yacc_ora.yy:

- system versioning
- column compression
- table value constructor
- spatial predicate WITHIN
- DELETE_DOMAIN_ID

8 files changed, 3243 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/column_compression.result b/mysql-test/suite/compat/oracle/r/column_compression.result
new file mode 100644
index 00000000000..eaa4de002a4
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/column_compression.result
@@ -0,0 +1,9 @@
+SET sql_mode=ORACLE;
+SET column_compression_zlib_wrap=true;
+CREATE TABLE t1 (a BLOB COMPRESSED);
+INSERT INTO t1 VALUES (REPEAT('a',10000));
+SELECT DATA_LENGTH<100 AS c FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';
+c
+1
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/gis.result b/mysql-test/suite/compat/oracle/r/gis.result
new file mode 100644
index 00000000000..ebd56a089ad
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/gis.result
@@ -0,0 +1,6 @@
+SELECT WITHIN(POINT(1,1), POINT(1,1));
+WITHIN(POINT(1,1), POINT(1,1))
+1
+SELECT WITHIN(POINT(1,1), POINT(0,0));
+WITHIN(POINT(1,1), POINT(0,0))
+0
diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result
new file mode 100644
index 00000000000..31dcecfdb18
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result
@@ -0,0 +1,2101 @@
+SET sql_mode=ORACLE;
+create table t1 (a int, b int);
+insert into t1 values (1,2),(4,6),(9,7),
+(1,1),(2,5),(7,8);
+# just VALUES
+values (1,2);
+1	2
+1	2
+values (1,2), (3,4), (5.6,0);
+1	2
+1.0	2
+3.0	4
+5.6	0
+values ('abc', 'def');
+abc	def
+abc	def
+# UNION that uses VALUES structure(s)
+select 1,2
+union
+values (1,2);
+1	2
+1	2
+values (1,2)
+union
+select 1,2;
+1	2
+1	2
+select 1,2
+union
+values (1,2),(3,4),(5,6),(7,8);
+1	2
+1	2
+3	4
+5	6
+7	8
+select 3,7
+union
+values (1,2),(3,4),(5,6);
+3	7
+3	7
+1	2
+3	4
+5	6
+select 3,7,4
+union
+values (1,2,5),(4,5,6);
+3	7	4
+3	7	4
+1	2	5
+4	5	6
+select 1,2
+union
+values (1,7),(3,6.5);
+1	2
+1	2.0
+1	7.0
+3	6.5
+select 1,2
+union
+values (1,2.0),(3,6);
+1	2
+1	2.0
+3	6.0
+select 1.8,2
+union
+values (1,2),(3,6);
+1.8	2
+1.8	2
+1.0	2
+3.0	6
+values (1,2.4),(3,6)
+union
+select 2.8,9;
+1	2.4
+1.0	2.4
+3.0	6.0
+2.8	9.0
+values (1,2),(3,4),(5,6),(7,8)
+union
+select 5,6;
+1	2
+1	2
+3	4
+5	6
+7	8
+select 'ab','cdf'
+union
+values ('al','zl'),('we','q');
+ab	cdf
+ab	cdf
+al	zl
+we	q
+values ('ab', 'cdf')
+union
+select 'ab','cdf';
+ab	cdf
+ab	cdf
+values (1,2)
+union
+values (1,2),(5,6);
+1	2
+1	2
+5	6
+values (1,2)
+union
+values (3,4),(5,6);
+1	2
+1	2
+3	4
+5	6
+values (1,2)
+union
+values (1,2)
+union values (4,5);
+1	2
+1	2
+4	5
+# UNION ALL that uses VALUES structure
+values (1,2),(3,4)
+union all
+select 5,6;
+1	2
+1	2
+3	4
+5	6
+values (1,2),(3,4)
+union all
+select 1,2;
+1	2
+1	2
+3	4
+1	2
+select 5,6
+union all
+values (1,2),(3,4);
+5	6
+5	6
+1	2
+3	4
+select 1,2
+union all
+values (1,2),(3,4);
+1	2
+1	2
+1	2
+3	4
+values (1,2)
+union all
+values (1,2),(5,6);
+1	2
+1	2
+1	2
+5	6
+values (1,2)
+union all
+values (3,4),(5,6);
+1	2
+1	2
+3	4
+5	6
+values (1,2)
+union all
+values (1,2)
+union all
+values (4,5);
+1	2
+1	2
+1	2
+4	5
+values (1,2)
+union all
+values (1,2)
+union values (1,2);
+1	2
+1	2
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+1	2
+# EXCEPT that uses VALUES structure(s)
+select 1,2
+except
+values (3,4),(5,6);
+1	2
+1	2
+select 1,2
+except
+values (1,2),(3,4);
+1	2
+values (1,2),(3,4)
+except
+select 5,6;
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+select 1,2;
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (5,6);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2);
+1	2
+3	4
+# INTERSECT that uses VALUES structure(s)
+select 1,2
+intersect
+values (3,4),(5,6);
+1	2
+select 1,2
+intersect
+values (1,2),(3,4);
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+select 5,6;
+1	2
+values (1,2),(3,4)
+intersect
+select 1,2;
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (5,6);
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2);
+1	2
+1	2
+# combination of different structures that uses VALUES structures : UNION + EXCEPT
+values (1,2),(3,4)
+except
+select 1,2
+union values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (3,4);
+1	2
+3	4
+values (1,2),(3,4)
+union
+values (1,2)
+except
+values (1,2);
+1	2
+3	4
+# 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);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (3,4);
+1	2
+3	4
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+except
+values (1,2);
+1	2
+3	4
+# combination of different structures that uses VALUES structures : UNION + INTERSECT
+values (1,2),(3,4)
+intersect
+select 1,2
+union
+values (1,2);
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (1,2);
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (3,4);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union
+values (1,2)
+intersect
+values (1,2);
+1	2
+1	2
+# 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);
+1	2
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (3,4);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+intersect
+values (1,2);
+1	2
+1	2
+# 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);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (3,4);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+3	4
+1	2
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+1	2
+# CTE that uses VALUES structure(s) : non-recursive CTE
+with t2 as
+(
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+select 1,2
+union
+values (1,2)
+)
+select * from t2;
+1	2
+1	2
+with t2 as
+(
+select 1,2
+union
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+values (1,2)
+union
+select 1,2
+)
+select * from t2;
+1	2
+1	2
+with t2 as
+(
+values (1,2),(3,4)
+union
+select 1,2
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+values (5,6)
+union
+values (1,2),(3,4)
+)
+select * from t2;
+5	6
+5	6
+1	2
+3	4
+with t2 as
+(
+values (1,2)
+union
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+select 1,2
+union all
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+1	2
+3	4
+with t2 as
+(
+values (1,2),(3,4)
+union all
+select 1,2
+)
+select * from t2;
+1	2
+1	2
+3	4
+1	2
+with t2 as
+(
+values (1,2)
+union all
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+1	2
+3	4
+# 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;
+a	b
+1	1
+1	2
+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;
+a	b
+1	1
+2	2
+2	1
+3	5
+# 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;
+a	b
+1	1
+3	4
+2	2
+2	1
+3	5
+# 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;
+a	b	st
+1	1	1
+1	2	2
+1	1	2
+1	2	3
+1	2	3
+1	1	3
+1	1	3
+# 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;
+n	f
+1	1
+2	1
+3	2
+4	6
+5	24
+6	120
+7	720
+8	5040
+9	40320
+10	362880
+# Derived table that uses VALUES structure(s) : singe VALUES structure
+select * from (values (1,2),(3,4)) as t2;
+1	2
+1	2
+3	4
+# Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
+select * from (select 1,2 union values (1,2)) as t2;
+1	2
+1	2
+select * from (select 1,2 union values (1,2),(3,4)) as t2;
+1	2
+1	2
+3	4
+select * from (values (1,2) union select 1,2) as t2;
+1	2
+1	2
+select * from (values (1,2),(3,4) union select 1,2) as t2;
+1	2
+1	2
+3	4
+select * from (values (5,6) union values (1,2),(3,4)) as t2;
+5	6
+5	6
+1	2
+3	4
+select * from (values (1,2) union values (1,2),(3,4)) as t2;
+1	2
+1	2
+3	4
+# 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;
+1	2
+1	2
+1	2
+3	4
+select * from (values (1,2),(3,4) union all select 1,2) as t2;
+1	2
+1	2
+3	4
+1	2
+select * from (values (1,2) union all values (1,2),(3,4)) as t2;
+1	2
+1	2
+1	2
+3	4
+# CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
+create view v1 as values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+# CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
+create view v1 as
+select 1,2
+union
+values (1,2);
+select * from v1;
+1	2
+1	2
+drop view v1;
+create view v1 as
+select 1,2
+union
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+values (1,2)
+union
+select 1,2;
+select * from v1;
+1	2
+1	2
+drop view v1;
+create view v1 as
+values (1,2),(3,4)
+union
+select 1,2;
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+values (5,6)
+union
+values (1,2),(3,4);
+select * from v1;
+5	6
+5	6
+1	2
+3	4
+drop view v1;
+# 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);
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+select 1,2
+union all
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+values (1,2),(3,4)
+union all
+select 1,2;
+select * from v1;
+1	2
+1	2
+3	4
+1	2
+drop view v1;
+create view v1 as
+values (1,2)
+union all
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+1	2
+3	4
+drop view v1;
+# IN-subquery with VALUES structure(s) : simple case
+select * from t1
+where a in (values (1));
+a	b
+1	2
+1	1
+select * from t1
+where a in (select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a in (values (1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+explain extended select * from t1
+where a in (select * from (values (1)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+# IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
+select * from t1
+where a in (values (1) union select 2);
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a in (select * from (values (1)) as tvc_0 union 
+select 2);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1
+where a in (values (1) union select 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1" union /* select#3 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2))))
+explain extended select * from t1
+where a in (select * from (values (1)) as tvc_0 union 
+select 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1" union /* select#4 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2))))
+# IN-subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a in (select 2 union values (1));
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a in (select 2 union 
+select * from (values (1)) tvc_0);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1
+where a in (select 2 union values (1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1")))
+explain extended select * from t1
+where a in (select 2 union 
+select * from (values (1)) tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1")))
+# IN-subquery with VALUES structure(s) : UNION ALL
+select * from t1
+where a in (values (1) union all select b from t1);
+a	b
+1	2
+1	1
+2	5
+7	8
+select * from t1
+where a in (select * from (values (1)) as tvc_0 union all 
+select b from t1);
+a	b
+1	2
+1	1
+2	5
+7	8
+explain extended select * from t1
+where a in (values (1) union all select b from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1" union all /* select#3 */ select "test"."t1"."b" from "test"."t1" where <cache>("test"."t1"."a") = "test"."t1"."b")))
+explain extended select * from t1
+where a in (select * from (values (1)) as tvc_0 union all 
+select b from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1" union all /* select#4 */ select "test"."t1"."b" from "test"."t1" where <cache>("test"."t1"."a") = "test"."t1"."b")))
+# NOT IN subquery with VALUES structure(s) : simple case
+select * from t1 
+where a not in (values (1),(2));
+a	b
+4	6
+9	7
+7	8
+select * from t1 
+where a not in (select * from (values (1),(2)) as tvc_0);
+a	b
+4	6
+9	7
+7	8
+explain extended select * from t1 
+where a not in (values (1),(2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (/* select#3 */ select "tvc_0"."1" from (values (1),(2)) "tvc_0" ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key where "test"."t1"."a" = "<subquery3>"."1"))))
+explain extended select * from t1 
+where a not in (select * from (values (1),(2)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a","test"."t1"."a" in ( <materialize> (/* select#2 */ select "tvc_0"."1" from (values (1),(2)) "tvc_0" ), <primary_index_lookup>("test"."t1"."a" in <temporary table> on distinct_key where "test"."t1"."a" = "<subquery2>"."1"))))
+# NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place
+select * from t1
+where a not in (values (1) union select 2);
+a	b
+4	6
+9	7
+7	8
+select * from t1
+where a not in (select * from (values (1)) as tvc_0 union 
+select 2);
+a	b
+4	6
+9	7
+7	8
+explain extended select * from t1
+where a not in (values (1) union select 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1") union /* select#3 */ select 2 having trigcond(<cache>("test"."t1"."a") = <ref_null_helper>(2)))))
+explain extended select * from t1
+where a not in (select * from (values (1)) as tvc_0 union 
+select 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1") union /* select#4 */ select 2 having trigcond(<cache>("test"."t1"."a") = <ref_null_helper>(2)))))
+# NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a not in (select 2 union values (1));
+a	b
+4	6
+9	7
+7	8
+select * from t1
+where a not in (select 2 union 
+select * from (values (1)) as tvc_0);
+a	b
+4	6
+9	7
+7	8
+explain extended select * from t1
+where a not in (select 2 union values (1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 2 having trigcond(<cache>("test"."t1"."a") = <ref_null_helper>(2)) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1"))))
+explain extended select * from t1
+where a not in (select 2 union 
+select * from (values (1)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where !<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 2 having trigcond(<cache>("test"."t1"."a") = <ref_null_helper>(2)) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") = "tvc_0"."1"))))
+# ANY-subquery with VALUES structure(s) : simple case
+select * from t1 
+where a = any (values (1),(2));
+a	b
+1	2
+1	1
+2	5
+select * from t1 
+where a = any (select * from (values (1),(2)) as tvc_0);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 
+where a = any (values (1),(2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+explain extended select * from t1 
+where a = any (select * from (values (1),(2)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where "test"."t1"."a" = "tvc_0"."1"
+# ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
+select * from t1 
+where a = any (values (1) union select 2);
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a = any (select * from (values (1)) as tvc_0 union 
+select 2);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 
+where a = any (values (1) union select 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1" union /* select#3 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2))))
+explain extended select * from t1
+where a = any (select * from (values (1)) as tvc_0 union 
+select 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1" union /* select#4 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2))))
+# ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a = any (select 2 union values (1));
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a = any (select 2 union 
+select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1
+where a = any (select 2 union values (1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1")))
+explain extended select * from t1
+where a = any (select 2 union 
+select * from (values (1)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 2 having <cache>("test"."t1"."a") = <ref_null_helper>(2) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1")))
+# ALL-subquery with VALUES structure(s) : simple case
+select * from t1 
+where a = all (values (1));
+a	b
+1	2
+1	1
+select * from t1 
+where a = all (select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+explain extended select * from t1 
+where a = all (values (1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") <> "tvc_0"."1")))))
+explain extended select * from t1 
+where a = all (select * from (values (1)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") <> "tvc_0"."1")))))
+# ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place
+select * from t1 
+where a = all (values (1) union select 1);
+a	b
+1	2
+1	1
+select * from t1
+where a = all (select * from (values (1)) as tvc_0 union 
+select 1);
+a	b
+1	2
+1	1
+explain extended select * from t1 
+where a = all (values (1) union select 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union4,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") <> "tvc_0"."1") union /* select#3 */ select 1 having trigcond(<cache>("test"."t1"."a") <> <ref_null_helper>(1))))))
+explain extended select * from t1
+where a = all (select * from (values (1)) as tvc_0 union 
+select 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <not>(<expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(<cache>("test"."t1"."a") <> "tvc_0"."1") union /* select#4 */ select 1 having trigcond(<cache>("test"."t1"."a") <> <ref_null_helper>(1))))))
+# ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a = any (select 1 union values (1));
+a	b
+1	2
+1	1
+select * from t1
+where a = any (select 1 union 
+select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a = any (select 1 union values (1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 1 having <cache>("test"."t1"."a") = <ref_null_helper>(1) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1")))
+explain extended select * from t1
+where a = any (select 1 union 
+select * from (values (1)) as tvc_0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" where <expr_cache><"test"."t1"."a">(<in_optimizer>("test"."t1"."a",<exists>(/* select#2 */ select 1 having <cache>("test"."t1"."a") = <ref_null_helper>(1) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where <cache>("test"."t1"."a") = "tvc_0"."1")))
+# prepare statement that uses VALUES structure(s): single VALUES structure
+prepare stmt1 from '
+values (1,2);
+';
+execute stmt1;
+1	2
+1	2
+execute stmt1;
+1	2
+1	2
+deallocate prepare stmt1;
+# 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;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from '
+  values (1,2),(3,4)
+  union
+  select 1,2;
+';
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from '
+  select 1,2
+  union
+  values (3,4)
+  union
+  values (1,2);
+';
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from '
+  values (5,6)
+  union
+  values (1,2),(3,4);
+';
+execute stmt1;
+5	6
+5	6
+1	2
+3	4
+execute stmt1;
+5	6
+5	6
+1	2
+3	4
+deallocate prepare stmt1;
+# 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;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from '
+  values (1,2),(3,4)
+  union all
+  select 1,2;
+';
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+deallocate prepare stmt1;
+prepare stmt1 from '
+  select 1,2
+  union all
+  values (3,4)
+  union all
+  values (1,2);
+';
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+deallocate prepare stmt1;
+prepare stmt1 from '
+  values (1,2)
+  union all
+  values (1,2),(3,4);
+';
+execute stmt1;
+1	2
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+# explain query that uses VALUES structure(s): single VALUES structure
+explain
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<unit1>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# explain query that uses VALUES structure(s): UNION with VALUES structure(s)
+explain
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (1,2),(3,4)
+union
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (5,6)
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (1,2),(3,4)
+union
+select 1,2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (5,6)
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+explain
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (1,2),(3,4)
+union all
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain
+values (1,2)
+union all
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): single VALUES structure
+analyze
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<unit1>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (1,2),(3,4)
+union
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (5,6)
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	3.00	NULL	NULL	
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (1,2),(3,4)
+union
+select 1,2;
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (5,6)
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 3,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (1,2),(3,4)
+union all
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze
+values (1,2)
+union all
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# different number of values in TVC
+values (1,2),(3,4,5);
+ERROR HY000: The used table value constructor has a different number of values
+# illegal parameter data types in TVC
+values (1,point(1,1)),(1,1);
+ERROR HY000: Illegal parameter data types geometry and int for operation 'TABLE VALUE CONSTRUCTOR'
+values (1,point(1,1)+1);
+ERROR HY000: Illegal parameter data types geometry and int for operation '+'
+# field reference in TVC
+select * from (values (1), (b), (2)) as new_tvc;
+ERROR HY000: Field reference 'b' can't be used in table value constructor
+select * from (values (1), (t1.b), (2)) as new_tvc;
+ERROR HY000: Field reference 't1.b' can't be used in table value constructor
+drop table t1;
+#
+# MDEV-15940: cursor over TVC
+#
+DECLARE
+v INT;
+CURSOR cur IS VALUES(7);
+BEGIN
+OPEN cur;
+FETCH cur INTO v;
+SELECT v;
+END;
+|
+v
+7
+DECLARE
+v INT DEFAULT 0;
+BEGIN
+FOR a IN (VALUES (7)) LOOP
+SET v = v + 1;
+END LOOP;
+SELECT v;
+END;
+|
+v
+1
+#
+# MDEV-16038: empty row in TVC
+#
+with t as (values (),()) select 1 from t;
+ERROR HY000: Row with no elements is not allowed in table value constructor in this context
diff --git a/mysql-test/suite/compat/oracle/r/versioning.result b/mysql-test/suite/compat/oracle/r/versioning.result
new file mode 100644
index 00000000000..ebedcf0f462
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/versioning.result
@@ -0,0 +1,16 @@
+SET sql_mode=ORACLE;
+#
+# MDEV-15975 PL/SQL parser does not understand historical queries
+#
+CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
+INSERT INTO t1 VALUES (10);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (20);
+SELECT * FROM t1 FOR SYSTEM_TIME ALL;
+a
+10
+20
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (NOW()+INTERVAL 10 YEAR);
+a
+20
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/column_compression.test b/mysql-test/suite/compat/oracle/t/column_compression.test
new file mode 100644
index 00000000000..5544ff6c0b7
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/column_compression.test
@@ -0,0 +1,11 @@
+--source include/have_innodb.inc
+--source include/have_csv.inc
+
+SET sql_mode=ORACLE;
+
+SET column_compression_zlib_wrap=true;
+CREATE TABLE t1 (a BLOB COMPRESSED);
+INSERT INTO t1 VALUES (REPEAT('a',10000));
+SELECT DATA_LENGTH<100 AS c FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='test';
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/gis.test b/mysql-test/suite/compat/oracle/t/gis.test
new file mode 100644
index 00000000000..a684563390b
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/gis.test
@@ -0,0 +1,4 @@
+-- source include/have_geometry.inc
+
+SELECT WITHIN(POINT(1,1), POINT(1,1));
+SELECT WITHIN(POINT(1,1), POINT(0,0));
diff --git a/mysql-test/suite/compat/oracle/t/table_value_constr.test b/mysql-test/suite/compat/oracle/t/table_value_constr.test
new file mode 100644
index 00000000000..66519e93a36
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/table_value_constr.test
@@ -0,0 +1,1083 @@
+SET sql_mode=ORACLE;
+
+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;
+
+--echo #
+--echo # MDEV-15940: cursor over TVC
+--echo #
+
+DELIMITER |;
+
+DECLARE
+  v INT;
+  CURSOR cur IS VALUES(7);
+BEGIN
+  OPEN cur;
+  FETCH cur INTO v;
+  SELECT v;
+END;
+|
+
+DECLARE
+  v INT DEFAULT 0;
+BEGIN
+  FOR a IN (VALUES (7)) LOOP
+    SET v = v + 1;
+  END LOOP;
+  SELECT v;
+END;
+|
+
+DELIMITER ;|
+
+--echo #
+--echo # MDEV-16038: empty row in TVC
+--echo #
+
+--error ER_EMPTY_ROW_IN_TVC
+with t as (values (),()) select 1 from t;
diff --git a/mysql-test/suite/compat/oracle/t/versioning.test b/mysql-test/suite/compat/oracle/t/versioning.test
new file mode 100644
index 00000000000..d70058c56e4
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/versioning.test
@@ -0,0 +1,13 @@
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # MDEV-15975 PL/SQL parser does not understand historical queries
+--echo #
+
+CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
+INSERT INTO t1 VALUES (10);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (20);
+SELECT * FROM t1 FOR SYSTEM_TIME ALL;
+SELECT * FROM t1 FOR SYSTEM_TIME AS OF (NOW()+INTERVAL 10 YEAR);
+DROP TABLE t1;