diff options
Diffstat (limited to 'mysql-test/main/table_value_constr.result')
| -rw-r--r-- | mysql-test/main/table_value_constr.result | 3281 |
1 files changed, 3281 insertions, 0 deletions
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result new file mode 100644 index 00000000..1ed6953b --- /dev/null +++ b/mysql-test/main/table_value_constr.result @@ -0,0 +1,3281 @@ +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 +3 4 +# 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 +3 4 +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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +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` semi join ((values (1)) `tvc_0`) where 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +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 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +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` semi join ((values (1),(2)) `tvc_0`) where 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 t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +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 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": { + "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": { + "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": { + "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": { + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "union_result": { + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "union_result": { + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "union_result": { + "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_optimization": { + "r_total_time_ms": "REPLACED" + }, + "query_block": { + "union_result": { + "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 point and int for operation 'TABLE VALUE CONSTRUCTOR' +values (1,point(1,1)+1); +ERROR HY000: Illegal parameter data types point 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 +# +BEGIN NOT ATOMIC +DECLARE v INT; +DECLARE cur CURSOR FOR VALUES(7); +OPEN cur; +FETCH cur INTO v; +SELECT v; +END; +| +v +7 +BEGIN NOT ATOMIC +DECLARE v INT DEFAULT 0; +FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR; +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 +# +# MDEV-17017: TVC in derived table +# +create table t1 (a int); +insert into t1 values (9), (3), (2); +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +8 +1 +explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +1 11 +1 11 +7 77 +3 31 +4 42 +explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED 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 <union2,3> ALL NULL NULL NULL NULL NULL +select * from (values (7), (5), (8), (1) union select * from t1) t; +7 +7 +5 +8 +1 +9 +3 +2 +explain select * from (values (7), (5), (8), (1) union select * from t1) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; +# +# MDEV-16930: expression in the first row of TVC specifying derived table +# +SELECT 1 + 1, 2, "abc"; +1 + 1 2 abc +2 2 abc +SELECT * FROM (SELECT 1 + 1, 2, "abc") t; +1 + 1 2 abc +2 2 abc +WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte; +1 + 1 2 abc +2 2 abc +SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc"; +1 + 1 2 abc +2 2 abc +7 3 abc +CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc"; +SELECT * FROM v1; +1 + 1 2 abc +2 2 abc +DROP VIEW v1; +VALUES(1 + 1,2,"abc"); +1 + 1 2 abc +2 2 abc +SELECT * FROM (VALUES(1 + 1,2,"abc")) t; +1 + 1 2 abc +2 2 abc +PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +EXECUTE stmt; +1 + 1 2 abc +2 2 abc +DEALLOCATE PREPARE stmt; +# +# MDEV-17894: tvc with ORDER BY ... LIMIT +# +values (5), (7), (1), (3), (4) limit 2; +5 +5 +7 +explain extended values (5), (7), (1), (3), (4) limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 2 +values (5), (7), (1), (3), (4) limit 2 offset 1; +5 +7 +1 +explain extended values (5), (7), (1), (3), (4) limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 1,2 +values (5), (7), (1), (3), (4) order by 1 limit 2; +5 +1 +3 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 2 +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +5 +3 +4 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 1,2 +values (5), (7), (1), (3), (4) order by 1; +5 +1 +3 +4 +5 +7 +explain extended values (5), (7), (1), (3), (4) order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +5 90 +4 10 +7 20 +3 50 +1 70 +5 90 +explain extended values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5,90),(7,20),(1,70),(3,50),(4,10) order by 2 +select 2 union (values (5), (7), (1), (3), (4) limit 2); +2 +2 +5 +7 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 2) +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +2 +2 +7 +1 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 1,2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +2 +2 +1 +3 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +2 +2 +3 +4 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) +(values (5), (7), (1), (3), (4) limit 2) union select 2; +5 +5 +7 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +5 +7 +1 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +5 +1 +3 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +5 +3 +4 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) union /* select#2 */ select 2 AS `2` +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +3 +3 +3 +4 +explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select 3 AS `3` union all (values (5),(7),(1),(3),(4) limit 3,2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +5 +3 +4 +3 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +3 +3 +1 +3 +explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 3 AS `3` union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +5 +1 +3 +3 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union all /* select#2 */ select 3 AS `3` +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +5 +3 +3 +4 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +5 +3 +3 +4 +5 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +5 +3 +4 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 limit 1,2 +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +ERROR 42S22: Unknown column '3' in 'order clause' +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +2 +2 +5 +7 +execute stmt; +2 +2 +5 +7 +deallocate prepare stmt; +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +2 +2 +1 +3 +execute stmt; +2 +2 +1 +3 +deallocate prepare stmt; +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +3 +3 +5 +7 +execute stmt; +3 +3 +5 +7 +deallocate prepare stmt; +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +3 +3 +1 +3 +execute stmt; +3 +3 +1 +3 +deallocate prepare stmt; +prepare stmt from " +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +"; +execute stmt; +5 +7 +1 +3 +execute stmt; +5 +7 +1 +3 +deallocate prepare stmt; +prepare stmt from " +values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +"; +ERROR 42S22: Unknown column '3' in 'order clause' +create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (5),(7),(1),(3),(4) order by 1 limit 2 latin1 latin1_swedish_ci +select * from v1; +5 +1 +3 +drop view v1; +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (5),(7),(1),(3),(4) limit 1,2) union (values (5),(7),(1),(3),(4) order by 1 limit 2) latin1 latin1_swedish_ci +select * from v1; +5 +7 +1 +3 +drop view v1; +create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; +ERROR 42S22: Unknown column '3' in 'order clause' +create view v1 as +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 2 limit 2 ); +ERROR 42S22: Unknown column '2' in 'order clause' +# +# MDEV-20229: view defined as select using +# CTE with named columns defined as TVC +# +create view v1 as with t(a) as (values (2), (1)) select a from t; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1))select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +select * from v1; +a +2 +1 +drop view v1; +# +# MDEV-22560 Crash on a table value constructor with an SP variable +# +BEGIN NOT ATOMIC +DECLARE a INT DEFAULT 0; +VALUES (a) UNION SELECT 1; +END; +$$ +a +0 +1 +# +# MDEV-21995 Server crashes in Item_field::real_type_handler with table value constructor +# +VALUES (IGNORE); +ERROR HY000: 'ignore' is not allowed in this context +VALUES (DEFAULT); +ERROR HY000: 'default' is not allowed in this context +EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; +ERROR HY000: 'ignore' is not allowed in this context +EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; +ERROR HY000: 'default' is not allowed in this context +# +# MDEV-24675: TVC using subqueries +# +values((select 1)); +(select 1) +1 +values (2), ((select 1)); +2 +2 +1 +values ((select 1)), (2), ((select 3)); +(select 1) +1 +2 +3 +values ((select 1), 2), (3,4), (5, (select 6)); +(select 1) 2 +1 2 +3 4 +5 6 +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3), (3,2), (1,2); +values((select max(a) from t1)); +(select max(a) from t1) +3 +values((select min(b) from t1)); +(select min(b) from t1) +2 +values ((select max(a) from t1), (select min(b) from t1)); +(select max(a) from t1) (select min(b) from t1) +3 2 +values((select * from (select max(b) from t1) as t)); +(select * from (select max(b) from t1) as t) +3 +drop table t1; +# +# MDEV-24618: TVC contains extra parenthesis for row expressions +# in value list +# +create table t1 (a int, b int); +insert into t1 values (1,3), (2,3); +insert into t1 values ((5,4)); +ERROR 21000: Operand should contain 1 column(s) +values ((1,2)); +ERROR 21000: Operand should contain 1 column(s) +select * from (values ((1,2))) dt; +ERROR 21000: Operand should contain 1 column(s) +values (1,2); +1 2 +1 2 +values ((select min(a), max(b) from t1)); +ERROR 21000: Operand should contain 1 column(s) +drop table t1; +# +# MDEV-24840: union of TVCs in IN subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +select a from t1 where a in (values (7) union values (8)); +a +7 +explain extended select a from t1 where a in (values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 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 +5 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 <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`8`))) +prepare stmt from "select a from t1 where a in (values (7) union values (8))"; +execute stmt; +a +7 +execute stmt; +a +7 +deallocate prepare stmt; +select a from t1 where a not in (values (7) union values (8)); +a +3 +1 +explain extended select a from t1 where a not in (values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 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 +5 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 <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`8`)))) +select a from t1 where a < all(values (7) union values (8)); +a +3 +1 +explain extended select a from t1 where a < all(values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 where a >= any(values (7) union values (8)); +a +7 +explain extended select a from t1 where a >= any(values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) +drop table t1; +# +# MDEV-24934:EXPLAIN for queries based on TVC using subqueries +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +values (8), ((select * from t1 where a between 2 and 4)); +8 +8 +3 +explain values (8), ((select * from t1 where a between 2 and 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 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +(select * from t1 where a between 2 and 4) +3 +NULL +explain values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +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 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values (10,11), ((select * from t1 where a = 7) + 1, 21); +10 11 +10 11 +8 21 +explain values (10,11), ((select * from t1 where a = 7) + 1, 21); +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 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +drop table t1; +# +# MDEV-24910: TVC containing subquery used as a subselect +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); +select (values ((select 2))) from t2; +(values ((select 2))) +2 +2 +explain select (values ((select 2))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 3 was reduced during optimization +prepare stmt from "select (values ((select 2))) from t2"; +execute stmt; +(values ((select 2))) +2 +2 +execute stmt; +(values ((select 2))) +2 +2 +deallocate prepare stmt; +select (values ((select * from t1 where a > 10))) from t2; +(values ((select * from t1 where a > 10))) +NULL +NULL +explain select (values ((select * from t1 where a > 10))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +prepare stmt from "select (values ((select * from t1 where a > 10))) from t2"; +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +deallocate prepare stmt; +create table t3 (a int); +insert into t3 values +(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); +create view v1 as select count(a) as c from t3 group by a; +select +(values ((select * from t3 where a in (select * from v1)))); +(values ((select * from t3 where a in (select * from v1)))) +1 +explain select +(values ((select * from t3 where a in (select * from v1)))); +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 +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 Using where +3 SUBQUERY <derived5> ref key0 key0 8 test.t3.a 2 Using where; FirstMatch(t3) +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 where a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +deallocate prepare stmt; +select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +explain select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +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 +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 Using where +3 SUBQUERY <derived5> ref key0 key0 8 test.t3.a 2 Using where; FirstMatch(t3) +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 +where a > 10 and a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +deallocate prepare stmt; +drop view v1; +drop table t1,t2,t3; +# +# MDEV-24919: subselect formed by TVC and used in set function +# +select sum((values(1))); +sum((values(1))) +1 +# +# MDEV-22786: Nested table values constructors +# +values ((values (2))); +(values (2)) +2 +values ((values (2)), (5), (select 4)); +(values (2)) 5 (select 4) +2 5 4 +values ((7), (values (2)), (5), (select 4)); +7 (values (2)) 5 (select 4) +7 2 5 4 +values ((values (2))) union values ((values (3))); +(values (2)) +2 +3 +values ((values (2))), ((values (3))); +(values (2)) +2 +3 +values ((values (2))), ((select 4)), ((values (3))); +(values (2)) +2 +4 +3 +values ((values (4)), (values (5))), ((values (1)), (values (7))); +(values (4)) (values (5)) +4 5 +1 7 +values ((values (4)), (select 5)), ((select 1), (values (7))); +(values (4)) (select 5) +4 5 +1 7 +values ((select 2)) union values ((values (3))); +(select 2) +2 +3 +values ((values (2))) union values((select 3)); +(values (2)) +2 +3 +values ((values (2))) union all values ((values (2))); +(values (2)) +2 +2 +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union +values ((values (4)), (select 5)), ((select 2), (values (8))); +(values (4)) (values (5)) +4 5 +1 7 +2 8 +values ((values (4)), (values (5))), ((values (1)), (values (7))) +union all +values ((values (4)), (select 5)), ((select 2), (values (8))); +(values (4)) (values (5)) +4 5 +1 7 +4 5 +2 8 +values ((values (1) union values (1))); +(values (1) union values (1)) +1 +values ((values (1) union values (1) union values (1))); +(values (1) union values (1) union values (1)) +1 +values ((values ((values (4))))); +(values ((values (4)))) +4 +values ((values ((select 5)))); +(values ((select 5))) +5 +values ((select (values (4))), (values ((values(5))))); +(select (values (4))) (values ((values(5)))) +4 5 +values ((select (values (4))), (values ((select 5)))); +(select (values (4))) (values ((select 5))) +4 5 +values ((select (values (4))), (values ((values(5))))) +union +values ((select (values (4))), (values ((select 7)))); +(select (values (4))) (values ((values(5)))) +4 5 +4 7 +values ((values (2))), ((values ((values (4))))); +(values (2)) +2 +4 +values ((values (2))), ((values ((select 4)))); +(values (2)) +2 +4 +values ((values (2))), ((values ((values (4))))) +union +values ((values (8))), ((values ((select 4)))); +(values (2)) +2 +4 +8 +values ((values (2))), ((values ((values (4))))) +union all +values ((values (8))), ((values ((select 4)))); +(values (2)) +2 +4 +8 +4 +select * from (values ((values (2)))) dt; +(values (2)) +2 +select * from (values ((values (2)), (5), (select 4))) dt; +(values (2)) 5 (select 4) +2 5 4 +select * from (values ((values (2))) union values ((values (3)))) dt; +(values (2)) +2 +3 +select * from (values ((values (2))), ((values (3)))) dt; +(values (2)) +2 +3 +select * from (values ((values (2))), ((values (3)))) dt; +(values (2)) +2 +3 +select * from (values ((values (2))), ((select 4)), ((values (3)))) dt; +(values (2)) +2 +4 +3 +create table t1 (a int); +insert into t1 values (3), (7), (1); +values ((values ((select a from t1 where a=7)))); +(values ((select a from t1 where a=7))) +7 +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select (values(2)) from t1 where a=8))) +NULL +values ((values ((select a from t1 where a=7)))) +union +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select a from t1 where a=7))) +7 +NULL +values ((values ((select a from t1 where a in ((values (7))))))); +(values ((select a from t1 where a in ((values (7)))))) +7 +values ((values ((select a from t1 where a in ((values (7), (8))))))); +(values ((select a from t1 where a in ((values (7), (8)))))) +7 +values ((values +((select a from t1 where a in (values (7) union values (8)))))); +(values +((select a from t1 where a in (values (7) union values (8))))) +7 +values ((values ((select (values(2)) from t1 where a=8)))); +(values ((select (values(2)) from t1 where a=8))) +NULL +values ((select (values(2)) from t1 where a<7)); +ERROR 21000: Subquery returns more than 1 row +select * from (values ((values ((select a from t1 where a=7))))) dt; +(values ((select a from t1 where a=7))) +7 +select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; +(values ((select (values(2)) from t1 where a=8))) +NULL +insert into t1(a) values ((values (2))), ((values (3))); +select * from t1; +a +3 +7 +1 +2 +3 +drop table t1; +# +# MDEV-25484: Derived table using TVC with LIMIT and ORDER BY +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +select * from ( (select * from t1 limit 2) order by 1 desc) as dt; +a +3 +7 +(values (3), (7), (1) limit 2) order by 1 desc; +3 +7 +3 +select * from ( (values (3), (7), (1) limit 2) order by 1 desc) as dt; +3 +3 +7 +select * from ( select * from t1 order by 1 limit 2 ) as dt; +a +1 +3 +values (3),(7),(1) order by 1 limit 2; +3 +1 +3 +select * from ( values (3),(7),(1) order by 1 limit 2 ) as dt; +3 +1 +3 +values (3),(7),(1) union values (2),(4) order by 1 limit 2; +3 +1 +2 +select * from (values (3),(7),(1) union values (2),(4) order by 1 limit 2) as dt; +3 +1 +2 +drop table t1; +# +# MDEV-23182: Server crashes in +# Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS +# +SET @save_in_predicate_conversion_threshold=@@in_predicate_conversion_threshold; +SET in_predicate_conversion_threshold=2; +CREATE TABLE t1 (c VARCHAR(10)) DEFAULT CHARSET=utf8; +PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; +EXECUTE stmt; +c +# Without the patch second execution of the prepared statement 'stmt' +# results in crash. +EXECUTE stmt; +c +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +# Check that the query without conversion doesn't crash server +CREATE TABLE t1 (c VARCHAR(10)); +PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; +EXECUTE stmt; +c +EXECUTE stmt; +c +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +# Test case for a row expression in the left part of the IN clause +CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)) DEFAULT CHARSET=utf8; +PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +DROP TABLE t1; +# Check that the query without conversion is handled successfully +CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)); +PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +DROP TABLE t1; +SET @@in_predicate_conversion_threshold = @save_in_predicate_conversion_threshold; +End of 10.3 tests +# +# MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)) +# +VALUES (DEFAULT) UNION VALUES (DEFAULT); +ERROR HY000: 'default' is not allowed in this context +VALUES (IGNORE) UNION VALUES (IGNORE); +ERROR HY000: 'ignore' is not allowed in this context +CREATE TABLE t1 (a INT DEFAULT 10); +INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)); +ERROR HY000: 'default' is not allowed in this context +INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); +ERROR HY000: 'ignore' is not allowed in this context +DROP TABLE t1; +# +# MDEV-28603: VIEW with table value constructor used as single-value +# subquery contains subquery as its first element +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create table t2 (b int); +insert into t2 values (1), (2); +create view v as select (values ((select * from t1 where a > 5))) as m from t2; +select (values ((select * from t1 where a > 5))) as m from t2; +m +7 +7 +select * from v; +m +7 +7 +with cte as ( select (values ((select * from t1 where a > 5))) as m from t2 ) select * from cte; +m +7 +7 +explain select (values ((select * from t1 where a > 5))) as m from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +explain select * from v; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +5 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +explain with cte as ( select (values ((select * from t1 where a > 5))) as m from t2 ) select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +5 SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +prepare stmt from "select (values ((select * from t1 where a > 5))) as m from t2"; +execute stmt; +m +7 +7 +execute stmt; +m +7 +7 +deallocate prepare stmt; +prepare stmt from "select * from v"; +execute stmt; +m +7 +7 +execute stmt; +m +7 +7 +deallocate prepare stmt; +prepare stmt from "with cte as ( select (values ((select * from t1 where a > 5))) as m from t2 ) select * from cte"; +execute stmt; +m +7 +7 +execute stmt; +m +7 +7 +deallocate prepare stmt; +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +drop view v; +prepare stmt from "create view v as select (values ((select * from t1 where a > 5))) as m from t2"; +execute stmt; +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +select * from v; +m +7 +7 +drop view v; +execute stmt; +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +select * from v; +m +7 +7 +deallocate prepare stmt; +prepare stmt from "show create view v"; +execute stmt; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +execute stmt; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5))) AS `m` from `t2` latin1 latin1_swedish_ci +deallocate prepare stmt; +drop view v; +create view v as select (values ((select * from t1 where a > 5 +union +select * from t1 where a > 7))) as m from t2; +select (values ((select * from t1 where a > 5 +union +select * from t1 where a > 7))) as m from t2; +m +7 +7 +select * from v; +m +7 +7 +show create view v; +View Create View character_set_client collation_connection +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (values ((select `t1`.`a` from `t1` where `t1`.`a` > 5 union select `t1`.`a` from `t1` where `t1`.`a` > 7))) AS `m` from `t2` latin1 latin1_swedish_ci +drop view v; +drop table t1,t2; +# +# End of 10.4 tests +# |