mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2026-01-31 07:49:09 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/table_value_constr.result
Rex 9c01f28c41 MDEV-30073 Wrong result on 2nd execution of PS for query with NOT EXISTS 
This bug is consequence of a serious architectural flaw of the query
processing at the prepare stage. Context analysis of processed queries has
to be done at this stage. The important part of this analysis is resolution
of column references also known as name resolution. The items for column
references used in a query are created by the parser. They are allocated
in the statement memory because their life span is the same as the life
span of the executed query. During name resolution some of the items are
wrapped into objects belonging to classes derived from the Item_ref class.
In many cases we can't do without such wrappers. For example, the parser
does not differentiate between references to columns of base tables and
references to columns of views. However for a reference to a view column
we need a pointer to the translation item for this column. This pointer
is stored in a Item_direct_view_ref object that wraps the item for the
column reference. Before this patch the wrappers were allocated in
execution memory good only for one execution. That wasn't a problem when
the query was executed only once. Yet for the queries executed as prepared
statements or within a stored procedure it could lead to crashes or wrong
result sets at the second execution of the query. It could happen when
the wrapped item was substituted for something else by permanent
transformations during the optimization phase.

This patch allocates all the wrappers around items created for column
references in the statement memory at the first execution of PS or at the
first call of SP/SF.
2025-04-14 09:21:31 +11:00

3352 lines
92 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 1 2
1 1 3
1 1 3
1 2 2
1 2 3
1 2 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 Using where
1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 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 (values (1)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
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 Using where
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 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 (values (1)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
# 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> eq_ref distinct_key distinct_key 4 func 1 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> eq_ref distinct_key distinct_key 4 func 1 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> eq_ref distinct_key distinct_key 4 func 1 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 1 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> eq_ref distinct_key distinct_key 4 func 1 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> eq_ref distinct_key distinct_key 4 func 1 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 DEPENDENT SUBQUERY <derived2> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
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`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in <temporary table> on distinct_key where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`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 DEPENDENT SUBQUERY <derived3> unique_subquery distinct_key distinct_key 4 func 1 100.00 Using where; Full scan on NULL key
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`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in <temporary table> on distinct_key where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`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 Using where
1 PRIMARY <derived2> eq_ref distinct_key distinct_key 4 test.t1.a 1 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 (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
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 Using where
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 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 (values (1),(2)) `tvc_0` join `test`.`t1` where `tvc_0`.`1` = `test`.`t1`.`a`
# 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> eq_ref distinct_key distinct_key 4 func 1 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> eq_ref distinct_key distinct_key 4 func 1 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> eq_ref distinct_key distinct_key 4 func 1 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 1 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> eq_ref distinct_key distinct_key 4 func 1 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 1 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 BY'
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 BY'
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 BY'
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 BY'
#
# 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> eq_ref distinct_key distinct_key 4 func 1 100.00
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
5 DEPENDENT UNION <derived3> eq_ref distinct_key distinct_key 4 func 1 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 1 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 1 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 all values (1)));
ERROR 21000: Subquery returns more than 1 row
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;
#
# MDEV-31178: 2 execution of query with conversion from
# IN predicate to IN subquery applied
#
CREATE TABLE t1 (a bigint);
INSERT INTO t1 VALUES (1), (2), (3);
SET in_predicate_conversion_threshold=1;
# IN predicate is transformed into IN subquery
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,5,3);
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
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 4 test.t1.a 1 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` from (values (1),(5),(3)) `tvc_0` join `test`.`t1` where `test`.`t1`.`a` = `tvc_0`.`_col_1`
SELECT * FROM t1 WHERE a IN (1,5,3);
a
1
3
PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN (1,5,3)";
EXECUTE stmt;
a
1
3
EXECUTE stmt;
a
1
3
DEALLOCATE PREPARE stmt;
# here IN predicate is also transformed into IN subquery
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN ('1','5','3');
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
1 PRIMARY <derived3> eq_ref distinct_key distinct_key 8 test.t1.a 1 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` from (values (1),(5),(3)) `tvc_0` join `test`.`t1` where `tvc_0`.`_col_1` = `test`.`t1`.`a`
SELECT * FROM t1 WHERE a IN ('1','5','3');
a
1
3
PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN ('1','5','3')";
EXECUTE stmt;
a
1
3
EXECUTE stmt;
a
1
3
DEALLOCATE PREPARE stmt;
# and here IN predicate is not transformed into IN subquery
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN ('1','1.7','3');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` in (1,'1.7',3)
SELECT * FROM t1 WHERE a IN ('1','5','3');
a
1
3
PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN ('1','1.7','3')";
EXECUTE stmt;
a
1
3
EXECUTE stmt;
a
1
3
DEALLOCATE PREPARE stmt;
SET in_predicate_conversion_threshold=default;
DROP TABLE t1;
#
# End of 10.4 tests
#
Reference in a new issue View git blame Copy permalink