[MDEV-29083] MariaDB produce different results for INSERT statement when using transaction Created: 2022-07-11  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.8.3
Fix Version/s: 10.11

Type: Bug Priority: Major
Reporter: Zuming Jiang Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu 20.04

Attachments: File mysql_bk.sql    
Issue Links:
Relates
relates to MDEV-29399 Affected by ROLLBACK txn, COMMIT txn ... Confirmed

 Description   

I used my fuzzing tool to test MariaDB and found a transaction-related bug that make server produce different results.

Mariadb installation
1) cd mariadb-10.8.3
2) mkdir build; cd build
3) cmake .. -DCMAKE_BUILD_TYPE=Debug
4) make -j12 && sudo make install

Setup the environment
1) export ASAN_OPTIONS=detect_leaks=0
2) /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql &
3) /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql # set up the database

Reproduce bug

Testcase 1

/usr/local/mysql/bin/mysql -uroot -Dtestdb # set up 2 transactions T0 and T1
Txn 0> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Txn 0> START TRANSACTION;
Txn 1> START TRANSACTION;
Txn 1> delete from t_euhshb;
Txn 1> ROLLBACK; Txn 1
Txn 0> insert into t_7sdcgd values
(91, 167000, case when exists (
select *
from
(t_euhshb as ref_0
inner join t_euhshb as ref_1
on (ref_0.pkey = ref_1.wkey ))
where ref_1.wkey = (
select
ref_0.c_oyg4yd as c0
from
t_euhshb as ref_2)
) then 1
else 2 end
, 96, 71.64, '1c08ld');
Txn 0> select * from t_7sdcgd where wkey = 91;
Txn 0> COMMIT;

Testcase 2

/usr/local/mysql/bin/mysql -uroot -Dtestdb
Txn 0> insert into t_7sdcgd values
(91, 167000, case when exists (
select *
from
(t_euhshb as ref_0
inner join t_euhshb as ref_1
on (ref_0.pkey = ref_1.wkey ))
where ref_1.wkey = (
select
ref_0.c_oyg4yd as c0
from
t_euhshb as ref_2)
) then 1
else 2 end
, 96, 71.64, '1c08ld');
Txn 0> select * from t_7sdcgd where wkey = 91;

Testcase 1 and Testcase 2 should produce the same results. However, The INSERT statement in Testcase 1 succeeds, and the last SELECT statement output one row (91, 167000, 2, 96, 71.64 , 1c08ld), while the INSERT statement in Testcase 2 fails, and the last SELECT statement output empty.

 Comments   
Comment by Elena Stepanova [ 2022-07-12 ]

DML, concurrency and transactions are irrelevant, the ultimately void delete is just the means of collecting statistics. You could achieve the same by running ANALYZE on the table and executing only inner select, like

MariaDB [test]> source /tmp/mysql_bk.sql
 
...
 
MariaDB [test]> select * from (t_euhshb as ref_0 inner join t_euhshb as ref_1 on (ref_0.pkey = ref_1.wkey )) where ref_1.wkey = ( select ref_0.c_oyg4yd as c0 from t_euhshb as ref_2);
 
ERROR 1242 (21000): Subquery returns more than 1 row
 
MariaDB [test]> analyze table t_euhshb;
 
+---------------+---------+----------+----------+
 
| Table         | Op      | Msg_type | Msg_text |
 
+---------------+---------+----------+----------+
 
| test.t_euhshb | analyze | status   | OK       |
 
+---------------+---------+----------+----------+
 
1 row in set (0.013 sec)
 
 
 
MariaDB [test]> select * from (t_euhshb as ref_0 inner join t_euhshb as ref_1 on (ref_0.pkey = ref_1.wkey )) where ref_1.wkey = ( select ref_0.c_oyg4yd as c0 from t_euhshb as ref_2);
 
Empty set (0.002 sec)

So, different plans, in one case the select in WHERE clause can be successfully resolved, in other case it can't.
I'll leave it to somebody from the optimizer team to close or deal with.

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