From 2a33d248e0bd910ec10a2bb68e47f17b47e3a980 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 18 May 2018 15:48:25 +0400 Subject: 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 --- .../compat/oracle/r/column_compression.result | 9 + mysql-test/suite/compat/oracle/r/gis.result | 6 + .../compat/oracle/r/table_value_constr.result | 2101 ++++++++++++++++++++ mysql-test/suite/compat/oracle/r/versioning.result | 16 + .../suite/compat/oracle/t/column_compression.test | 11 + mysql-test/suite/compat/oracle/t/gis.test | 4 + .../suite/compat/oracle/t/table_value_constr.test | 1083 ++++++++++ mysql-test/suite/compat/oracle/t/versioning.test | 13 + 8 files changed, 3243 insertions(+) create mode 100644 mysql-test/suite/compat/oracle/r/column_compression.result create mode 100644 mysql-test/suite/compat/oracle/r/gis.result create mode 100644 mysql-test/suite/compat/oracle/r/table_value_constr.result create mode 100644 mysql-test/suite/compat/oracle/r/versioning.result create mode 100644 mysql-test/suite/compat/oracle/t/column_compression.test create mode 100644 mysql-test/suite/compat/oracle/t/gis.test create mode 100644 mysql-test/suite/compat/oracle/t/table_value_constr.test create mode 100644 mysql-test/suite/compat/oracle/t/versioning.test (limited to 'mysql-test') 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 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 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 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 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 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 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("test"."t1"."a") = "tvc_0"."1" union /* select#3 */ select 2 having ("test"."t1"."a") = (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 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 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("test"."t1"."a") = "tvc_0"."1" union /* select#4 */ select 2 having ("test"."t1"."a") = (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 ref key0 key0 4 func 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 2 having ("test"."t1"."a") = (2) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("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 ref key0 key0 4 func 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 2 having ("test"."t1"."a") = (2) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("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 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("test"."t1"."a") = "tvc_0"."1" union all /* select#3 */ select "test"."t1"."b" from "test"."t1" where ("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 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("test"."t1"."a") = "tvc_0"."1" union all /* select#4 */ select "test"."t1"."b" from "test"."t1" where ("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 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 !<"test"."t1"."a">(("test"."t1"."a","test"."t1"."a" in ( (/* select#3 */ select "tvc_0"."1" from (values (1),(2)) "tvc_0" ), ("test"."t1"."a" in on distinct_key where "test"."t1"."a" = ""."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 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 !<"test"."t1"."a">(("test"."t1"."a","test"."t1"."a" in ( (/* select#2 */ select "tvc_0"."1" from (values (1),(2)) "tvc_0" ), ("test"."t1"."a" in on distinct_key where "test"."t1"."a" = ""."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 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 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 !<"test"."t1"."a">(("test"."t1"."a",(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("test"."t1"."a") = "tvc_0"."1") union /* select#3 */ select 2 having trigcond(("test"."t1"."a") = (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 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 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 !<"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("test"."t1"."a") = "tvc_0"."1") union /* select#4 */ select 2 having trigcond(("test"."t1"."a") = (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 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 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 !<"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 2 having trigcond(("test"."t1"."a") = (2)) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("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 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 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 !<"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 2 having trigcond(("test"."t1"."a") = (2)) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("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 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 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 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 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 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 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("test"."t1"."a") = "tvc_0"."1" union /* select#3 */ select 2 having ("test"."t1"."a") = (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 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 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("test"."t1"."a") = "tvc_0"."1" union /* select#4 */ select 2 having ("test"."t1"."a") = (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 ref key0 key0 4 func 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 2 having ("test"."t1"."a") = (2) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("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 ref key0 key0 4 func 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 2 having ("test"."t1"."a") = (2) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("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 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 (<"test"."t1"."a">(("test"."t1"."a",(/* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("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 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 (<"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("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 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 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 (<"test"."t1"."a">(("test"."t1"."a",(/* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("test"."t1"."a") <> "tvc_0"."1") union /* select#3 */ select 1 having trigcond(("test"."t1"."a") <> (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 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 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 (<"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select "tvc_0"."1" from (values (1)) "tvc_0" where trigcond(("test"."t1"."a") <> "tvc_0"."1") union /* select#4 */ select 1 having trigcond(("test"."t1"."a") <> (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 ref key0 key0 4 func 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 1 having ("test"."t1"."a") = (1) union /* select#4 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("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 ref key0 key0 4 func 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT 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 <"test"."t1"."a">(("test"."t1"."a",(/* select#2 */ select 1 having ("test"."t1"."a") = (1) union /* select#3 */ select "tvc_0"."1" from (values (1)) "tvc_0" where ("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": "", + "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 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 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 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": "", + "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": "", + "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": "", + "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 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": "", + "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 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": "", + "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": "", + "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": "", + "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": "", + "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": "", + "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 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 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 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": "", + "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": "", + "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": "", + "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 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": "", + "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 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": "", + "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": "", + "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": "", + "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": "", + "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; -- cgit v1.2.1