mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-04 12:56:14 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/table_value_constr.result
Marko Mäkelä 8ec12e0d6d Merge 10.4 into 10.5
2024-02-12 11:38:13 +02:00

3254 lines
89 KiB
Text
Raw Permalink Blame History

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_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_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": {
"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": {
"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": {
"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: Default/ignore value is not supported for such parameter usage
EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT;
ERROR HY000: Default/ignore value is not supported for such parameter usage
#
# 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
#
Reference in a new issue View git blame Copy permalink