mirror of
https://github.com/MariaDB/server.git
synced 2025-03-31 04:15:33 +02:00
b8c2bd9f69
MDEV-34447 Removed setting first_cond_optimization to 0 in update and delete when leaf_tables_saved. This can cause problems when two ps executions of an update go through different paths, where the first ps execution goes through single table update only and the second ps execution also goes through multi table update. When this happens, the first_cond_optimization of the outer query is not set to false during the first ps execution because optimize() is not called for the outer query. But then the second ps execution will call optimize() on the outer query, which with first_cond_optimization==true trips the 2nd ps mem leak detection. This is not a problem in higher version as both executions go through multi table updates, possibly due to MDEV-28883. We fix this problem by restoring the FALSE assignments to first_cond_optimization.
341 lines
8.8 KiB
Text
341 lines
8.8 KiB
Text
#
|
|
# MDEV-32369: Memory leak when executing PS for query with IN subquery
|
|
#
|
|
CREATE TABLE t1 (a VARCHAR(10)) ENGINE=MYISAM;
|
|
CREATE TABLE t2 (b VARCHAR(10) CHARACTER SET utf8) ENGINE=MYISAM;
|
|
INSERT INTO t1 VALUES ('b'), ('a'), ('c');
|
|
INSERT INTO t2 VALUES ('c'), ('d'), ('b');
|
|
PREPARE stmt FROM "SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2)";
|
|
EXECUTE stmt;
|
|
a
|
|
c
|
|
b
|
|
EXECUTE stmt;
|
|
a
|
|
c
|
|
b
|
|
DEALLOCATE PREPARE stmt;
|
|
DELETE FROM t1;
|
|
DELETE FROM t2;
|
|
INSERT INTO t1 VALUES ('b');
|
|
INSERT INTO t2 VALUES ('b');
|
|
PREPARE stmt FROM "SELECT t1.a FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2)";
|
|
EXECUTE stmt;
|
|
a
|
|
b
|
|
EXECUTE stmt;
|
|
a
|
|
b
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# MDEV-32569: Failure when executing PS for query using IN subquery
|
|
#
|
|
CREATE TABLE t1 (a varchar(10)) ENGINE=MYISAM;
|
|
CREATE TABLE t2 (b varchar(10) CHARACTER SET utf8) ENGINE=MYISAM;
|
|
INSERT INTO t1 VALUES ('b');
|
|
INSERT INTO t2 VALUES ('b');
|
|
PREPARE stmt FROM
|
|
"SELECT STRAIGHT_JOIN t1.a FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2)";
|
|
EXECUTE stmt;
|
|
a
|
|
b
|
|
EXECUTE stmt;
|
|
a
|
|
b
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# MDEV-32733: Two JSON related tests running in PS mode fail on server
|
|
# built with -DWITH_PROTECT_STATEMENT_MEMROOT=YES
|
|
#
|
|
PREPARE stmt FROM "select json_contains_path('{\"key1\":1}', 'oNE', '$.key2[1]') as exp";
|
|
EXECUTE stmt;
|
|
exp
|
|
0
|
|
EXECUTE stmt;
|
|
exp
|
|
0
|
|
DEALLOCATE PREPARE stmt;
|
|
#
|
|
# MDEV-32466: Potential memory leak on execuing of create view statement
|
|
#
|
|
CREATE FUNCTION f1 () RETURNS VARCHAR(1)
|
|
BEGIN
|
|
DECLARE rec1 ROW TYPE OF v1;
|
|
SELECT z INTO rec1 FROM v1;
|
|
RETURN 1;
|
|
END|
|
|
CREATE FUNCTION f2 () RETURNS VARCHAR(1) RETURN '!';
|
|
CREATE VIEW v1 AS SELECT f2() z;
|
|
PREPARE stmt FROM "SELECT f1()";
|
|
EXECUTE stmt;
|
|
f1()
|
|
1
|
|
EXECUTE stmt;
|
|
f1()
|
|
1
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP FUNCTION f1;
|
|
DROP VIEW v1;
|
|
DROP FUNCTION f2;
|
|
#
|
|
# MDEV-32867: ASAN errors in Item_func_json_contains_path::val_int upon PS execution
|
|
#
|
|
CREATE TABLE t1 (f BLOB) ENGINE=MyISAM;
|
|
PREPARE stmt FROM "SELECT * FROM t1 WHERE JSON_EXISTS(JSON_ARRAY('[true,1234567890]'), '$**.*') != JSON_CONTAINS_PATH(JSON_INSERT('{}', '$[1]', NULL), 'all', '$[1]')";
|
|
EXECUTE stmt;
|
|
f
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP TABLE t1;
|
|
# End of 10.4 tests
|
|
#
|
|
# MDEV-34447: Memory leakage is detected on running the test main.ps against the server 11.1
|
|
#
|
|
CREATE TABLE t1 (id INT, value INT);
|
|
CREATE TABLE t2 (id INT);
|
|
PREPARE stmt FROM 'UPDATE t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)';
|
|
EXECUTE stmt;
|
|
INSERT INTO t1 VALUES (1,10),(2,10),(3,10);
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
EXECUTE stmt;
|
|
SELECT * FROM t1;
|
|
id value
|
|
1 1
|
|
2 1
|
|
3 NULL
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP TABLE t1, t2;
|
|
# Memory leak also could take place on running the DELETE statement
|
|
# with the LIMIT clause. Check it.
|
|
CREATE TABLE t1 (c1 INT);
|
|
INSERT INTO t1 (c1) VALUES (1), (2), (3);
|
|
CREATE PROCEDURE p1(p1 INT)
|
|
DELETE FROM t1 LIMIT p1;
|
|
CALL p1(0);
|
|
CALL p1(1);
|
|
CALL p1(2);
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-34757: assertion of (mem_root->flags & 4) == 0 fails in 2nd ps execution with partition pruning
|
|
#
|
|
CREATE TABLE t1 (id INT, value INT);
|
|
CREATE TABLE t2 (id INT);
|
|
PREPARE stmt FROM 'EXPLAIN UPDATE t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id)';
|
|
EXECUTE stmt;
|
|
INSERT INTO t1 VALUES (1,10),(2,10),(3,10);
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
EXECUTE stmt;
|
|
SELECT * FROM t1;
|
|
id value
|
|
1 10
|
|
2 10
|
|
3 10
|
|
DEALLOCATE PREPARE stmt;
|
|
DROP TABLE t1, t2;
|
|
set @var1=5;
|
|
set @var2=4;
|
|
create table t1 (a int) partition by list(a) (
|
|
partition p0 values in (null,1,2),
|
|
partition p1 values in (3,4)
|
|
);
|
|
create table t2 (a int);
|
|
insert into t1 values (1),(2),(3),(4);
|
|
insert into t2 values (4);
|
|
PREPARE stmt FROM 'UPDATE t1 t1 SET a = (SELECT 1 FROM t2 WHERE a = t1.a) where a = ?';
|
|
execute stmt using @var1;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
execute stmt using @var2;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
1
|
|
3
|
|
deallocate prepare stmt;
|
|
drop table t1, t2;
|
|
set @var1=5;
|
|
set @var2=4;
|
|
create table t1 (a int) partition by list(a) (
|
|
partition p0 values in (null,1,2),
|
|
partition p1 values in (3,4)
|
|
);
|
|
create table t2 (a int);
|
|
insert into t1 values (1),(2),(3),(4);
|
|
insert into t2 values (4);
|
|
PREPARE stmt FROM 'EXPLAIN UPDATE t1 t1 SET a = (SELECT 1 FROM t2 WHERE a = t1.a) where a = ?';
|
|
execute stmt using @var1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
execute stmt using @var2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using buffer
|
|
2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
deallocate prepare stmt;
|
|
drop table t1, t2;
|
|
set @var1=1;
|
|
set @var2=2;
|
|
CREATE TABLE t1 ( id INT(10), value INT(10) );
|
|
CREATE TABLE t2 ( id INT(10) );
|
|
insert into t1 values (1,10),(2,10),(3,10);
|
|
insert into t2 values (1),(2);
|
|
PREPARE stmt FROM 'UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id) WHERE ?=?';
|
|
execute stmt using @var1, @var2;
|
|
execute stmt using @var1, @var1;
|
|
deallocate prepare stmt;
|
|
DROP TABLE t1,t2;
|
|
set @var1=1;
|
|
set @var2=2;
|
|
CREATE TABLE t1 ( id INT(10), value INT(10) );
|
|
CREATE TABLE t2 ( id INT(10) );
|
|
insert into t1 values (1,10),(2,10),(3,10);
|
|
insert into t2 values (1),(2);
|
|
PREPARE stmt FROM 'EXPLAIN UPDATE t1 t1 SET value = (SELECT 1 FROM t2 WHERE id = t1.id) WHERE ?=?';
|
|
execute stmt using @var1, @var2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
execute stmt using @var1, @var1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
|
|
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
|
|
deallocate prepare stmt;
|
|
DROP TABLE t1,t2;
|
|
CREATE TABLE t1 (c1 INT);
|
|
INSERT INTO t1 (c1) VALUES (1), (2), (3);
|
|
CREATE PROCEDURE p1(p1 INT)
|
|
EXPLAIN DELETE FROM t1 LIMIT p1;
|
|
CALL p1(0);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
CALL p1(1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
|
|
CALL p1(2);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
|
|
# Clean up
|
|
DROP TABLE t1;
|
|
DROP PROCEDURE p1;
|
|
set @var1=5;
|
|
set @var2=4;
|
|
create table t1 (a int) partition by list(a) (
|
|
partition p0 values in (1,2),
|
|
partition p1 values in (3,4)
|
|
);
|
|
create table t2 (a int);
|
|
insert into t1 values (1),(2),(3),(4);
|
|
insert into t2 values (4);
|
|
PREPARE stmt FROM 'DELETE FROM t1 where a = ?';
|
|
execute stmt using @var1;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
execute stmt using @var2;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
deallocate prepare stmt;
|
|
drop table t1, t2;
|
|
set @var1=5;
|
|
set @var2=4;
|
|
create table t1 (a int) partition by list(a) (
|
|
partition p0 values in (1,2),
|
|
partition p1 values in (3,4)
|
|
);
|
|
create table t2 (a int);
|
|
insert into t1 values (1),(2),(3),(4);
|
|
insert into t2 values (4);
|
|
PREPARE stmt FROM 'EXPLAIN DELETE FROM t1 where a = ?';
|
|
execute stmt using @var1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
execute stmt using @var2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
deallocate prepare stmt;
|
|
drop table t1, t2;
|
|
set @var1=1;
|
|
set @var2=2;
|
|
CREATE TABLE t1 ( id INT(10), value INT(10) );
|
|
CREATE TABLE t2 ( id INT(10) );
|
|
insert into t1 values (1,10),(2,10),(3,10);
|
|
insert into t2 values (1),(2);
|
|
PREPARE stmt FROM 'DELETE FROM t1 WHERE ?=?';
|
|
execute stmt using @var1, @var2;
|
|
execute stmt using @var1, @var1;
|
|
deallocate prepare stmt;
|
|
DROP TABLE t1,t2;
|
|
set @var1=1;
|
|
set @var2=2;
|
|
CREATE TABLE t1 ( id INT(10), value INT(10) );
|
|
CREATE TABLE t2 ( id INT(10) );
|
|
insert into t1 values (1,10),(2,10),(3,10);
|
|
insert into t2 values (1),(2);
|
|
PREPARE stmt FROM 'EXPLAIN DELETE FROM t1 WHERE ?=?';
|
|
execute stmt using @var1, @var2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
execute stmt using @var1, @var1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL 3 Deleting all rows
|
|
deallocate prepare stmt;
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# MDEV-33858: Assertion `(mem_root->flags & 4) == 0' fails on 2nd execution of PS with -DWITH_PROTECT_STATEMENT_MEMROOT=ON
|
|
#
|
|
CREATE TABLE t (a INT);
|
|
INSERT INTO t VALUES (1),(2);
|
|
PREPARE stmt FROM "UPDATE t SET a = 0 LIMIT ?";
|
|
EXECUTE stmt USING 0;
|
|
EXECUTE stmt USING 1;
|
|
DROP TABLE t;
|
|
#
|
|
# MDEV-35249: Assertion `(mem_root->flags & 4) == 0' failed in convert_subq_to_jtbm
|
|
#
|
|
CREATE TABLE t (a INT, b INT, c INT);
|
|
INSERT INTO t VALUES (1,2,3),(4,5,6);
|
|
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t;
|
|
CREATE VIEW v2 AS SELECT * FROM v1;
|
|
PREPARE stmt FROM 'UPDATE t SET a = 0 WHERE b IN (SELECT c FROM v2)';
|
|
EXECUTE stmt;
|
|
EXECUTE stmt;
|
|
DROP VIEW v2;
|
|
DROP VIEW v1;
|
|
DROP TABLE t;
|
|
# End of 10.5 tests
|