create table t1 (a int, b int); insert into t1 values (1,2),(4,6),(9,7), (1,1),(2,5),(7,8); --echo # just VALUES values (1,2); values (1,2), (3,4), (5.6,0); values ("abc", "def"); --echo # UNION that uses VALUES structure(s) select 1,2 union values (1,2); values (1,2) union select 1,2; select 1,2 union values (1,2),(3,4),(5,6),(7,8); select 3,7 union values (1,2),(3,4),(5,6); select 3,7,4 union values (1,2,5),(4,5,6); select 1,2 union values (1,7),(3,6.5); select 1,2 union values (1,2.0),(3,6); select 1.8,2 union values (1,2),(3,6); values (1,2.4),(3,6) union select 2.8,9; values (1,2),(3,4),(5,6),(7,8) union select 5,6; select "ab","cdf" union values ("al","zl"),("we","q"); values ("ab", "cdf") union select "ab","cdf"; values (1,2) union values (1,2),(5,6); values (1,2) union values (3,4),(5,6); values (1,2) union values (1,2) union values (4,5); --echo # UNION ALL that uses VALUES structure values (1,2),(3,4) union all select 5,6; values (1,2),(3,4) union all select 1,2; select 5,6 union all values (1,2),(3,4); select 1,2 union all values (1,2),(3,4); values (1,2) union all values (1,2),(5,6); values (1,2) union all values (3,4),(5,6); values (1,2) union all values (1,2) union all values (4,5); values (1,2) union all values (1,2) union values (1,2); values (1,2) union values (1,2) union all values (1,2); --echo # EXCEPT that uses VALUES structure(s) select 1,2 except values (3,4),(5,6); select 1,2 except values (1,2),(3,4); values (1,2),(3,4) except select 5,6; values (1,2),(3,4) except select 1,2; values (1,2),(3,4) except values (5,6); values (1,2),(3,4) except values (1,2); --echo # INTERSECT that uses VALUES structure(s) select 1,2 intersect values (3,4),(5,6); select 1,2 intersect values (1,2),(3,4); values (1,2),(3,4) intersect select 5,6; values (1,2),(3,4) intersect select 1,2; values (1,2),(3,4) intersect values (5,6); values (1,2),(3,4) intersect values (1,2); --echo # combination of different structures that uses VALUES structures : UNION + EXCEPT values (1,2),(3,4) except select 1,2 union values (1,2); values (1,2),(3,4) except values (1,2) union values (1,2); values (1,2),(3,4) except values (1,2) union values (3,4); values (1,2),(3,4) union values (1,2) except values (1,2); --echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT values (1,2),(3,4) except select 1,2 union all values (1,2); values (1,2),(3,4) except values (1,2) union all values (1,2); values (1,2),(3,4) except values (1,2) union all values (3,4); values (1,2),(3,4) union all values (1,2) except values (1,2); --echo # combination of different structures that uses VALUES structures : UNION + INTERSECT values (1,2),(3,4) intersect select 1,2 union values (1,2); values (1,2),(3,4) intersect values (1,2) union values (1,2); values (1,2),(3,4) intersect values (1,2) union values (3,4); values (1,2),(3,4) union values (1,2) intersect values (1,2); --echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT values (1,2),(3,4) intersect select 1,2 union all values (1,2); values (1,2),(3,4) intersect values (1,2) union all values (1,2); values (1,2),(3,4) intersect values (1,2) union all values (3,4); values (1,2),(3,4) union all values (1,2) intersect values (1,2); --echo # combination of different structures that uses VALUES structures : UNION + UNION ALL values (1,2),(3,4) union all select 1,2 union values (1,2); values (1,2),(3,4) union all values (1,2) union values (1,2); values (1,2),(3,4) union all values (1,2) union values (3,4); values (1,2),(3,4) union values (1,2) union all values (1,2); values (1,2) union values (1,2) union all values (1,2); --echo # CTE that uses VALUES structure(s) : non-recursive CTE with t2 as ( values (1,2),(3,4) ) select * from t2; with t2 as ( select 1,2 union values (1,2) ) select * from t2; with t2 as ( select 1,2 union values (1,2),(3,4) ) select * from t2; with t2 as ( values (1,2) union select 1,2 ) select * from t2; with t2 as ( values (1,2),(3,4) union select 1,2 ) select * from t2; with t2 as ( values (5,6) union values (1,2),(3,4) ) select * from t2; with t2 as ( values (1,2) union values (1,2),(3,4) ) select * from t2; with t2 as ( select 1,2 union all values (1,2),(3,4) ) select * from t2; with t2 as ( values (1,2),(3,4) union all select 1,2 ) select * from t2; with t2 as ( values (1,2) union all values (1,2),(3,4) ) select * from t2; --echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor with recursive t2(a,b) as ( values(1,1) union select t1.a, t1.b from t1,t2 where t1.a=t2.a ) select * from t2; with recursive t2(a,b) as ( values(1,1) union select t1.a+1, t1.b from t1,t2 where t1.a=t2.a ) select * from t2; --echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors with recursive t2(a,b) as ( values(1,1) union values (3,4) union select t2.a+1, t1.b from t1,t2 where t1.a=t2.a ) select * from t2; --echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL with recursive t2(a,b,st) as ( values(1,1,1) union all select t2.a, t1.b, t2.st+1 from t1,t2 where t1.a=t2.a and st<3 ) select * from t2; --echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements) with recursive fact(n,f) as ( values(1,1) union select n+1,f*n from fact where n < 10 ) select * from fact; --echo # Derived table that uses VALUES structure(s) : singe VALUES structure select * from (values (1,2),(3,4)) as t2; --echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) select * from (select 1,2 union values (1,2)) as t2; select * from (select 1,2 union values (1,2),(3,4)) as t2; select * from (values (1,2) union select 1,2) as t2; select * from (values (1,2),(3,4) union select 1,2) as t2; select * from (values (5,6) union values (1,2),(3,4)) as t2; select * from (values (1,2) union values (1,2),(3,4)) as t2; --echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) select * from (select 1,2 union all values (1,2),(3,4)) as t2; select * from (values (1,2),(3,4) union all select 1,2) as t2; select * from (values (1,2) union all values (1,2),(3,4)) as t2; --echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure let $drop_view= drop view v1; let $select_view= select * from v1; create view v1 as values (1,2),(3,4); eval $select_view; eval $drop_view; --echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s) create view v1 as select 1,2 union values (1,2); eval $select_view; eval $drop_view; create view v1 as select 1,2 union values (1,2),(3,4); eval $select_view; eval $drop_view; create view v1 as values (1,2) union select 1,2; eval $select_view; eval $drop_view; create view v1 as values (1,2),(3,4) union select 1,2; eval $select_view; eval $drop_view; create view v1 as values (5,6) union values (1,2),(3,4); eval $select_view; eval $drop_view; --echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s) create view v1 as values (1,2) union values (1,2),(3,4); eval $select_view; eval $drop_view; create view v1 as select 1,2 union all values (1,2),(3,4); eval $select_view; eval $drop_view; create view v1 as values (1,2),(3,4) union all select 1,2; eval $select_view; eval $drop_view; create view v1 as values (1,2) union all values (1,2),(3,4); eval $select_view; eval $drop_view; --echo # IN-subquery with VALUES structure(s) : simple case let $query= select * from t1 where a in (values (1)); let $subst_query= select * from t1 where a in (select * from (values (1)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place let $query= select * from t1 where a in (values (1) union select 2); let $subst_query= select * from t1 where a in (select * from (values (1)) as tvc_0 union select 2); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place let $query= select * from t1 where a in (select 2 union values (1)); let $subst_query= select * from t1 where a in (select 2 union select * from (values (1)) tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # IN-subquery with VALUES structure(s) : UNION ALL let $query= select * from t1 where a in (values (1) union all select b from t1); let $subst_query= select * from t1 where a in (select * from (values (1)) as tvc_0 union all select b from t1); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # NOT IN subquery with VALUES structure(s) : simple case let $query= select * from t1 where a not in (values (1),(2)); let $subst_query= select * from t1 where a not in (select * from (values (1),(2)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place let $query= select * from t1 where a not in (values (1) union select 2); let $subst_query= select * from t1 where a not in (select * from (values (1)) as tvc_0 union select 2); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place let $query= select * from t1 where a not in (select 2 union values (1)); let $subst_query= select * from t1 where a not in (select 2 union select * from (values (1)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # ANY-subquery with VALUES structure(s) : simple case let $query= select * from t1 where a = any (values (1),(2)); let $subst_query= select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place let $query= select * from t1 where a = any (values (1) union select 2); let $subst_query= select * from t1 where a = any (select * from (values (1)) as tvc_0 union select 2); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place let $query= select * from t1 where a = any (select 2 union values (1)); let $subst_query= select * from t1 where a = any (select 2 union select * from (values (1)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # ALL-subquery with VALUES structure(s) : simple case let $query= select * from t1 where a = all (values (1)); let $subst_query= select * from t1 where a = all (select * from (values (1)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place let $query= select * from t1 where a = all (values (1) union select 1); let $subst_query= select * from t1 where a = all (select * from (values (1)) as tvc_0 union select 1); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place let $query= select * from t1 where a = any (select 1 union values (1)); let $subst_query= select * from t1 where a = any (select 1 union select * from (values (1)) as tvc_0); eval $query; eval $subst_query; eval explain extended $query; eval explain extended $subst_query; --echo # prepare statement that uses VALUES structure(s): single VALUES structure prepare stmt1 from " values (1,2); "; execute stmt1; execute stmt1; deallocate prepare stmt1; --echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) prepare stmt1 from " select 1,2 union values (1,2),(3,4); "; execute stmt1; execute stmt1; deallocate prepare stmt1; prepare stmt1 from " values (1,2),(3,4) union select 1,2; "; execute stmt1; execute stmt1; deallocate prepare stmt1; prepare stmt1 from " select 1,2 union values (3,4) union values (1,2); "; execute stmt1; execute stmt1; deallocate prepare stmt1; prepare stmt1 from " values (5,6) union values (1,2),(3,4); "; execute stmt1; execute stmt1; deallocate prepare stmt1; --echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) prepare stmt1 from " select 1,2 union values (1,2),(3,4); "; execute stmt1; execute stmt1; deallocate prepare stmt1; prepare stmt1 from " values (1,2),(3,4) union all select 1,2; "; execute stmt1; execute stmt1; deallocate prepare stmt1; prepare stmt1 from " select 1,2 union all values (3,4) union all values (1,2); "; execute stmt1; execute stmt1; deallocate prepare stmt1; prepare stmt1 from " values (1,2) union all values (1,2),(3,4); "; execute stmt1; execute stmt1; deallocate prepare stmt1; --echo # explain query that uses VALUES structure(s): single VALUES structure explain values (1,2); explain format=json values (1,2); --echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s) explain select 1,2 union values (1,2),(3,4); explain values (1,2),(3,4) union select 1,2; explain values (5,6) union values (1,2),(3,4); explain format=json select 1,2 union values (1,2),(3,4); explain format=json values (1,2),(3,4) union select 1,2; explain format=json values (5,6) union values (1,2),(3,4); explain select 1,2 union values (3,4) union values (1,2); explain format=json select 1,2 union values (3,4) union values (1,2); --echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) explain select 1,2 union values (1,2),(3,4); explain values (1,2),(3,4) union all select 1,2; explain values (1,2) union all values (1,2),(3,4); explain format=json values (1,2),(3,4) union all select 1,2; explain format=json select 1,2 union values (1,2),(3,4); explain format=json values (1,2) union all values (1,2),(3,4); explain select 1,2 union all values (3,4) union all values (1,2); explain format=json select 1,2 union all values (3,4) union all values (1,2); --echo # analyze query that uses VALUES structure(s): single VALUES structure analyze values (1,2); --source include/analyze-format.inc analyze format=json values (1,2); --echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s) analyze select 1,2 union values (1,2),(3,4); analyze values (1,2),(3,4) union select 1,2; analyze values (5,6) union values (1,2),(3,4); --source include/analyze-format.inc analyze format=json select 1,2 union values (1,2),(3,4); --source include/analyze-format.inc analyze format=json values (1,2),(3,4) union select 1,2; --source include/analyze-format.inc analyze format=json values (5,6) union values (1,2),(3,4); analyze select 1,2 union values (3,4) union values (1,2); --source include/analyze-format.inc analyze format=json select 1,2 union values (3,4) union values (1,2); --echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) analyze select 1,2 union values (1,2),(3,4); analyze values (1,2),(3,4) union all select 1,2; analyze values (1,2) union all values (1,2),(3,4); --source include/analyze-format.inc analyze format=json values (1,2),(3,4) union all select 1,2; --source include/analyze-format.inc analyze format=json select 1,2 union values (1,2),(3,4); --source include/analyze-format.inc analyze format=json values (1,2) union all values (1,2),(3,4); analyze select 1,2 union all values (3,4) union all values (1,2); --source include/analyze-format.inc analyze format=json select 1,2 union all values (3,4) union all values (1,2); --echo # different number of values in TVC --error ER_WRONG_NUMBER_OF_VALUES_IN_TVC values (1,2),(3,4,5); --echo # illegal parameter data types in TVC --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION values (1,point(1,1)),(1,1); --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION values (1,point(1,1)+1); --echo # field reference in TVC --error ER_FIELD_REFERENCE_IN_TVC select * from (values (1), (b), (2)) as new_tvc; --error ER_FIELD_REFERENCE_IN_TVC select * from (values (1), (t1.b), (2)) as new_tvc; drop table t1; --echo # --echo # MDEV-15940: cursor over TVC --echo # DELIMITER |; BEGIN NOT ATOMIC DECLARE v INT; DECLARE cur CURSOR FOR VALUES(7); OPEN cur; FETCH cur INTO v; SELECT v; END; | BEGIN NOT ATOMIC DECLARE v INT DEFAULT 0; FOR a IN (VALUES (7)) DO SET v = v + 1; END FOR; SELECT v; END; | DELIMITER ;| --echo # --echo # MDEV-16038: empty row in TVC --echo # --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; --echo # --echo # MDEV-17017: TVC in derived table --echo # create table t1 (a int); insert into t1 values (9), (3), (2); let $q1= select * from (values (7), (5), (8), (1), (3), (8), (1)) t; eval $q1; eval explain $q1; let $q2= select * from (values (1,11), (7,77), (3,31), (4,42)) t; eval $q2; eval explain $q2; let $q3= select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; eval $q3; eval explain $q3; let $q4= select * from (values (7), (5), (8), (1) union select * from t1) t; eval $q4; eval explain $q4; drop table t1; --echo # --echo # MDEV-16930: expression in the first row of TVC specifying derived table --echo # SELECT 1 + 1, 2, "abc"; SELECT * FROM (SELECT 1 + 1, 2, "abc") t; WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte; SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc"; CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc"; SELECT * FROM v1; DROP VIEW v1; VALUES(1 + 1,2,"abc"); SELECT * FROM (VALUES(1 + 1,2,"abc")) t; PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; --echo # --echo # MDEV-17894: tvc with ORDER BY ... LIMIT --echo # let $q= values (5), (7), (1), (3), (4) limit 2; eval $q; eval explain extended $q; let $q= values (5), (7), (1), (3), (4) limit 2 offset 1; eval $q; eval explain extended $q; let $q= values (5), (7), (1), (3), (4) order by 1 limit 2; eval $q; eval explain extended $q; let $q= values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; eval $q; eval explain extended $q; let $q= values (5), (7), (1), (3), (4) order by 1; eval $q; eval explain extended $q; let $q= values (5,90), (7,20), (1,70), (3,50), (4,10) order by 2; eval $q; eval explain extended $q; let $q= select 2 union (values (5), (7), (1), (3), (4) limit 2); eval $q; eval explain extended $q; let $q= select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); eval $q; eval explain extended $q; let $q= select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); eval $q; eval explain extended $q; let $q= select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) limit 2) union select 2; eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; eval $q; eval explain extended $q; let $q= select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; eval $q; eval explain extended $q; let $q= select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; eval $q; eval explain extended $q; let $q= ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) union ( values (5), (7), (1), (3), (4) order by 1 limit 2 ); eval $q; eval explain extended $q; let $q= ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) union all ( values (5), (7), (1), (3), (4) order by 1 limit 2 ); eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; eval $q; eval explain extended $q; let $q= (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1 limit 2 offset 1; eval $q; eval explain extended $q; --error ER_BAD_FIELD_ERROR values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; prepare stmt from " select 2 union (values (5), (7), (1), (3), (4) limit 2) "; execute stmt; execute stmt; deallocate prepare stmt; prepare stmt from " select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) "; execute stmt; execute stmt; deallocate prepare stmt; prepare stmt from " select 3 union all (values (5), (7), (1), (3), (4) limit 2) "; execute stmt; execute stmt; deallocate prepare stmt; prepare stmt from " select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) "; execute stmt; execute stmt; 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; execute stmt; deallocate prepare stmt; --error ER_BAD_FIELD_ERROR prepare stmt from " values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; "; create view v1 as values (5), (7), (1), (3), (4) order by 1 limit 2; show create view v1; select * from v1; 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; select * from v1; drop view v1; --error ER_BAD_FIELD_ERROR create view v1 as values (5,90), (7,20), (1,70), (3,50), (4,10) order by 3; --error ER_BAD_FIELD_ERROR 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 ); --echo # --echo # MDEV-20229: view defined as select using --echo # CTE with named columns defined as TVC --echo # create view v1 as with t(a) as (values (2), (1)) select a from t; show create view v1; select * from v1; drop view v1; --echo # --echo # MDEV-22560 Crash on a table value constructor with an SP variable --echo # DELIMITER $$; BEGIN NOT ATOMIC DECLARE a INT DEFAULT 0; VALUES (a) UNION SELECT 1; END; $$ DELIMITER ;$$ --echo # --echo # MDEV-21995 Server crashes in Item_field::real_type_handler with table value constructor --echo # --error ER_NOT_ALLOWED_IN_THIS_CONTEXT VALUES (IGNORE); --error ER_NOT_ALLOWED_IN_THIS_CONTEXT VALUES (DEFAULT); --error ER_NOT_ALLOWED_IN_THIS_CONTEXT EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE; --error ER_NOT_ALLOWED_IN_THIS_CONTEXT EXECUTE IMMEDIATE 'VALUES (?)' USING DEFAULT; --echo # --echo # MDEV-24675: TVC using subqueries --echo # values((select 1)); values (2), ((select 1)); values ((select 1)), (2), ((select 3)); values ((select 1), 2), (3,4), (5, (select 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)); values((select min(b) from t1)); values ((select max(a) from t1), (select min(b) from t1)); values((select * from (select max(b) from t1) as t)); drop table t1; --echo # --echo # MDEV-24618: TVC contains extra parenthesis for row expressions --echo # in value list --echo # create table t1 (a int, b int); insert into t1 values (1,3), (2,3); --error ER_OPERAND_COLUMNS insert into t1 values ((5,4)); --error ER_OPERAND_COLUMNS values ((1,2)); --error ER_OPERAND_COLUMNS select * from (values ((1,2))) dt; values (1,2); --error ER_OPERAND_COLUMNS values ((select min(a), max(b) from t1)); drop table t1; --echo # --echo # MDEV-24840: union of TVCs in IN subquery --echo # create table t1 (a int) engine=myisam; insert into t1 values (3), (7), (1); let $q= select a from t1 where a in (values (7) union values (8)); eval $q; eval explain extended $q; eval prepare stmt from "$q"; execute stmt; execute stmt; deallocate prepare stmt; let $q= select a from t1 where a not in (values (7) union values (8)); eval $q; eval explain extended $q; let $q= select a from t1 where a < all(values (7) union values (8)); eval $q; eval explain extended $q; let $q= select a from t1 where a >= any(values (7) union values (8)); eval $q; eval explain extended $q; drop table t1; --echo # --echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries --echo # create table t1 (a int); insert into t1 values (3), (7), (1); let $q1= values (8), ((select * from t1 where a between 2 and 4)); eval $q1; eval explain $q1; let $q2= values ((select * from t1 where a between 2 and 4)), ((select * from t1 where a > 10)); eval $q2; eval explain $q2; let $q3= values (10,11), ((select * from t1 where a = 7) + 1, 21); eval $q3; eval explain $q3; drop table t1; --echo # --echo # MDEV-24910: TVC containing subquery used as a subselect --echo # 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); let $q1= select (values ((select 2))) from t2; eval $q1; --enable_prepare_warnings eval explain $q1; --disable_prepare_warnings eval prepare stmt from "$q1"; execute stmt; execute stmt; deallocate prepare stmt; #enable after fix MDEV-28603 --disable_view_protocol let $q2= select (values ((select * from t1 where a > 10))) from t2; eval $q2; eval explain $q2; eval prepare stmt from "$q2"; execute stmt; execute stmt; 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; let $q3= select (values ((select * from t3 where a in (select * from v1)))); eval $q3; eval explain $q3; eval prepare stmt from "$q3"; execute stmt; execute stmt; deallocate prepare stmt; let $q4= select (values ((select * from t3 where a > 10 and a in (select * from v1)))); eval $q4; eval explain $q4; eval prepare stmt from "$q4"; execute stmt; execute stmt; deallocate prepare stmt; --enable_view_protocol drop view v1; drop table t1,t2,t3; --echo # --echo # MDEV-24919: subselect formed by TVC and used in set function --echo # select sum((values(1))); --echo # --echo # MDEV-22786: Nested table values constructors --echo # values ((values (2))); values ((values (2)), (5), (select 4)); values ((7), (values (2)), (5), (select 4)); values ((values (2))) union values ((values (3))); values ((values (2))), ((values (3))); values ((values (2))), ((select 4)), ((values (3))); values ((values (4)), (values (5))), ((values (1)), (values (7))); values ((values (4)), (select 5)), ((select 1), (values (7))); values ((select 2)) union values ((values (3))); values ((values (2))) union values((select 3)); values ((values (2))) union all values ((values (2))); values ((values (4)), (values (5))), ((values (1)), (values (7))) union values ((values (4)), (select 5)), ((select 2), (values (8))); values ((values (4)), (values (5))), ((values (1)), (values (7))) union all values ((values (4)), (select 5)), ((select 2), (values (8))); values ((values (1) union values (1))); values ((values (1) union values (1) union values (1))); values ((values ((values (4))))); values ((values ((select 5)))); values ((select (values (4))), (values ((values(5))))); values ((select (values (4))), (values ((select 5)))); values ((select (values (4))), (values ((values(5))))) union values ((select (values (4))), (values ((select 7)))); values ((values (2))), ((values ((values (4))))); values ((values (2))), ((values ((select 4)))); values ((values (2))), ((values ((values (4))))) union values ((values (8))), ((values ((select 4)))); values ((values (2))), ((values ((values (4))))) union all values ((values (8))), ((values ((select 4)))); #A view definition cannot contain a subquery in the FROM clause # in the SELECT statement --disable_view_protocol select * from (values ((values (2)))) dt; select * from (values ((values (2)), (5), (select 4))) dt; select * from (values ((values (2))) union values ((values (3)))) dt; select * from (values ((values (2))), ((values (3)))) dt; select * from (values ((values (2))), ((values (3)))) dt; select * from (values ((values (2))), ((select 4)), ((values (3)))) dt; --enable_view_protocol create table t1 (a int); insert into t1 values (3), (7), (1); values ((values ((select a from t1 where a=7)))); values ((values ((select (values(2)) from t1 where a=8)))); values ((values ((select a from t1 where a=7)))) union values ((values ((select (values(2)) from t1 where a=8)))); values ((values ((select a from t1 where a in ((values (7))))))); values ((values ((select a from t1 where a in ((values (7), (8))))))); values ((values ((select a from t1 where a in (values (7) union values (8)))))); values ((values ((select (values(2)) from t1 where a=8)))); --error ER_SUBQUERY_NO_1_ROW values ((select (values(2)) from t1 where a<7)); #A view definition cannot contain a subquery in the FROM clause # in the SELECT statement --disable_view_protocol select * from (values ((values ((select a from t1 where a=7))))) dt; select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt; --enable_view_protocol insert into t1(a) values ((values (2))), ((values (3))); select * from t1; drop table t1; --echo # --echo # MDEV-25484: Derived table using TVC with LIMIT and ORDER BY --echo # #A view definition cannot contain a subquery in the FROM clause # in the SELECT statement --disable_view_protocol 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; (values (3), (7), (1) limit 2) order by 1 desc; select * from ( (values (3), (7), (1) limit 2) order by 1 desc) as dt; select * from ( select * from t1 order by 1 limit 2 ) as dt; values (3),(7),(1) order by 1 limit 2; select * from ( values (3),(7),(1) order by 1 limit 2 ) as dt; values (3),(7),(1) union values (2),(4) order by 1 limit 2; select * from (values (3),(7),(1) union values (2),(4) order by 1 limit 2) as dt; drop table t1; --enable_view_protocol --echo # --echo # MDEV-23182: Server crashes in --echo # Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS --echo # 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; --echo # Without the patch second execution of the prepared statement 'stmt' --echo # results in crash. EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; --echo # 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; EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1; --echo # 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; EXECUTE stmt; DROP TABLE t1; --echo # 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; EXECUTE stmt; DROP TABLE t1; SET @@in_predicate_conversion_threshold = @save_in_predicate_conversion_threshold; --echo End of 10.3 tests --echo # --echo # MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)) --echo # --error ER_NOT_ALLOWED_IN_THIS_CONTEXT VALUES (DEFAULT) UNION VALUES (DEFAULT); --error ER_NOT_ALLOWED_IN_THIS_CONTEXT VALUES (IGNORE) UNION VALUES (IGNORE); CREATE TABLE t1 (a INT DEFAULT 10); --error ER_NOT_ALLOWED_IN_THIS_CONTEXT INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)); --error ER_NOT_ALLOWED_IN_THIS_CONTEXT INSERT INTO t1 (VALUES (IGNORE) UNION VALUES (IGNORE)); DROP TABLE t1; --echo # --echo # MDEV-28603: VIEW with table value constructor used as single-value --echo # subquery contains subquery as its first element --echo # create table t1 (a int); insert into t1 values (3), (7), (1); create table t2 (b int); insert into t2 values (1), (2); let $q= select (values ((select * from t1 where a > 5))) as m from t2; eval create view v as $q; eval $q; eval select * from v; eval with cte as ( $q ) select * from cte; eval explain $q; eval explain select * from v; eval explain with cte as ( $q ) select * from cte; eval prepare stmt from "$q"; execute stmt; execute stmt; deallocate prepare stmt; eval prepare stmt from "select * from v"; execute stmt; execute stmt; deallocate prepare stmt; eval prepare stmt from "with cte as ( $q ) select * from cte"; execute stmt; execute stmt; deallocate prepare stmt; show create view v; drop view v; eval prepare stmt from "create view v as $q"; execute stmt; show create view v; select * from v; drop view v; execute stmt; show create view v; select * from v; deallocate prepare stmt; prepare stmt from "show create view v"; execute stmt; execute stmt; deallocate prepare stmt; drop view v; let $q= select (values ((select * from t1 where a > 5 union select * from t1 where a > 7))) as m from t2; eval create view v as $q; eval $q; eval select * from v; show create view v; drop view v; drop table t1,t2; --echo # --echo # End of 10.4 tests --echo #