[MDEV-29061] Prepared insert statement doesn't insert rows Created: 2022-07-08  Updated: 2023-10-01

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5
Fix Version/s: 10.5

Type: Bug Priority: Critical
Reporter: Michael Widenius Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: regression


 Description   

10.5 ha introduced a bug in derived_view.test (10.4 is correct)

From the result file:

#
 
# LP BUG#968720 crash due to converting to materialized and
 
# natural join made only once
 
#
 
SET @save968720_optimizer_switch=@@optimizer_switch;
 
SET optimizer_switch = 'derived_merge=on';
 
CREATE TABLE t1 (a int, INDEX(a));
 
INSERT INTO t1 VALUES (1);
 
CREATE TABLE t2 (a int, INDEX(a));
 
INSERT INTO t2 VALUES (1), (2);
 
INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
 
t2 AS s2;
 
SELECT * FROM t1;
 
a
 
1
 
1
 
DELETE FROM t1;
 
INSERT INTO t1 VALUES (1);
 
PREPARE stmt FROM "
 
INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN
 
t2 AS s2;
 
";
 
EXECUTE stmt;
 
SELECT * FROM t1;
 
a
 
1
 
1
 
EXECUTE stmt;
 
SELECT * FROM t1;
 
a
 
1
 
1
 
drop table t1,t2;

The last SELECT query should return 4 '1' rows. Somehow the last insert statement didn't insert any rows.

I marked this as Critical as this is a regression

 Comments   
Comment by Sergei Golubchik [ 2022-07-25 ]

sanja, this was introduced in the commit cf63eecef44f

Comment by Oleksandr Byelkin [ 2022-07-25 ]

Result form 10.4 looks the same:

SET optimizer_switch = 'derived_merge=on';
 
CREATE TABLE t1 (a int, INDEX(a));
 
INSERT INTO t1 VALUES (1);
 
CREATE TABLE t2 (a int, INDEX(a));
 
INSERT INTO t2 VALUES (1), (2);
 
SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;
 
a
 
1
 
explain extended
 
SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 
1	SIMPLE	t1	system	a	NULL	NULL	NULL	1	100.00	
1	SIMPLE	s2	ref	a	a	5	const	1	100.00	Using index
 
Warnings:
 
Note	1003	select 1 AS `a` from `test`.`t2` `s2` where `test`.`s2`.`a` = 1
 
INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;
 
SELECT * FROM t1;
 
a
 
1
 
1

Comment by Oleksandr Byelkin [ 2022-07-25 ]

ah, it is abot stored...

Generated at Thu Feb 08 10:05:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.