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 eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 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 eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); a b 1 2 1 1 2 5 select * from t1 where a in (select * from (values (1)) as tvc_0 union select 2); a b 1 2 1 1 2 5 explain extended select * from t1 where a in (values (1) union select 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 4 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having (`test`.`t1`.`a`) = (2)))) explain extended select * from t1 where a in (select * from (values (1)) as tvc_0 union select 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having (`test`.`t1`.`a`) = (2)))) # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a in (select 2 union values (1)); a b 1 2 1 1 2 5 select * from t1 where a in (select 2 union select * from (values (1)) tvc_0); a b 1 2 1 1 2 5 explain extended select * from t1 where a in (select 2 union values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 2 having (`test`.`t1`.`a`) = (2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1`))) explain extended select * from t1 where a in (select 2 union select * from (values (1)) tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION ref key0 key0 4 func 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 2 having (`test`.`t1`.`a`) = (2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1`))) # IN-subquery with VALUES structure(s) : UNION ALL select * from t1 where a in (values (1) union all select b from t1); a b 1 2 1 1 2 5 7 8 select * from t1 where a in (select * from (values (1)) as tvc_0 union all select b from t1); a b 1 2 1 1 2 5 7 8 explain extended select * from t1 where a in (values (1) union all select b from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 4 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where (`test`.`t1`.`a`) = `test`.`t1`.`b`))) explain extended select * from t1 where a in (select * from (values (1)) as tvc_0 union all select b from t1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where (`test`.`t1`.`a`) = `test`.`t1`.`b`))) # NOT IN subquery with VALUES structure(s) : simple case select * from t1 where a not in (values (1),(2)); a b 4 6 9 7 7 8 select * from t1 where a not in (select * from (values (1),(2)) as tvc_0); a b 4 6 9 7 7 8 explain extended select * from t1 where a not in (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 3 MATERIALIZED ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,`test`.`t1`.`a` in ( (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), (`test`.`t1`.`a` in on distinct_key where `test`.`t1`.`a` = ``.`1`)))) explain extended select * from t1 where a not in (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 MATERIALIZED ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,`test`.`t1`.`a` in ( (/* select#2 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), (`test`.`t1`.`a` in on distinct_key where `test`.`t1`.`a` = ``.`1`)))) # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a not in (values (1) union select 2); a b 4 6 9 7 7 8 select * from t1 where a not in (select * from (values (1)) as tvc_0 union select 2); a b 4 6 9 7 7 8 explain extended select * from t1 where a not in (values (1) union select 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 4 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 100.00 Using where 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#3 */ select 2 having trigcond((`test`.`t1`.`a`) = (2))))) explain extended select * from t1 where a not in (select * from (values (1)) as tvc_0 union select 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 100.00 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#4 */ select 2 having trigcond((`test`.`t1`.`a`) = (2))))) # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a not in (select 2 union values (1)); a b 4 6 9 7 7 8 select * from t1 where a not in (select 2 union select * from (values (1)) as tvc_0); a b 4 6 9 7 7 8 explain extended select * from t1 where a not in (select 2 union values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION ALL NULL NULL NULL NULL 2 100.00 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 2 having trigcond((`test`.`t1`.`a`) = (2)) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) = `tvc_0`.`1`)))) explain extended select * from t1 where a not in (select 2 union select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION ALL NULL NULL NULL NULL 2 100.00 Using where 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 2 having trigcond((`test`.`t1`.`a`) = (2)) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) = `tvc_0`.`1`)))) # ANY-subquery with VALUES structure(s) : simple case select * from t1 where a = any (values (1),(2)); a b 1 2 1 1 2 5 select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); a b 1 2 1 1 2 5 explain extended select * from t1 where a = any (values (1),(2)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 100.00 3 MATERIALIZED ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` 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 eq_ref distinct_key distinct_key 4 func 1 100.00 2 MATERIALIZED ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); a b 1 2 1 1 2 5 select * from t1 where a = any (select * from (values (1)) as tvc_0 union select 2); a b 1 2 1 1 2 5 explain extended select * from t1 where a = any (values (1) union select 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 4 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having (`test`.`t1`.`a`) = (2)))) explain extended select * from t1 where a = any (select * from (values (1)) as tvc_0 union select 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having (`test`.`t1`.`a`) = (2)))) # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a = any (select 2 union values (1)); a b 1 2 1 1 2 5 select * from t1 where a = any (select 2 union select * from (values (1)) as tvc_0); a b 1 2 1 1 2 5 explain extended select * from t1 where a = any (select 2 union values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 2 having (`test`.`t1`.`a`) = (2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1`))) explain extended select * from t1 where a = any (select 2 union select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION ref key0 key0 4 func 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 2 having (`test`.`t1`.`a`) = (2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1`))) # ALL-subquery with VALUES structure(s) : simple case select * from t1 where a = all (values (1)); a b 1 2 1 1 select * from t1 where a = all (select * from (values (1)) as tvc_0); a b 1 2 1 1 explain extended select * from t1 where a = all (values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 3 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 100.00 Using where 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) <> `tvc_0`.`1`))))) explain extended select * from t1 where a = all (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 100.00 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) <> `tvc_0`.`1`))))) # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = all (values (1) union select 1); a b 1 2 1 1 select * from t1 where a = all (select * from (values (1)) as tvc_0 union select 1); a b 1 2 1 1 explain extended select * from t1 where a = all (values (1) union select 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 4 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 100.00 Using where 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#3 */ select 1 having trigcond((`test`.`t1`.`a`) <> (1)))))) explain extended select * from t1 where a = all (select * from (values (1)) as tvc_0 union select 1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY ALL NULL NULL NULL NULL 2 100.00 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond((`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#4 */ select 1 having trigcond((`test`.`t1`.`a`) <> (1)))))) # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a = any (select 1 union values (1)); a b 1 2 1 1 select * from t1 where a = any (select 1 union select * from (values (1)) as tvc_0); a b 1 2 1 1 explain extended select * from t1 where a = any (select 1 union values (1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 1 having (`test`.`t1`.`a`) = (1) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1`))) explain extended select * from t1 where a = any (select 1 union select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION ref key0 key0 4 func 2 100.00 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#2 */ select 1 having (`test`.`t1`.`a`) = (1) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`1`))) # prepare statement that uses VALUES structure(s): single VALUES structure prepare stmt1 from " values (1,2); "; execute stmt1; 1 2 1 2 execute stmt1; 1 2 1 2 deallocate prepare stmt1; # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) prepare stmt1 from " select 1,2 union values (1,2),(3,4); "; execute stmt1; 1 2 1 2 3 4 execute stmt1; 1 2 1 2 3 4 deallocate prepare stmt1; prepare stmt1 from " values (1,2),(3,4) union select 1,2; "; execute stmt1; 1 2 1 2 3 4 execute stmt1; 1 2 1 2 3 4 deallocate prepare stmt1; prepare stmt1 from " select 1,2 union values (3,4) union values (1,2); "; execute stmt1; 1 2 1 2 3 4 execute stmt1; 1 2 1 2 3 4 deallocate prepare stmt1; prepare stmt1 from " values (5,6) union values (1,2),(3,4); "; execute stmt1; 5 6 5 6 1 2 3 4 execute stmt1; 5 6 5 6 1 2 3 4 deallocate prepare stmt1; # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) prepare stmt1 from " select 1,2 union values (1,2),(3,4); "; execute stmt1; 1 2 1 2 3 4 execute stmt1; 1 2 1 2 3 4 deallocate prepare stmt1; prepare stmt1 from " values (1,2),(3,4) union all select 1,2; "; execute stmt1; 1 2 1 2 3 4 1 2 execute stmt1; 1 2 1 2 3 4 1 2 deallocate prepare stmt1; prepare stmt1 from " select 1,2 union all values (3,4) union all values (1,2); "; execute stmt1; 1 2 1 2 3 4 1 2 execute stmt1; 1 2 1 2 3 4 1 2 deallocate prepare stmt1; prepare stmt1 from " values (1,2) union all values (1,2),(3,4); "; execute stmt1; 1 2 1 2 1 2 3 4 execute stmt1; 1 2 1 2 1 2 3 4 deallocate prepare stmt1; # explain query that uses VALUES structure(s): single VALUES structure explain values (1,2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used explain format=json values (1,2); EXPLAIN { "query_block": { "union_result": { "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } } ] } } } # explain query that uses VALUES structure(s): UNION with VALUES structure(s) explain select 1,2 union values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain values (1,2),(3,4) union select 1,2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain values (5,6) union values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain format=json select 1,2 union values (1,2),(3,4); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } explain format=json values (1,2),(3,4) union select 1,2; EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } explain format=json values (5,6) union values (1,2),(3,4); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } explain select 1,2 union values (3,4) union values (1,2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain format=json select 1,2 union values (3,4) union values (1,2); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 3, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) explain select 1,2 union values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain values (1,2),(3,4) union all select 1,2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used explain values (1,2) union all values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used explain format=json values (1,2),(3,4) union all select 1,2; EXPLAIN { "query_block": { "union_result": { "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } explain format=json select 1,2 union values (1,2),(3,4); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } explain format=json values (1,2) union all values (1,2),(3,4); EXPLAIN { "query_block": { "union_result": { "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 ALL NULL NULL NULL NULL NULL 2.00 NULL NULL analyze values (1,2),(3,4) union select 1,2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL 2.00 NULL NULL analyze values (5,6) union values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL 3.00 NULL NULL analyze format=json select 1,2 union values (1,2),(3,4); ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 2, "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } analyze format=json values (1,2),(3,4) union select 1,2; ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 2, "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } analyze format=json values (5,6) union values (1,2),(3,4); ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 3, "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } analyze select 1,2 union values (3,4) union values (1,2); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL 2.00 NULL NULL analyze format=json select 1,2 union values (3,4) union values (1,2); ANALYZE { "query_optimization": { "r_total_time_ms": "REPLACED" }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 2, "query_specifications": [ { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "message": "No tables used" } } }, { "query_block": { "select_id": 3, "operation": "UNION", "table": { "message": "No tables used" } } } ] } } } # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) analyze select 1,2 union values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL 2.00 NULL NULL analyze values (1,2),(3,4) union all select 1,2; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used analyze values (1,2) union all values (1,2),(3,4); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used analyze format=json values (1,2),(3,4) union all select 1,2; ANALYZE { "query_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": "", "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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 limit 1,2 values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; ERROR 42S22: Unknown column '3' in 'order clause' prepare stmt from " select 2 union (values (5), (7), (1), (3), (4) limit 2) "; execute stmt; 2 2 5 7 execute stmt; 2 2 5 7 deallocate prepare stmt; prepare stmt from " select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) "; execute stmt; 2 2 1 3 execute stmt; 2 2 1 3 deallocate prepare stmt; prepare stmt from " select 3 union all (values (5), (7), (1), (3), (4) limit 2) "; execute stmt; 3 3 5 7 execute stmt; 3 3 5 7 deallocate prepare stmt; prepare stmt from " select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) "; execute stmt; 3 3 1 3 execute stmt; 3 3 1 3 deallocate prepare stmt; prepare stmt from " ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) union ( values (5), (7), (1), (3), (4) order by 1 limit 2 ); "; execute stmt; 5 7 1 3 execute stmt; 5 7 1 3 deallocate prepare stmt; prepare stmt from " values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; "; ERROR 42S22: Unknown column '3' in 'order clause' create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS values (5),(7),(1),(3),(4) order by 1 limit 2 latin1 latin1_swedish_ci select * from v1; 5 1 3 drop view v1; create view v1 as ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) union ( values (5), (7), (1), (3), (4) order by 1 limit 2 ); show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS (values (5),(7),(1),(3),(4) limit 1,2) union (values (5),(7),(1),(3),(4) order by 1 limit 2) latin1 latin1_swedish_ci select * from v1; 5 7 1 3 drop view v1; create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; ERROR 42S22: Unknown column '3' in 'order clause' create view v1 as ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) union ( values (5), (7), (1), (3), (4) order by 2 limit 2 ); ERROR 42S22: Unknown column '2' in 'order clause' # # MDEV-20229: view defined as select using # CTE with named columns defined as TVC # create view v1 as with t(a) as (values (2), (1)) select a from t; show create view v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(`a`) as (values (2),(1))select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 2 1 drop view v1; # # MDEV-22560 Crash on a table value constructor with an SP variable # BEGIN NOT ATOMIC DECLARE a INT DEFAULT 0; VALUES (a) UNION SELECT 1; END; $$ a 0 1 # # MDEV-21995 Server crashes in Item_field::real_type_handler with table value constructor # VALUES (IGNORE); ERROR HY000: 'ignore' is not allowed in this context VALUES (DEFAULT); ERROR HY000: 'default' is not allowed in this context EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; ERROR HY000: 'ignore' is not allowed in this context EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; ERROR HY000: 'default' is not allowed in this context # # MDEV-24675: TVC using subqueries # values((select 1)); (select 1) 1 values (2), ((select 1)); 2 2 1 values ((select 1)), (2), ((select 3)); (select 1) 1 2 3 values ((select 1), 2), (3,4), (5, (select 6)); (select 1) 2 1 2 3 4 5 6 create table t1 (a int, b int); insert into t1 values (1,3), (2,3), (3,2), (1,2); values((select max(a) from t1)); (select max(a) from t1) 3 values((select min(b) from t1)); (select min(b) from t1) 2 values ((select max(a) from t1), (select min(b) from t1)); (select max(a) from t1) (select min(b) from t1) 3 2 values((select * from (select max(b) from t1) as t)); (select * from (select max(b) from t1) as t) 3 drop table t1; # # MDEV-24618: TVC contains extra parenthesis for row expressions # in value list # create table t1 (a int, b int); insert into t1 values (1,3), (2,3); insert into t1 values ((5,4)); ERROR 21000: Operand should contain 1 column(s) values ((1,2)); ERROR 21000: Operand should contain 1 column(s) select * from (values ((1,2))) dt; ERROR 21000: Operand should contain 1 column(s) values (1,2); 1 2 1 2 values ((select min(a), max(b) from t1)); ERROR 21000: Operand should contain 1 column(s) drop table t1; # # MDEV-24840: union of TVCs in IN subquery # create table t1 (a int) engine=myisam; insert into t1 values (3), (7), (1); select a from t1 where a in (values (7) union values (8)); a 7 explain extended select a from t1 where a in (values (7) union values (8)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 4 DEPENDENT SUBQUERY ref key0 key0 4 func 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5 DEPENDENT UNION ref key0 key0 4 func 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where (`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where (`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 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 ALL NULL NULL NULL NULL 2 100.00 Using where 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<`test`.`t1`.`a`>((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond((`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond((`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 ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5 UNION ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= (`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 ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 5 UNION ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a`,(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= (`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 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 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 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 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 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 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 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 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 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 #