diff options
| author | Alexander Barkov <bar@mariadb.com> | 2018-05-18 15:48:25 +0400 |
|---|---|---|
| committer | Alexander Barkov <bar@mariadb.com> | 2018-05-18 15:48:25 +0400 |
| commit | 2a33d248e0bd910ec10a2bb68e47f17b47e3a980 (patch) | |
| tree | 81dd25d25b493ef0ad9a1a44a8998a240b2174ad | |
| parent | 395c8ca708c15e7f4b8dca5c3f5246d03eb557af (diff) | |
| download | mariadb-git-2a33d248e0bd910ec10a2bb68e47f17b47e3a980.tar.gz | |
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
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/column_compression.result | 9 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/gis.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/table_value_constr.result | 2101 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/versioning.result | 16 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/column_compression.test | 11 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/gis.test | 4 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/table_value_constr.test | 1083 | ||||
| -rw-r--r-- | mysql-test/suite/compat/oracle/t/versioning.test | 13 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 169 | ||||
| -rw-r--r-- | sql/sql_lex.h | 18 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 246 | ||||
| -rw-r--r-- | sql/sql_yacc_ora.yy | 647 |
12 files changed, 4010 insertions, 313 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; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 18373ed4449..2e54177dcc2 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -31,6 +31,7 @@ #include "sql_select.h" #include "sql_cte.h" #include "sql_signal.h" +#include "sql_partition.h" void LEX::parse_error(uint err_number) @@ -7977,3 +7978,171 @@ bool Lex_ident_sys_st::convert(THD *thd, length= tmp.length; return false; } + + +bool Lex_ident_sys_st::to_size_number(THD *thd, ulonglong *to) const +{ + ulonglong number; + uint text_shift_number= 0; + longlong prefix_number; + const char *start_ptr= str; + size_t str_len= length; + const char *end_ptr= start_ptr + str_len; + int error; + prefix_number= my_strtoll10(start_ptr, (char**) &end_ptr, &error); + if (likely((start_ptr + str_len - 1) == end_ptr)) + { + switch (end_ptr[0]) + { + case 'g': + case 'G': text_shift_number+=30; break; + case 'm': + case 'M': text_shift_number+=20; break; + case 'k': + case 'K': text_shift_number+=10; break; + default: + my_error(ER_WRONG_SIZE_NUMBER, MYF(0)); + return true; + } + if (unlikely(prefix_number >> 31)) + { + my_error(ER_SIZE_OVERFLOW_ERROR, MYF(0)); + return true; + } + number= prefix_number << text_shift_number; + } + else + { + my_error(ER_WRONG_SIZE_NUMBER, MYF(0)); + return true; + } + *to= number; + return false; +} + + +bool LEX::part_values_current(THD *thd) +{ + partition_element *elem= part_info->curr_part_elem; + if (!is_partition_management()) + { + if (unlikely(part_info->part_type != VERSIONING_PARTITION)) + { + my_error(ER_PARTITION_WRONG_TYPE, MYF(0), "SYSTEM_TIME"); + return true; + } + } + else + { + DBUG_ASSERT(create_last_non_select_table); + DBUG_ASSERT(create_last_non_select_table->table_name.str); + // FIXME: other ALTER commands? + my_error(ER_VERS_WRONG_PARTS, MYF(0), + create_last_non_select_table->table_name.str); + return true; + } + elem->type(partition_element::CURRENT); + DBUG_ASSERT(part_info->vers_info); + part_info->vers_info->now_part= elem; + if (unlikely(part_info->init_column_part(thd))) + return true; + return false; +} + + +bool LEX::part_values_history(THD *thd) +{ + partition_element *elem= part_info->curr_part_elem; + if (!is_partition_management()) + { + if (unlikely(part_info->part_type != VERSIONING_PARTITION)) + { + my_error(ER_PARTITION_WRONG_TYPE, MYF(0), "SYSTEM_TIME"); + return true; + } + } + else + { + part_info->vers_init_info(thd); + elem->id= UINT_MAX32; + } + DBUG_ASSERT(part_info->vers_info); + if (unlikely(part_info->vers_info->now_part)) + { + DBUG_ASSERT(create_last_non_select_table); + DBUG_ASSERT(create_last_non_select_table->table_name.str); + my_error(ER_VERS_WRONG_PARTS, MYF(0), + create_last_non_select_table->table_name.str); + return true; + } + elem->type(partition_element::HISTORY); + if (unlikely(part_info->init_column_part(thd))) + return true; + return false; +} + + +bool LEX::last_field_generated_always_as_row_start_or_end(Lex_ident *p, + const char *type, + uint flag) +{ + if (unlikely(p->str)) + { + my_error(ER_VERS_DUPLICATE_ROW_START_END, MYF(0), type, + last_field->field_name.str); + return true; + } + last_field->flags|= (flag | NOT_NULL_FLAG); + DBUG_ASSERT(p); + *p= last_field->field_name; + return false; +} + + + +bool LEX::last_field_generated_always_as_row_start() +{ + Vers_parse_info &info= vers_get_info(); + Lex_ident *p= &info.as_row.start; + return last_field_generated_always_as_row_start_or_end(p, "START", + VERS_SYS_START_FLAG); +} + + +bool LEX::last_field_generated_always_as_row_end() +{ + Vers_parse_info &info= vers_get_info(); + Lex_ident *p= &info.as_row.end; + return last_field_generated_always_as_row_start_or_end(p, "END", + VERS_SYS_END_FLAG); +} + + +bool LEX::tvc_finalize() +{ + mysql_init_select(this); + if (unlikely(!(current_select->tvc= + new (thd->mem_root) + table_value_constr(many_values, + current_select, + current_select->options)))) + return true; + many_values.empty(); + return false; +} + + +bool LEX::tvc_finalize_derived() +{ + derived_tables|= DERIVED_SUBQUERY; + if (unlikely(!expr_allows_subselect || sql_command == (int)SQLCOM_PURGE)) + { + thd->parse_error(); + return true; + } + if (current_select->linkage == GLOBAL_OPTIONS_TYPE || + unlikely(mysql_new_select(this, 1, NULL))) + return true; + current_select->linkage= DERIVED_TABLE_TYPE; + return tvc_finalize(); +} diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f1be02ef1f1..2369568e8a9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -143,6 +143,7 @@ public: bool convert(THD *thd, const LEX_CSTRING *str, CHARSET_INFO *cs); bool copy_or_convert(THD *thd, const Lex_ident_cli_st *str, CHARSET_INFO *cs); bool is_null() const { return str == NULL; } + bool to_size_number(THD *thd, ulonglong *to) const; }; @@ -3262,7 +3263,10 @@ public: void restore_backup_query_tables_list(Query_tables_list *backup); bool table_or_sp_used(); + bool is_partition_management() const; + bool part_values_current(THD *thd); + bool part_values_history(THD *thd); /** @brief check if the statement is a single-level join @@ -3294,6 +3298,11 @@ public: void init_last_field(Column_definition *field, const LEX_CSTRING *name, const CHARSET_INFO *cs); + bool last_field_generated_always_as_row_start_or_end(Lex_ident *p, + const char *type, + uint flags); + bool last_field_generated_always_as_row_start(); + bool last_field_generated_always_as_row_end(); bool set_bincmp(CHARSET_INFO *cs, bool bin); bool get_dynamic_sql_string(LEX_CSTRING *dst, String *buffer); @@ -3919,6 +3928,15 @@ public: } return false; } + + void tvc_start() + { + field_list.empty(); + many_values.empty(); + insert_list= 0; + } + bool tvc_finalize(); + bool tvc_finalize_derived(); }; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e9b5d3b5f8f..4e0d990d85c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1848,7 +1848,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); opt_default_time_precision case_stmt_body opt_bin_mod opt_for_system_time_clause opt_if_exists_table_element opt_if_not_exists_table_element - opt_recursive opt_format_xid + opt_recursive opt_format_xid %type <object_ddl_options> create_or_replace @@ -2082,9 +2082,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); vcol_opt_attribute_list vcol_attribute opt_serial_attribute opt_serial_attribute_list serial_attribute explainable_command - opt_lock_wait_timeout + opt_lock_wait_timeout opt_delete_gtid_domain - asrow_attribute + asrow_attribute END_OF_INPUT %type <NONE> call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt @@ -2105,7 +2105,6 @@ END_OF_INPUT %type <num> sp_decl_idents sp_decl_idents_init_vars %type <num> sp_handler_type sp_hcond_list -%type <num> start_or_end %type <spcondvalue> sp_cond sp_hcond sqlstate signal_value opt_signal_value %type <spblock> sp_decls sp_decl sp_decl_body sp_decl_variable_list %type <spname> sp_name @@ -2136,6 +2135,7 @@ END_OF_INPUT %type <spvar_definition> row_field_name row_field_definition %type <spvar_definition_list> row_field_definition_list row_type_body + %type <NONE> opt_window_clause window_def_list window_def window_spec %type <lex_str_ptr> window_name %type <NONE> opt_window_ref opt_window_frame_clause @@ -3613,7 +3613,8 @@ opt_parenthesized_cursor_formal_parameters: sp_cursor_stmt_lex: { DBUG_ASSERT(thd->lex->sphead); - if (unlikely(!($$= new (thd->mem_root) sp_lex_cursor(thd, thd->lex)))) + if (unlikely(!($$= new (thd->mem_root) + sp_lex_cursor(thd, thd->lex)))) MYSQL_YYABORT; } ; @@ -5138,34 +5139,8 @@ size_number: real_ulonglong_num { $$= $1;} | IDENT_sys { - ulonglong number; - uint text_shift_number= 0; - longlong prefix_number; - const char *start_ptr= $1.str; - size_t str_len= $1.length; - const char *end_ptr= start_ptr + str_len; - int error; - prefix_number= my_strtoll10(start_ptr, (char**) &end_ptr, &error); - if (likely((start_ptr + str_len - 1) == end_ptr)) - { - switch (end_ptr[0]) - { - case 'g': - case 'G': text_shift_number+=30; break; - case 'm': - case 'M': text_shift_number+=20; break; - case 'k': - case 'K': text_shift_number+=10; break; - default: - my_yyabort_error((ER_WRONG_SIZE_NUMBER, MYF(0))); - } - if (unlikely(prefix_number >> 31)) - my_yyabort_error((ER_SIZE_OVERFLOW_ERROR, MYF(0))); - number= prefix_number << text_shift_number; - } - else - my_yyabort_error((ER_WRONG_SIZE_NUMBER, MYF(0))); - $$= number; + if ($1.to_size_number(thd, &$$)) + MYSQL_YYABORT; } ; @@ -5631,55 +5606,12 @@ opt_part_values: part_values_in {} | CURRENT_SYM { - LEX *lex= Lex; - partition_info *part_info= lex->part_info; - partition_element *elem= part_info->curr_part_elem; - if (! lex->is_partition_management()) - { - if (unlikely(part_info->part_type != VERSIONING_PARTITION)) - my_yyabort_error((ER_PARTITION_WRONG_TYPE, MYF(0), "SYSTEM_TIME")); - } - else - { - DBUG_ASSERT(Lex->create_last_non_select_table); - DBUG_ASSERT(Lex->create_last_non_select_table->table_name.str); - // FIXME: other ALTER commands? - my_yyabort_error((ER_VERS_WRONG_PARTS, MYF(0), - Lex->create_last_non_select_table-> - table_name.str)); - } - elem->type(partition_element::CURRENT); - DBUG_ASSERT(part_info->vers_info); - part_info->vers_info->now_part= elem; - if (unlikely(part_info->init_column_part(thd))) + if (Lex->part_values_current(thd)) MYSQL_YYABORT; } | HISTORY_SYM { - LEX *lex= Lex; - partition_info *part_info= lex->part_info; - partition_element *elem= part_info->curr_part_elem; - if (! lex->is_partition_management()) - { - if (unlikely(part_info->part_type != VERSIONING_PARTITION)) - my_yyabort_error((ER_PARTITION_WRONG_TYPE, MYF(0), "SYSTEM_TIME")); - } - else - { - part_info->vers_init_info(thd); - elem->id= UINT_MAX32; - } - DBUG_ASSERT(part_info->vers_info); - if (unlikely(part_info->vers_info->now_part)) - { - DBUG_ASSERT(Lex->create_last_non_select_table); - DBUG_ASSERT(Lex->create_last_non_select_table->table_name.str); - my_yyabort_error((ER_VERS_WRONG_PARTS, MYF(0), - Lex->create_last_non_select_table-> - table_name.str)); - } - elem->type(partition_element::HISTORY); - if (unlikely(part_info->init_column_part(thd))) + if (Lex->part_values_history(thd)) MYSQL_YYABORT; } | DEFAULT @@ -5992,7 +5924,6 @@ opt_versioning_rotation: } ; - ; opt_versioning_interval_start: /* empty */ @@ -6712,46 +6643,16 @@ field_def: Lex->last_field->flags&= ~NOT_NULL_FLAG; // undo automatic NOT NULL for timestamps } vcol_opt_specifier vcol_opt_attribute - | opt_generated_always AS ROW_SYM start_or_end opt_asrow_attribute + | opt_generated_always AS ROW_SYM START_SYM opt_asrow_attribute { - LEX *lex= Lex; - Vers_parse_info &info= lex->vers_get_info(); - const LEX_CSTRING &field_name= lex->last_field->field_name; - - Lex_ident *p; - switch ($4) - { - case 1: - p= &info.as_row.start; - if (unlikely(p->str)) - { - my_yyabort_error((ER_VERS_DUPLICATE_ROW_START_END, MYF(0), - "START", field_name.str)); - } - lex->last_field->flags|= VERS_SYS_START_FLAG | NOT_NULL_FLAG; - break; - case 0: - p= &info.as_row.end; - if (unlikely(p->str)) - { - my_yyabort_error((ER_VERS_DUPLICATE_ROW_START_END, MYF(0), - "END", field_name.str)); - } - lex->last_field->flags|= VERS_SYS_END_FLAG | NOT_NULL_FLAG; - break; - default: - /* Not Reachable */ + if (Lex->last_field_generated_always_as_row_start()) + MYSQL_YYABORT; + } + | opt_generated_always AS ROW_SYM END opt_asrow_attribute + { + if (Lex->last_field_generated_always_as_row_end()) MYSQL_YYABORT; - break; - } - DBUG_ASSERT(p); - *p= field_name; } - ; - -start_or_end: - START_SYM { $$ = 1; } - | END { $$ = 0; } ; opt_generated_always: @@ -6804,7 +6705,7 @@ vcol_attribute: | COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment= $2; } | INVISIBLE_SYM { - Lex->last_field->invisible= INVISIBLE_USER; + Lex->last_field->invisible= INVISIBLE_USER; } ; @@ -8348,9 +8249,7 @@ alter_list_item: lex->select_lex.db= $3->db; if (lex->select_lex.db.str == NULL && unlikely(lex->copy_db_to(&lex->select_lex.db))) - { MYSQL_YYABORT; - } if (unlikely(check_table_name($3->table.str,$3->table.length, FALSE)) || ($3->db.str && unlikely(check_db_name((LEX_STRING*) &$3->db)))) @@ -11214,17 +11113,17 @@ window_func_expr: $$= new (thd->mem_root) Item_window_func(thd, (Item_sum *) $1, $3); if (unlikely($$ == NULL)) MYSQL_YYABORT; - if (Select->add_window_func((Item_window_func *) $$)) + if (unlikely(Select->add_window_func((Item_window_func *) $$))) MYSQL_YYABORT; } | window_func OVER_SYM window_spec { LEX *lex= Lex; - if (Select->add_window_spec(thd, lex->win_ref, - Select->group_list, - Select->order_list, - lex->win_frame)) + if (unlikely(Select->add_window_spec(thd, lex->win_ref, + Select->group_list, + Select->order_list, + lex->win_frame))) MYSQL_YYABORT; $$= new (thd->mem_root) Item_window_func(thd, (Item_sum *) $1, thd->lex->win_spec); @@ -11352,10 +11251,10 @@ inverse_distribution_function: '(' opt_window_partition_clause ')' { LEX *lex= Lex; - if (Select->add_window_spec(thd, lex->win_ref, - Select->group_list, - Select->order_list, - NULL)) + if (unlikely(Select->add_window_spec(thd, lex->win_ref, + Select->group_list, + Select->order_list, + NULL))) MYSQL_YYABORT; $$= new (thd->mem_root) Item_window_func(thd, (Item_sum *) $1, thd->lex->win_spec); @@ -11378,9 +11277,7 @@ percentile_function: Item *args= new (thd->mem_root) Item_decimal(thd, "0.5", 3, thd->charset()); if (unlikely(args == NULL) || unlikely(thd->is_error())) - { MYSQL_YYABORT; - } Select->prepare_add_window_spec(thd); if (unlikely(add_order_to_list(thd, $3,FALSE))) MYSQL_YYABORT; @@ -11411,7 +11308,7 @@ order_by_single_element_list: { if (unlikely(add_order_to_list(thd, $3,(bool) $4))) MYSQL_YYABORT; - } + } ; @@ -11640,13 +11537,15 @@ when_list: $$= new (thd->mem_root) List<Item>; if (unlikely($$ == NULL)) MYSQL_YYABORT; - $$->push_back($2, thd->mem_root); - $$->push_back($4, thd->mem_root); + if (unlikely($$->push_back($2, thd->mem_root) || + $$->push_back($4, thd->mem_root))) + MYSQL_YYABORT; } | when_list WHEN_SYM expr THEN_SYM expr { - $1->push_back($3, thd->mem_root); - $1->push_back($5, thd->mem_root); + if (unlikely($1->push_back($3, thd->mem_root) || + $1->push_back($5, thd->mem_root))) + MYSQL_YYABORT; $$= $1; } ; @@ -11655,7 +11554,8 @@ when_list_opt_else: when_list | when_list ELSE expr { - $1->push_back($3, thd->mem_root); + if (unlikely($1->push_back($3, thd->mem_root))) + MYSQL_YYABORT; $$= $1; } ; @@ -12114,34 +12014,12 @@ derived_query_specification: derived_table_value_constructor: VALUES { - LEX *lex=Lex; - lex->field_list.empty(); - lex->many_values.empty(); - lex->insert_list=0; - } + Lex->tvc_start(); + } values_list { - LEX *lex= Lex; - lex->derived_tables|= DERIVED_SUBQUERY; - if (unlikely(!lex->expr_allows_subselect || - lex->sql_command == (int)SQLCOM_PURGE)) - { - thd->parse_error(); - MYSQL_YYABORT; - } - if (lex->current_select->linkage == GLOBAL_OPTIONS_TYPE || - unlikely(mysql_new_select(lex, 1, NULL))) + if (Lex->tvc_finalize_derived()) MYSQL_YYABORT; - mysql_init_select(lex); - lex->current_select->linkage= DERIVED_TABLE_TYPE; - - if (unlikely(!(lex->current_select->tvc= - new (lex->thd->mem_root) - table_value_constr(lex->many_values, - lex->current_select, - lex->current_select->options)))) - MYSQL_YYABORT; - lex->many_values.empty(); $$= NULL; } ; @@ -13188,7 +13066,7 @@ table_list: table_name: table_ident { - if (unlikely(!Select->add_table_to_list(thd, $1, NULL, + if (unlikely(!Select->add_table_to_list(thd, $1, NULL, TL_OPTION_UPDATING, YYPS->m_lock_type, YYPS->m_mdl_type))) @@ -14967,17 +14845,13 @@ NUM_literal: $$= new (thd->mem_root) Item_decimal(thd, $1.str, $1.length, thd->charset()); if (unlikely($$ == NULL) || unlikely(thd->is_error())) - { MYSQL_YYABORT; - } } | FLOAT_NUM { $$= new (thd->mem_root) Item_float(thd, $1.str, $1.length); if (unlikely($$ == NULL) || unlikely(thd->is_error())) - { MYSQL_YYABORT; - } } ; @@ -15952,13 +15826,13 @@ start_option_value_list_following_option_type: option_value_following_option_type { if (unlikely(sp_create_assignment_instr(thd, yychar == YYEMPTY))) - MYSQL_YYABORT; + MYSQL_YYABORT; } option_value_list_continued | TRANSACTION_SYM transaction_characteristics { if (unlikely(sp_create_assignment_instr(thd, yychar == YYEMPTY))) - MYSQL_YYABORT; + MYSQL_YYABORT; } ; @@ -15976,7 +15850,7 @@ option_value_list: option_value { if (unlikely(sp_create_assignment_instr(thd, yychar == YYEMPTY))) - MYSQL_YYABORT; + MYSQL_YYABORT; } | option_value_list ',' { @@ -15985,7 +15859,7 @@ option_value_list: option_value { if (unlikely(sp_create_assignment_instr(thd, yychar == YYEMPTY))) - MYSQL_YYABORT; + MYSQL_YYABORT; } ; @@ -16094,7 +15968,7 @@ option_value_no_option_type: LEX *lex= Lex; sp_pcontext *spc= lex->spcont; LEX_CSTRING names= { STRING_WITH_LEN("names") }; - if (spc && spc->find_variable(&names, false)) + if (unlikely(spc && spc->find_variable(&names, false))) my_error(ER_SP_BAD_VAR_SHADOW, MYF(0), names.str); else thd->parse_error(); @@ -16171,6 +16045,7 @@ option_value_no_option_type: } ; + transaction_characteristics: transaction_access_mode | isolation_level @@ -17135,29 +17010,20 @@ simple_table: query_specification { $$= $1; } | table_value_constructor { $$= $1; } ; - + table_value_constructor: VALUES { - LEX *lex=Lex; - lex->field_list.empty(); - lex->many_values.empty(); - lex->insert_list=0; + Lex->tvc_start(); } values_list - { - LEX *lex=Lex; - $$= lex->current_select; - mysql_init_select(Lex); - if (unlikely(!($$->tvc= - new (lex->thd->mem_root) - table_value_constr(lex->many_values, $$, - $$->options)))) - MYSQL_YYABORT; - lex->many_values.empty(); + { + $$= Lex->current_select; + if (Lex->tvc_finalize()) + MYSQL_YYABORT; } ; - + /* Corresponds to the SQL Standard <query specification> ::= @@ -17181,7 +17047,7 @@ query_term_union_not_ready: query_term_union_ready: simple_table opt_select_lock_type { $$= $1; } - | '(' select_paren_derived ')' { $$= $2; } + | '(' select_paren_derived ')' { $$= $2; } ; query_expression_body: @@ -17449,8 +17315,8 @@ trigger_tail: } table_ident /* $10 */ FOR_SYM - remember_name /* $13 */ - { /* $14 */ + remember_name /* $12 */ + { /* $13 */ Lex->raw_trg_on_table_name_end= YYLIP->get_tok_start(); } EACH_SYM diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index a4491258906..50a1cd26ee5 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -67,6 +67,9 @@ #include "lex_token.h" #include "sql_lex.h" #include "sql_sequence.h" +#include "sql_tvc.h" +#include "vers_utils.h" +#include "my_base.h" /* this is to get the bison compilation windows warnings out */ #ifdef _MSC_VER @@ -187,6 +190,7 @@ void ORAerror(THD *thd, const char *s) LEX_CSTRING name; uint offset; } sp_cursor_name_and_offset; + vers_history_point_t vers_history_point; /* pointers */ Create_field *create_field; @@ -268,6 +272,8 @@ void ORAerror(THD *thd, const char *s) enum Window_frame::Frame_exclusion frame_exclusion; enum trigger_order_type trigger_action_order_type; DDL_options_st object_ddl_options; + enum vers_sys_type_t vers_range_unit; + enum Column_definition::enum_column_versioning vers_column_versioning; } %{ @@ -278,10 +284,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %parse-param { THD *thd } %lex-param { THD *thd } /* - Currently there are 94 shift/reduce conflicts. + Currently there are 99 shift/reduce conflicts. We should not introduce new conflicts any more. */ -%expect 94 +%expect 99 /* Comments for TOKENS. @@ -1058,6 +1064,68 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %left COLLATE_SYM /* + Tokens that can change their meaning from identifier to something else + in certain context. + + - TRANSACTION: identifier, history unit: + SELECT transaction FROM t1; + SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @var; + + - TIMESTAMP: identifier, literal, history unit: + SELECT timestamp FROM t1; + SELECT TIMESTAMP '2001-01-01 10:20:30'; + SELECT * FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP CONCAT(@date,' ',@time); + + - PERIOD: identifier, period for sytem time: + SELECT period FROM t1; + ALTER TABLE DROP PERIOD FOR SYSTEM TIME; + + - SYSTEM: identifier, system versioning: + SELECT system FROM t1; + ALTER TABLE DROP SYSTEM VERSIONIONG; + + Note, we need here only tokens that cause shirt/reduce conflicts + with keyword identifiers. For example: + opt_clause1: %empty | KEYWORD ... ; + clause2: opt_clause1 ident; + KEYWORD can appear both in opt_clause1 and in "ident" through the "keyword" + rule. So the parser reports a conflict on how to interpret KEYWORD: + - as a start of non-empty branch in opt_clause1, or + - as an identifier which follows the empty branch in opt_clause1. + + Example#1: + alter_list_item: + DROP opt_column opt_if_exists_table_element field_ident + | DROP SYSTEM VERSIONING_SYM + SYSTEM can be a keyword in field_ident, or can be a start of + SYSTEM VERSIONING. + + Example#2: + system_time_expr: AS OF_SYM history_point + history_point: opt_history_unit bit_expr + opt_history_unit: | TRANSACTION_SYM + TRANSACTION can be a non-empty history unit, or can be an identifier + in bit_expr. + + In the grammar below we use %prec to explicitely tell Bison to go + through the empty branch in the optional rule only when the lookahead + token does not belong to a small set of selected tokens. + + Tokens NEXT_SYM and PREVIOUS_SYM also change their meaning from + identifiers to sequence operations when followed by VALUE_SYM: + SELECT NEXT VALUE FOR s1, PREVIOUS VALUE FOR s1; + but we don't need to list them here as they do not seem to cause + conflicts (according to bison -v), as both meanings + (as identifier, and as a sequence operation) are parts of the same target + column_default_non_parenthesized_expr, and there are no any optional + clauses between the start of column_default_non_parenthesized_expr + and until NEXT_SYM / PREVIOUS_SYM. +*/ +%left PREC_BELOW_IDENTIFIER_OPT_SPECIAL_CASE +%left TRANSACTION_SYM TIMESTAMP PERIOD SYSTEM + + +/* Tokens that can appear in a token contraction on the second place and change the meaning of the previous token. @@ -1067,7 +1135,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); SELECT TIMESTAMP'2001-01-01 00:00:00' FROM t1; - Parenthesis: changes the meaning of TIMESTAMP/TIME/DATE - from identifier to CAST: + from identifiers to CAST-alike functions: SELECT timestamp FROM t1; SELECT timestamp(1) FROM t1; @@ -1080,7 +1148,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); ALTER TABLE t1 ADD SYSTEM VERSIONING; */ %left PREC_BELOW_CONTRACTION_TOKEN2 -%left TEXT_STRING '(' VALUE_SYM +%left TEXT_STRING '(' VALUE_SYM VERSIONING_SYM %type <lex_str> DECIMAL_NUM FLOAT_NUM NUM LONG_NUM @@ -1143,6 +1211,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <const_simple_string> field_length opt_field_length opt_field_length_default_1 + opt_compression_method %type <string> text_string hex_or_bin_String opt_gconcat_separator @@ -1179,7 +1248,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); optional_flush_tables_arguments opt_time_precision kill_type kill_option int_num opt_default_time_precision - case_stmt_body opt_bin_mod + case_stmt_body opt_bin_mod opt_for_system_time_clause opt_if_exists_table_element opt_if_not_exists_table_element opt_recursive opt_format_xid @@ -1203,7 +1272,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <ulong_num> ulong_num real_ulong_num merge_insert_types - ws_nweights + ws_nweights opt_versioning_interval_start ws_level_flag_desc ws_level_flag_reverse ws_level_flags opt_ws_levels ws_level_list ws_level_list_item ws_level_number ws_level_range ws_level_list_or_range bool @@ -1242,6 +1311,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); inverse_distribution_function_def explicit_cursor_attr function_call_keyword + function_call_keyword_timestamp function_call_nonkeyword function_call_generic function_call_conflict kill_expr @@ -1296,7 +1366,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); table_primary_ident table_primary_derived select_derived derived_table_list select_derived_union + derived_simple_table derived_query_specification + derived_table_value_constructor %type <date_time_type> date_time_type; %type <interval> interval @@ -1333,11 +1405,13 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %type <select_lex> subselect get_select_lex get_select_lex_derived + simple_table query_specification query_term_union_not_ready query_term_union_ready query_expression_body select_paren_derived + table_value_constructor %type <boolfunc2creator> comp_op @@ -1408,12 +1482,16 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); keep_gcc_happy key_using_alg part_column_list + period_for_system_time server_def server_options_list server_option definer_opt no_definer definer get_diagnostics parse_vcol_expr vcol_opt_specifier vcol_opt_attribute vcol_opt_attribute_list vcol_attribute opt_serial_attribute opt_serial_attribute_list serial_attribute - explainable_command opt_lock_wait_timeout + explainable_command + opt_lock_wait_timeout + opt_delete_gtid_domain + asrow_attribute set_assign sf_tail_standalone sp_tail_standalone @@ -1496,7 +1574,6 @@ END_OF_INPUT %type <frame_exclusion> opt_window_frame_exclusion; %type <window_frame_bound> window_frame_start window_frame_bound; - %type <NONE> '-' '+' '*' '/' '%' '(' ')' ',' '!' '{' '}' '&' '|' AND_SYM OR_SYM BETWEEN_SYM CASE_SYM @@ -1509,6 +1586,9 @@ END_OF_INPUT %type <lex_str_list> opt_with_column_list +%type <vers_range_unit> opt_history_unit +%type <vers_history_point> history_point +%type <vers_column_versioning> with_or_without_system %% @@ -2505,7 +2585,7 @@ sequence_def: | START_SYM opt_with longlong_num { if (unlikely(Lex->create_info.seq_create_info->used_fields & - seq_field_used_start)) + seq_field_used_start)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "START")); Lex->create_info.seq_create_info->start= $3; Lex->create_info.seq_create_info->used_fields|= seq_field_used_start; @@ -4228,7 +4308,6 @@ case_stmt_body: { if (unlikely(Lex->case_stmt_action_expr($2))) MYSQL_YYABORT; - if (unlikely(Lex->sphead->restore_lex(thd))) MYSQL_YYABORT; } @@ -4613,7 +4692,7 @@ sp_labeled_control: sp_unlabeled_control: LOOP_SYM { - if (unlikely(unlikely(Lex->sp_push_loop_empty_label(thd)))) + if (unlikely(Lex->sp_push_loop_empty_label(thd))) MYSQL_YYABORT; } loop_body @@ -4622,7 +4701,7 @@ sp_unlabeled_control: } | WHILE_SYM { - if (unlikely(unlikely(Lex->sp_push_loop_empty_label(thd)))) + if (unlikely(Lex->sp_push_loop_empty_label(thd))) MYSQL_YYABORT; Lex->sphead->reset_lex(thd); } @@ -5018,40 +5097,8 @@ size_number: real_ulonglong_num { $$= $1;} | IDENT_sys { - ulonglong number; - uint text_shift_number= 0; - longlong prefix_number; - const char *start_ptr= $1.str; - size_t str_len= $1.length; - const char *end_ptr= start_ptr + str_len; - int error; - prefix_number= my_strtoll10(start_ptr, (char**) &end_ptr, &error); - if (likely((start_ptr + str_len - 1) == end_ptr)) - { - switch (end_ptr[0]) - { - case 'g': - case 'G': - text_shift_number+=10; - /* fall through */ - case 'm': - case 'M': - text_shift_number+=10; - /* fall through */ - case 'k': - case 'K': - text_shift_number+=10; - break; - default: - my_yyabort_error((ER_WRONG_SIZE_NUMBER, MYF(0))); - } - if (unlikely(prefix_number >> 31)) - my_yyabort_error((ER_SIZE_OVERFLOW_ERROR, MYF(0))); - number= prefix_number << text_shift_number; - } - else - my_yyabort_error((ER_WRONG_SIZE_NUMBER, MYF(0))); - $$= number; + if ($1.to_size_number(thd, &$$)) + MYSQL_YYABORT; } ; @@ -5094,7 +5141,7 @@ create_like: opt_create_select: /* empty */ {} - | opt_duplicate opt_as create_select_query_expression + | opt_duplicate opt_as create_select_query_expression opt_versioning_option ; create_select_query_expression: @@ -5166,7 +5213,6 @@ partitioning: lex->part_info= new (thd->mem_root) partition_info(); if (unlikely(!lex->part_info)) MYSQL_YYABORT; - if (lex->sql_command == SQLCOM_ALTER_TABLE) { lex->alter_info.partition_flags|= ALTER_PARTITION_INFO; @@ -5193,12 +5239,12 @@ have_partitioning: partition_entry: PARTITION_SYM { - LEX *lex= Lex; - if (unlikely(!lex->part_info)) + if (unlikely(!Lex->part_info)) { thd->parse_error(ER_PARTITION_ENTRY_ERROR); MYSQL_YYABORT; } + DBUG_ASSERT(Lex->part_info->table); /* We enter here when opening the frm file to translate partition info string into part_info data structure. @@ -5239,6 +5285,12 @@ part_type_def: } | LIST_SYM part_column_list { Lex->part_info->part_type= LIST_PARTITION; } + | SYSTEM_TIME_SYM + { + if (unlikely(Lex->part_info->vers_init_info(thd))) + MYSQL_YYABORT; + } + opt_versioning_rotation ; opt_linear: @@ -5284,7 +5336,6 @@ part_field_item: if (unlikely(part_info->part_field_list.push_back($1.str, thd->mem_root))) MYSQL_YYABORT; - if (unlikely(part_info->num_columns > MAX_REF_PARTS)) my_yyabort_error((ER_TOO_MANY_PARTITION_FUNC_FIELDS_ERROR, MYF(0), "list of partition fields")); @@ -5442,6 +5493,7 @@ part_definition: MYSQL_YYABORT; p_elem->part_state= PART_NORMAL; + p_elem->id= part_info->partitions.elements - 1; part_info->curr_part_elem= p_elem; part_info->current_partition= p_elem; part_info->use_default_partitions= FALSE; @@ -5472,12 +5524,12 @@ opt_part_values: partition_info *part_info= lex->part_info; if (! lex->is_partition_management()) { - if (unlikely(part_info->part_type == RANGE_PARTITION)) - my_yyabort_error((ER_PARTITION_REQUIRES_VALUES_ERROR, MYF(0), - "RANGE", "LESS THAN")); - if (unlikely(part_info->part_type == LIST_PARTITION)) - my_yyabort_error((ER_PARTITION_REQUIRES_VALUES_ERROR, MYF(0), - "LIST", "IN")); + if (unlikely(part_info->error_if_requires_values())) + MYSQL_YYABORT; + if (unlikely(part_info->part_type == VERSIONING_PARTITION)) + my_yyabort_error((ER_VERS_WRONG_PARTS, MYF(0), + lex->create_last_non_select_table-> + table_name.str)); } else part_info->part_type= HASH_PARTITION; @@ -5510,6 +5562,16 @@ opt_part_values: part_info->part_type= LIST_PARTITION; } part_values_in {} + | CURRENT_SYM + { + if (Lex->part_values_current(thd)) + MYSQL_YYABORT; + } + | HISTORY_SYM + { + if (Lex->part_values_history(thd)) + MYSQL_YYABORT; + } | DEFAULT { LEX *lex= Lex; @@ -5604,9 +5666,7 @@ part_value_item: if (unlikely(!(part_info->part_type == LIST_PARTITION && part_info->num_columns == 1U) && part_info->init_column_part(thd))) - { MYSQL_YYABORT; - } } part_value_item_list {} ')' @@ -5739,6 +5799,7 @@ sub_part_definition: unlikely(curr_part->subpartitions.push_back(sub_p_elem, thd->mem_root))) MYSQL_YYABORT; + sub_p_elem->id= curr_part->subpartitions.elements - 1; part_info->curr_part_elem= sub_p_elem; part_info->use_default_subpartitions= FALSE; part_info->use_default_num_subpartitions= FALSE; @@ -5795,6 +5856,50 @@ opt_part_option: { Lex->part_info->curr_part_elem->part_comment= $3.str; } ; +opt_versioning_rotation: + /* empty */ {} + | INTERVAL_SYM expr interval opt_versioning_interval_start + { + partition_info *part_info= Lex->part_info; + if (unlikely(part_info->vers_set_interval($2, $3, $4))) + { + my_error(ER_PART_WRONG_VALUE, MYF(0), + Lex->create_last_non_select_table->table_name.str, + "INTERVAL"); + MYSQL_YYABORT; + } + } + | LIMIT ulonglong_num + { + partition_info *part_info= Lex->part_info; + if (unlikely(part_info->vers_set_limit($2))) + { + my_error(ER_PART_WRONG_VALUE, MYF(0), + Lex->create_last_non_select_table->table_name.str, + "LIMIT"); + MYSQL_YYABORT; + } + } + ; + + +opt_versioning_interval_start: + /* empty */ + { + $$= thd->query_start(); + } + | STARTS_SYM ulong_num + { + /* only allowed from mysql_unpack_partition() */ + if (unlikely(!Lex->part_info->table)) + { + thd->parse_error(ER_SYNTAX_ERROR, $1.pos()); + MYSQL_YYABORT; + } + $$= (ulong)$2; + } + ; + /* End of partition parser part */ @@ -6163,6 +6268,31 @@ create_table_option: { Lex->create_info.used_fields|= HA_CREATE_USED_SEQUENCE; Lex->create_info.sequence= ($3 == HA_CHOICE_YES); + } + | versioning_option + ; + +opt_versioning_option: + /* empty */ + | versioning_option + ; + +versioning_option: + WITH_SYSTEM_SYM VERSIONING_SYM + { + if (unlikely(Lex->create_info.options & HA_LEX_CREATE_TMP_TABLE)) + { + if (DBUG_EVALUATE_IF("sysvers_force", 0, 1)) + { + my_error(ER_VERS_TEMPORARY, MYF(0)); + MYSQL_YYABORT; + } + } + else + { + Lex->alter_info.flags|= ALTER_ADD_SYSTEM_VERSIONING; + Lex->create_info.options|= HA_VERSIONED_TABLE; + } } ; @@ -6182,9 +6312,7 @@ default_collation: cinfo->default_table_charset && $4 && !($4= merge_charset_and_collation(cinfo->default_table_charset, $4)))) - { MYSQL_YYABORT; - } Lex->create_info.default_table_charset= $4; Lex->create_info.used_fields|= HA_CREATE_USED_DEFAULT_CHARSET; @@ -6263,6 +6391,7 @@ field_list_item: column_def { } | key_def | constraint_def + | period_for_system_time ; column_def: @@ -6363,6 +6492,15 @@ constraint_def: } ; +period_for_system_time: + // If FOR_SYM is followed by SYSTEM_TIME_SYM then they are merged to: FOR_SYSTEM_TIME_SYM . + PERIOD_SYM FOR_SYSTEM_TIME_SYM '(' ident ',' ident ')' + { + Vers_parse_info &info= Lex->vers_get_info(); + info.set_system_time($4, $6); + } + ; + opt_check_constraint: /* empty */ { $$= (Virtual_column_info*) 0; } | check_constraint { $$= $1;} @@ -6445,6 +6583,15 @@ opt_serial_attribute_list: | serial_attribute ; +opt_asrow_attribute: + /* empty */ {} + | opt_asrow_attribute_list {} + ; + +opt_asrow_attribute_list: + opt_asrow_attribute_list asrow_attribute {} + | asrow_attribute + ; field_def: opt_attribute @@ -6454,6 +6601,16 @@ field_def: Lex->last_field->flags&= ~NOT_NULL_FLAG; // undo automatic NOT NULL for timestamps } vcol_opt_specifier vcol_opt_attribute + | opt_generated_always AS ROW_SYM START_SYM opt_asrow_attribute + { + if (Lex->last_field_generated_always_as_row_start()) + MYSQL_YYABORT; + } + | opt_generated_always AS ROW_SYM END opt_asrow_attribute + { + if (Lex->last_field_generated_always_as_row_end()) + MYSQL_YYABORT; + } ; opt_generated_always: @@ -6986,7 +7143,7 @@ attribute: } | AUTO_INC { Lex->last_field->flags|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG; } | SERIAL_SYM DEFAULT VALUE_SYM - { + { LEX *lex=Lex; lex->last_field->flags|= AUTO_INCREMENT_FLAG | NOT_NULL_FLAG | UNIQUE_KEY_FLAG; lex->alter_info.flags|= ALTER_ADD_INDEX; @@ -6998,11 +7155,24 @@ attribute: $2->name,Lex->charset->csname)); Lex->last_field->charset= $2; } + | COMPRESSED_SYM opt_compression_method + { + if (unlikely(Lex->last_field->set_compressed($2))) + MYSQL_YYABORT; + } | serial_attribute ; -serial_attribute: - not NULL_SYM { Lex->last_field->flags|= NOT_NULL_FLAG; } +opt_compression_method: + /* empty */ { $$= NULL; } + | equal ident { $$= $2.str; } + ; + +asrow_attribute: + not NULL_SYM + { + Lex->last_field->flags|= NOT_NULL_FLAG; + } | opt_primary KEY_SYM { LEX *lex=Lex; @@ -7010,6 +7180,10 @@ serial_attribute: lex->alter_info.flags|= ALTER_ADD_INDEX; } | vcol_attribute + ; + +serial_attribute: + asrow_attribute | IDENT_sys equal TEXT_STRING_sys { if (unlikely($3.length > ENGINE_OPTION_MAX_LENGTH)) @@ -7040,6 +7214,26 @@ serial_attribute: engine_option_value($1, &Lex->last_field->option_list, &Lex->option_list_last); } + | with_or_without_system VERSIONING_SYM + { + Lex->last_field->versioning= $1; + Lex->create_info.options|= HA_VERSIONED_TABLE; + } + ; + +with_or_without_system: + WITH_SYSTEM_SYM + { + Lex->alter_info.flags|= ALTER_COLUMN_UNVERSIONED; + Lex->create_info.vers_info.versioned_fields= true; + $$= Column_definition::WITH_VERSIONING; + } + | WITHOUT SYSTEM + { + Lex->alter_info.flags|= ALTER_COLUMN_UNVERSIONED; + Lex->create_info.vers_info.unversioned_fields= true; + $$= Column_definition::WITHOUT_VERSIONING; + } ; @@ -7411,9 +7605,9 @@ key_using_alg: all_key_opt: KEY_BLOCK_SIZE opt_equal ulong_num { - Lex->create_info.used_fields|= HA_USES_BLOCK_SIZE; - Lex->create_info.key_block_size= $3; - } + Lex->last_key->key_create_info.block_size= $3; + Lex->last_key->key_create_info.flags|= HA_USES_BLOCK_SIZE; + } | COMMENT_SYM TEXT_STRING_sys { Lex->last_key->key_create_info.comment= $2; } | IDENT_sys equal TEXT_STRING_sys @@ -7874,9 +8068,7 @@ alter_commands: lex->select_lex.db= $6->db; if (lex->select_lex.db.str == NULL && unlikely(lex->copy_db_to(&lex->select_lex.db))) - { MYSQL_YYABORT; - } lex->name= $6->table; lex->alter_info.partition_flags|= ALTER_PARTITION_EXCHANGE; if (unlikely(!lex->select_lex.add_table_to_list(thd, $6, NULL, @@ -8007,6 +8199,10 @@ alter_list_item: Lex->create_last_non_select_table= Lex->last_table(); Lex->alter_info.flags|= ALTER_ADD_INDEX; } + | ADD period_for_system_time + { + Lex->alter_info.flags|= ALTER_ADD_PERIOD; + } | add_column '(' create_field_list ')' { LEX *lex=Lex; @@ -8163,6 +8359,19 @@ alter_list_item: } | alter_algorithm_option | alter_lock_option + | ADD SYSTEM VERSIONING_SYM + { + Lex->alter_info.flags|= ALTER_ADD_SYSTEM_VERSIONING; + Lex->create_info.options|= HA_VERSIONED_TABLE; + } + | DROP SYSTEM VERSIONING_SYM + { + Lex->alter_info.flags|= ALTER_DROP_SYSTEM_VERSIONING; + } + | DROP PERIOD_SYM FOR_SYSTEM_TIME_SYM + { + Lex->alter_info.flags|= ALTER_DROP_PERIOD; + } ; opt_index_lock_algorithm: @@ -8199,7 +8408,7 @@ alter_lock_option: ; opt_column: - /* empty */ {} + /* empty */ {} %prec PREC_BELOW_IDENTIFIER_OPT_SPECIAL_CASE | COLUMN_SYM {} ; @@ -8835,6 +9044,9 @@ select: select_init: SELECT_SYM select_options_and_item_list select_init3 + | table_value_constructor + | table_value_constructor union_list + | table_value_constructor union_order_or_limit | '(' select_paren ')' | '(' select_paren ')' union_list | '(' select_paren ')' union_order_or_limit @@ -8842,6 +9054,9 @@ select_init: union_list_part2: SELECT_SYM select_options_and_item_list select_init3_union_query_term + | table_value_constructor + | table_value_constructor union_list + | table_value_constructor union_order_or_limit | '(' select_paren_union_query_term ')' | '(' select_paren_union_query_term ')' union_list | '(' select_paren_union_query_term ')' union_order_or_limit @@ -8849,6 +9064,14 @@ union_list_part2: select_paren: { + Lex->current_select->set_braces(true); + } + table_value_constructor + { + DBUG_ASSERT(Lex->current_select->braces); + } + | + { /* In order to correctly parse UNION's global ORDER BY we need to set braces before parsing the clause. @@ -8900,6 +9123,15 @@ select_paren_derived: { Lex->current_select->set_braces(true); } + table_value_constructor + { + DBUG_ASSERT(Lex->current_select->braces); + $$= Lex->current_select->master_unit()->first_select(); + } + | + { + Lex->current_select->set_braces(true); + } SELECT_SYM select_part2_derived opt_table_expression opt_order_clause @@ -9059,6 +9291,70 @@ select_options: } ; +opt_history_unit: + /* empty*/ %prec PREC_BELOW_IDENTIFIER_OPT_SPECIAL_CASE + { + $$= VERS_UNDEFINED; + } + | TRANSACTION_SYM + { + $$= VERS_TRX_ID; + } + | TIMESTAMP + { + $$= VERS_TIMESTAMP; + } + ; + +history_point: + TIMESTAMP TEXT_STRING + { + Item *item; + if (!(item= create_temporal_literal(thd, $2.str, $2.length, YYCSCL, + MYSQL_TYPE_DATETIME, true))) + MYSQL_YYABORT; + $$= Vers_history_point(VERS_TIMESTAMP, item); + } + | function_call_keyword_timestamp + { + $$= Vers_history_point(VERS_TIMESTAMP, $1); + } + | opt_history_unit bit_expr + { + $$= Vers_history_point($1, $2); + } + ; + +opt_for_system_time_clause: + /* empty */ + { + $$= false; + } + | FOR_SYSTEM_TIME_SYM system_time_expr + { + $$= true; + } + ; + +system_time_expr: + AS OF_SYM history_point + { + Lex->vers_conditions.init(SYSTEM_TIME_AS_OF, $3); + } + | ALL + { + Lex->vers_conditions.init(SYSTEM_TIME_ALL); + } + | FROM history_point TO_SYM history_point + { + Lex->vers_conditions.init(SYSTEM_TIME_FROM_TO, $2, $4); + } + | BETWEEN_SYM history_point AND_SYM history_point + { + Lex->vers_conditions.init(SYSTEM_TIME_BETWEEN, $2, $4); + } + ; + select_option_list: select_option_list select_option | select_option @@ -9415,7 +9711,7 @@ predicate: | bit_expr not IN_SYM '(' subselect ')' { Item *item= new (thd->mem_root) Item_in_subselect(thd, $1, $5); - if (item == NULL) + if (unlikely(item == NULL)) MYSQL_YYABORT; $$= negate_expression(thd, item); if (unlikely($$ == NULL)) @@ -10004,7 +10300,21 @@ mysql_concatenation_expr: } ; - +function_call_keyword_timestamp: + TIMESTAMP '(' expr ')' + { + $$= new (thd->mem_root) Item_datetime_typecast(thd, $3, + AUTO_SEC_PART_DIGITS); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } + | TIMESTAMP '(' expr ',' expr ')' + { + $$= new (thd->mem_root) Item_func_add_time(thd, $3, $5, 1, 0); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } + ; /* Function call syntax using official SQL 2003 keywords. Because the function name is an official token, @@ -10071,8 +10381,9 @@ function_call_keyword: List<Item> *list= new (thd->mem_root) List<Item>; if (unlikely(list == NULL)) MYSQL_YYABORT; - list->push_front($5, thd->mem_root); - list->push_front($3, thd->mem_root); + if (unlikely(list->push_front($5, thd->mem_root)) || + unlikely(list->push_front($3, thd->mem_root))) + MYSQL_YYABORT; Item_row *item= new (thd->mem_root) Item_row(thd, *list); if (unlikely(item == NULL)) MYSQL_YYABORT; @@ -10136,18 +10447,9 @@ function_call_keyword: if (unlikely($$ == NULL)) MYSQL_YYABORT; } - | TIMESTAMP '(' expr ')' + | function_call_keyword_timestamp { - $$= new (thd->mem_root) Item_datetime_typecast(thd, $3, - AUTO_SEC_PART_DIGITS); - if (unlikely($$ == NULL)) - MYSQL_YYABORT; - } - | TIMESTAMP '(' expr ',' expr ')' - { - $$= new (thd->mem_root) Item_func_add_time(thd, $3, $5, 1, 0); - if (unlikely($$ == NULL)) - MYSQL_YYABORT; + $$= $1; } | TRIM '(' trim_operands ')' { @@ -10628,6 +10930,11 @@ geometry_function: Geometry::wkb_polygon, Geometry::wkb_linestring)); } + | WITHIN '(' expr ',' expr ')' + { + $$= GEOM_NEW(thd, Item_func_spatial_precise_rel(thd, $3, $5, + Item_func::SP_WITHIN_FUNC)); + } ; /* @@ -10813,7 +11120,7 @@ sum_expr: | COUNT_SYM '(' opt_all '*' ')' { Item *item= new (thd->mem_root) Item_int(thd, (int32) 0L, 1); - if (item == NULL) + if (unlikely(item == NULL)) MYSQL_YYABORT; $$= new (thd->mem_root) Item_sum_count(thd, item); if (unlikely($$ == NULL)) @@ -11060,6 +11367,9 @@ simple_window_func: MYSQL_YYABORT; } ; + + + inverse_distribution_function: percentile_function OVER_SYM '(' opt_window_partition_clause ')' @@ -11090,9 +11400,8 @@ percentile_function: { Item *args= new (thd->mem_root) Item_decimal(thd, "0.5", 3, thd->charset()); - if (unlikely(args == NULL) || unlikely((thd->is_error()))) + if (unlikely(args == NULL) || unlikely(thd->is_error())) MYSQL_YYABORT; - Select->prepare_add_window_spec(thd); if (unlikely(add_order_to_list(thd, $3,FALSE))) MYSQL_YYABORT; @@ -11481,7 +11790,7 @@ join_table: MYSQL_YYABORT_UNLESS($1 && $3); } '(' using_list ')' - { + { $3->straight=$2; add_join_natural($1,$3,$7,Select); $$=$3; @@ -11616,17 +11925,19 @@ table_primary_ident: SELECT_LEX *sel= Select; sel->table_join_options= 0; } - table_ident opt_use_partition opt_table_alias opt_key_definition + table_ident opt_use_partition opt_for_system_time_clause opt_table_alias opt_key_definition { - if (unlikely(!($$= Select-> - add_table_to_list(thd, $2, $4, - Select->get_table_join_options(), - YYPS->m_lock_type, - YYPS->m_mdl_type, - Select->pop_index_hints(), - $3)))) + if (unlikely(!($$= Select->add_table_to_list(thd, $2, $5, + Select->get_table_join_options(), + YYPS->m_lock_type, + YYPS->m_mdl_type, + Select-> + pop_index_hints(), + $3)))) MYSQL_YYABORT; Select->add_joined_table($$); + if ($4) + $$->vers_conditions= Lex->vers_conditions; } ; @@ -11649,11 +11960,11 @@ table_primary_ident: */ table_primary_derived: - '(' get_select_lex select_derived_union ')' opt_table_alias + '(' get_select_lex select_derived_union ')' opt_for_system_time_clause opt_table_alias { /* Use $2 instead of Lex->current_select as derived table will alter value of Lex->current_select. */ - if (!($3 || $5) && $2->embedding && + if (!($3 || $6) && $2->embedding && !$2->embedding->nested_join->join_list.elements) { /* we have a derived table ($3 == NULL) but no alias, @@ -11676,16 +11987,15 @@ table_primary_derived: if (unlikely(ti == NULL)) MYSQL_YYABORT; if (unlikely(!($$= sel->add_table_to_list(thd, - ti, $5, 0, + ti, $6, 0, TL_READ, MDL_SHARED_READ)))) - MYSQL_YYABORT; sel->add_joined_table($$); lex->pop_context(); lex->nest_level--; } - else if (unlikely($5 != NULL)) + else if (unlikely($6 != NULL)) { /* Tables with or without joins within parentheses cannot @@ -11709,11 +12019,16 @@ table_primary_derived: if ($$ && $$->derived && !$$->derived->first_select()->next_select()) $$->select_lex->add_where_field($$->derived->first_select()); + if ($5) + { + MYSQL_YYABORT_UNLESS(!$3); + $$->vers_conditions= Lex->vers_conditions; + } } /* Represents derived table with WITH clause */ | '(' get_select_lex subselect_start with_clause query_expression_body - subselect_end ')' opt_table_alias + subselect_end ')' opt_for_system_time_clause opt_table_alias { LEX *lex=Lex; SELECT_LEX *sel= $2; @@ -11724,11 +12039,13 @@ table_primary_derived: $5->set_with_clause($4); lex->current_select= sel; if (unlikely(!($$= sel->add_table_to_list(lex->thd, - ti, $8, 0, + ti, $9, 0, TL_READ, MDL_SHARED_READ)))) MYSQL_YYABORT; sel->add_joined_table($$); + if ($8) + $$->vers_conditions= Lex->vers_conditions; } ; @@ -11770,9 +12087,9 @@ select_derived_union: } } union_list_derived_part2 - | derived_query_specification opt_select_lock_type - | derived_query_specification order_or_limit opt_select_lock_type - | derived_query_specification opt_select_lock_type union_list_derived + | derived_simple_table opt_select_lock_type + | derived_simple_table order_or_limit opt_select_lock_type + | derived_simple_table opt_select_lock_type union_list_derived ; union_list_derived_part2: @@ -11827,6 +12144,10 @@ select_derived: } ; +derived_simple_table: + derived_query_specification { $$= $1; } + | derived_table_value_constructor { $$= $1; } + ; /* Similar to query_specification, but for derived tables. Example: the inner parenthesized SELECT in this query: @@ -11841,6 +12162,20 @@ derived_query_specification: } ; +derived_table_value_constructor: + VALUES + { + Lex->tvc_start(); + } + values_list + { + if (Lex->tvc_finalize_derived()) + MYSQL_YYABORT; + $$= NULL; + } + ; + + select_derived2: { LEX *lex= Lex; @@ -13259,10 +13594,29 @@ delete: lex->ignore= 0; lex->select_lex.init_order(); } - opt_delete_options single_multi + delete_part2 + ; + +opt_delete_system_time: + /* empty */ + { + Lex->vers_conditions.init(SYSTEM_TIME_ALL); + } + | BEFORE_SYM SYSTEM_TIME_SYM history_point + { + Lex->vers_conditions.init(SYSTEM_TIME_BEFORE, $3); + } + ; + +delete_part2: + opt_delete_options single_multi {} + | HISTORY_SYM delete_single_table opt_delete_system_time + { + Lex->last_table()->vers_conditions= Lex->vers_conditions; + } ; -single_multi: +delete_single_table: FROM table_ident opt_use_partition { if (unlikely(!Select-> @@ -13275,8 +13629,13 @@ single_multi: YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; } - opt_where_clause opt_order_clause - delete_limit_clause {} + ; + +single_multi: + delete_single_table + opt_where_clause + opt_order_clause + delete_limit_clause opt_select_expressions {} | table_wild_list { @@ -13362,7 +13721,7 @@ opt_delete_option: ; truncate: - TRUNCATE_SYM opt_table_sym + TRUNCATE_SYM { LEX* lex= Lex; lex->sql_command= SQLCOM_TRUNCATE; @@ -13373,7 +13732,7 @@ truncate: YYPS->m_lock_type= TL_WRITE; YYPS->m_mdl_type= MDL_EXCLUSIVE; } - table_name opt_lock_wait_timeout + opt_table_sym table_name opt_lock_wait_timeout { LEX* lex= thd->lex; DBUG_ASSERT(!lex->m_sql_cmd); @@ -13777,7 +14136,7 @@ show_param: { LEX *lex= Lex; lex->sql_command= SQLCOM_SHOW_STATUS_FUNC; - if (unlikely(unlikely(prepare_schema_table(thd, lex, 0, SCH_PROCEDURES)))) + if (unlikely(prepare_schema_table(thd, lex, 0, SCH_PROCEDURES))) MYSQL_YYABORT; } | PACKAGE_SYM STATUS_SYM wild_and_where @@ -14061,7 +14420,7 @@ flush_option: { Lex->type|= REFRESH_GENERAL_LOG; } | SLOW LOGS_SYM { Lex->type|= REFRESH_SLOW_LOG; } - | BINARY LOGS_SYM + | BINARY LOGS_SYM opt_delete_gtid_domain { Lex->type|= REFRESH_BINARY_LOG; } | RELAY LOGS_SYM optional_connection_name { @@ -14119,6 +14478,24 @@ opt_table_list: | table_list {} ; +opt_delete_gtid_domain: + /* empty */ {} + | DELETE_DOMAIN_ID_SYM '=' '(' delete_domain_id_list ')' + {} + ; +delete_domain_id_list: + /* Empty */ + | delete_domain_id + | delete_domain_id_list ',' delete_domain_id + ; + +delete_domain_id: + ulong_num + { + insert_dynamic(&Lex->delete_gtid_domain, (uchar*) &($1)); + } + ; + optional_flush_tables_arguments: /* empty */ {$$= 0;} | AND_SYM DISABLE_SYM CHECKPOINT_SYM {$$= REFRESH_CHECKPOINT; } @@ -15605,7 +15982,7 @@ keyword_sp_not_data_type: | TEMPORARY | TEMPTABLE_SYM | THAN_SYM - | TRANSACTION_SYM + | TRANSACTION_SYM %prec PREC_BELOW_CONTRACTION_TOKEN2 | TRANSACTIONAL_SYM | TRIGGERS_SYM | TRIM_ORACLE @@ -16000,7 +16377,8 @@ transaction_access_mode: item)); if (unlikely(var == NULL)) MYSQL_YYABORT; - lex->var_list.push_back(var, thd->mem_root); + if (unlikely(lex->var_list.push_back(var, thd->mem_root))) + MYSQL_YYABORT; } ; @@ -16502,6 +16880,7 @@ object_privilege: | EVENT_SYM { Lex->grant |= EVENT_ACL;} | TRIGGER_SYM { Lex->grant |= TRIGGER_ACL; } | CREATE TABLESPACE { Lex->grant |= CREATE_TABLESPACE_ACL; } + | DELETE_SYM HISTORY_SYM { Lex->grant |= DELETE_HISTORY_ACL; } ; opt_and: @@ -16953,6 +17332,24 @@ union_option: | ALL { $$=0; } ; +simple_table: + query_specification { $$= $1; } + | table_value_constructor { $$= $1; } + ; + +table_value_constructor: + VALUES + { + Lex->tvc_start(); + } + values_list + { + $$= Lex->current_select; + if (Lex->tvc_finalize()) + MYSQL_YYABORT; + } + ; + /* Corresponds to the SQL Standard <query specification> ::= @@ -16970,13 +17367,13 @@ query_specification: ; query_term_union_not_ready: - query_specification order_or_limit opt_select_lock_type { $$= $1; } + simple_table order_or_limit opt_select_lock_type { $$= $1; } | '(' select_paren_derived ')' union_order_or_limit { $$= $2; } ; query_term_union_ready: - query_specification opt_select_lock_type { $$= $1; } - | '(' select_paren_derived ')' { $$= $2; } + simple_table opt_select_lock_type { $$= $1; } + | '(' select_paren_derived ')' { $$= $2; } ; query_expression_body: @@ -17185,6 +17582,9 @@ view_select: */ query_expression_body_view: SELECT_SYM select_options_and_item_list select_init3_view + | table_value_constructor + | table_value_constructor union_order_or_limit + | table_value_constructor union_list_view | '(' select_paren_view ')' | '(' select_paren_view ')' union_order_or_limit | '(' select_paren_view ')' union_list_view @@ -17462,11 +17862,12 @@ opt_format_xid: $$= false; else { - my_yyabort_error((ER_UNKNOWN_EXPLAIN_FORMAT, MYF(0), "XA RECOVER", $3.str)); + my_yyabort_error((ER_UNKNOWN_EXPLAIN_FORMAT, MYF(0), + "XA RECOVER", $3.str)); $$= false; } - } - ; + } + ; xid: text_string |