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

This patch allocates all the wrappers around items created for column
references in the statement memory at the first execution of PS or at the
first call of SP/SF.
2025-03-25 12:52:05 +12:00

1875 lines
35 KiB
Text
Raw Permalink Blame History

create table t1 (a int, b int);
insert into t1 values (1,2),(4,6),(9,7),
(1,1),(2,5),(7,8);
--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_INVALID_DEFAULT_PARAM
EXECUTE IMMEDIATE 'VALUES (?)' USING IGNORE;
--error ER_INVALID_DEFAULT_PARAM
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 # MDEV-31178: 2 execution of query with conversion from
--echo # IN predicate to IN subquery applied
--echo #
CREATE TABLE t1 (a bigint);
INSERT INTO t1 VALUES (1), (2), (3);
SET in_predicate_conversion_threshold=1;
let $q1=
SELECT * FROM t1 WHERE a IN (1,5,3);
--echo # IN predicate is transformed into IN subquery
eval EXPLAIN EXTENDED $q1;
eval $q1;
eval PREPARE stmt FROM "$q1";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
let $q2=
SELECT * FROM t1 WHERE a IN ('1','5','3');
--echo # here IN predicate is also transformed into IN subquery
eval EXPLAIN EXTENDED $q2;
eval $q2;
eval PREPARE stmt FROM "$q2";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
let $q3=
SELECT * FROM t1 WHERE a IN ('1','1.7','3');
--echo # and here IN predicate is not transformed into IN subquery
eval EXPLAIN EXTENDED $q3;
eval $q2;
eval PREPARE stmt FROM "$q3";
EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET in_predicate_conversion_threshold=default;
DROP TABLE t1;
--echo #
--echo # End of 10.4 tests
--echo #
Reference in a new issue View git blame Copy permalink