[MDEV-29399] Affected by ROLLBACK txn, COMMIT txn produce incorrect result Created: 2022-08-27  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 10.8.3, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.6

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-29083 MariaDB produce different results for... Open
relates to MDEV-29123 Incorrect results of SELECT statement... Confirmed
relates to MDEV-29398 undermined result of SELECT statements Confirmed

 Description   

I used my fuzzing tool to test MariaDB and found a transaction-related bug that make server produce incorrect 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 3 transactions T0, T1 and T2
Txn 0> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Txn 0> START TRANSACTION;
Txn 2> START TRANSACTION;
Txn 0> update t_j_eqsc set wkey = 37, c_fm792b = PI();
Txn 0> COMMIT;
Txn 1> START TRANSACTION;
Txn 2> insert into t_j_eqsc (wkey, pkey) values (79, 162000);
Txn 2> ROLLBACK;
Txn 1> select * from t_j_eqsc
where t_j_eqsc.c_fm792b not in (
select PI() as c0 from t_xqlwp as ref_0); --- output 10 rows
Txn 1> COMMIT;

Testcase 2

/usr/local/mysql/bin/mysql -uroot -Dtestdb
Txn 0> update t_j_eqsc set wkey = 37, c_fm792b = PI();
Txn 0> select * from t_j_eqsc
where t_j_eqsc.c_fm792b not in (
select PI() as c0 from t_xqlwp as ref_0); --- output empty

Testcase 1 and Testcase 2 should produce the same results. However, the SELECT statement in Testcase 1 output 10 rows while the SELECT statement in Testcase 2 output empty.

Analyzing the test case, I think the SELECT statement in Testcase 1 produces incorrect results. Because the UPDATE in txn 0 has changed t_j_eqsc.c_fm792b to PI(), the WHERE clause in SELECT in txn 1 will be violated and thus the SELECT should output empty.

 Comments   
Comment by Elena Stepanova [ 2022-08-30 ]

As in other similar bugs, transactions and void DML are just the way to trigger statistics collection. Same can be achieved by ANALYZE TABLE.

MTR-style test case, otherwise the same as reported

--source include/have_innodb.inc
 
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 
/*!40101 SET NAMES utf8mb4 */;
 
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 
/*!40103 SET TIME_ZONE='+00:00' */;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
 
 
DROP TABLE IF EXISTS `t_j_eqsc`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `t_j_eqsc` (
 
  `wkey` int(11) DEFAULT NULL,
 
  `pkey` int(11) NOT NULL,
 
  `c_r2ct0` int(11) DEFAULT NULL,
 
  `c_fm792b` double DEFAULT NULL,
 
  `c_lbwsvd` int(11) DEFAULT NULL,
 
  `c_2_hecd` text DEFAULT NULL,
 
  `c_iaswod` int(11) DEFAULT NULL,
 
  `c_mhoxvc` text DEFAULT NULL,
 
  PRIMARY KEY (`pkey`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
LOCK TABLES `t_j_eqsc` WRITE;
 
/*!40000 ALTER TABLE `t_j_eqsc` DISABLE KEYS */;
 
INSERT INTO `t_j_eqsc` VALUES
 
(6,32000,92,NULL,59,'8kef5d',4,NULL),
 
(6,33000,40,NULL,20,'optfvc',66,'ikzfdd'),
 
(6,34000,83,50.4,60,'z7bs6c',69,'f_58sb'),
 
(7,35000,12,41.21,31,'j45ne',28,NULL),
 
(7,36000,73,42.91,19,'nm4by',87,NULL),
 
(7,37000,86,41.55,43,'_y9uo',50,NULL),
 
(11,51000,NULL,31.32,8,NULL,NULL,NULL),
 
(11,52000,NULL,85.77,94,'goiqgb',NULL,NULL),
 
(11,53000,NULL,99.95,73,'scio7b',NULL,NULL),
 
(11,54000,NULL,58.55,60,'huom8',NULL,NULL);
 
/*!40000 ALTER TABLE `t_j_eqsc` ENABLE KEYS */;
 
UNLOCK TABLES;
 
 
 
DROP TABLE IF EXISTS `t_xqlwp`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `t_xqlwp` (
 
  `wkey` int(11) DEFAULT NULL,
 
  `pkey` int(11) NOT NULL,
 
  `c_o9tn3d` text DEFAULT NULL,
 
  `c_nqwjm` text DEFAULT NULL,
 
  `c_xxmss` text DEFAULT NULL,
 
  `c_wthydc` double DEFAULT NULL,
 
  `c_w3gaz` text DEFAULT NULL,
 
  `c_l9gqbb` double DEFAULT NULL,
 
  `c_1fnn6d` text DEFAULT NULL,
 
  `c_ydnhic` int(11) DEFAULT NULL,
 
  `c_5eibb` text DEFAULT NULL,
 
  PRIMARY KEY (`pkey`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*!40101 SET character_set_client = @saved_cs_client */;
 
 
 
LOCK TABLES `t_xqlwp` WRITE;
 
/*!40000 ALTER TABLE `t_xqlwp` DISABLE KEYS */;
 
INSERT INTO `t_xqlwp` VALUES
 
(1,11000,NULL,'el1cj','ht6q_d',63.94,'egitbc',NULL,NULL,NULL,'mdsrvd'),
 
(1,12000,NULL,'pauvgb','vjrbrb',58.54,'gcm4fb',NULL,NULL,NULL,NULL),
 
(1,13000,NULL,'g_w6e','mvsrqb',64.97,'58bvec',NULL,NULL,NULL,'3fntl'),
 
(1,14000,NULL,'2ce8id','dtw_nb',85.48,'qofob',NULL,NULL,NULL,NULL),
 
(5,28000,'i_q3ab','kyxcs','7jw2_c',57.1,'vryovb',12.92,'etkofb',NULL,'ssw8_c'),
 
(5,29000,'sg18ad','d5c2dd',NULL,82.85,'_qxq1d',32.75,NULL,NULL,'net5ob'),
 
(5,30000,'mxmgnb','qozbod','jblspd',35.54,'7vc7tc',67.74,'58iyjb',NULL,'i1kqec'),
 
(5,31000,'x0rf4d','c0ek3c','kakd_d',71.25,'dbtmoc',23.52,'ebrnjd',NULL,'gnei3'),
 
(9,44000,'u_6h7c','7l3kfb','_j_h0',37.24,NULL,97.46,'z5myn',57,NULL),
 
(9,45000,'04hebc','fkjbu',NULL,100.6,NULL,44.59,'xflix',10,NULL),
 
(9,46000,'knrvyc','iviytd','sscjtc',6.48,NULL,100.7,'iecreb',64,NULL),
 
(10,47000,'v4pkw',NULL,'mpm1xb',8.96,NULL,NULL,'t6x04b',NULL,'5c2d7b'),
 
(10,48000,'ivvlkb',NULL,NULL,24.73,NULL,NULL,'8dpvrc',NULL,'bsw_hc'),
 
(10,49000,'i85bwb',NULL,NULL,NULL,NULL,47.95,'qjkpb',NULL,NULL),
 
(10,50000,'tjk_rc',NULL,'xsmcw',21.2,NULL,2.38,NULL,NULL,'icyr6'),
 
(13,59000,NULL,NULL,'zekhr',NULL,NULL,51.44,'iwioqd',34,'dgun9'),
 
(13,60000,NULL,NULL,'f6ojhd',18.45,NULL,90.25,'rugol',56,'w5bab'),
 
(13,61000,NULL,NULL,'6jgb1',10.5,NULL,90.91,'m3wkbc',40,'mqa1fc'),
 
(13,62000,NULL,NULL,'vmlefb',31.74,NULL,76.24,'0kte5c',88,'pzm6vc'),
 
(13,63000,NULL,NULL,'eyfwn',6.4,NULL,25.2,'pyu5dc',28,'x3izob'),
 
(13,64000,NULL,NULL,'dkmroc',52.3,NULL,79.7,'46kfid',15,'6grgrc'),
 
(13,65000,NULL,NULL,'wwb4i',NULL,NULL,75.1,'roprxb',15,'cwkxk'),
 
(15,74000,'x_2gzd','eoq7tc','9lbzgd',53.49,'z_nxcb',NULL,'taqog',56,NULL),
 
(15,75000,'jdzcnc','lsapqd','y4ipxb',36.51,'iqoeid',NULL,NULL,60,NULL),
 
(15,76000,'wescdd',NULL,'ljszm',83.16,'i9ekcc',NULL,'kqb1bb',56,'dhy8c'),
 
(15,77000,NULL,'3nby_b','tqopyb',87.84,'ycwlhd',NULL,'frt1gc',46,'_zpy7'),
 
(15,78000,'7_etk','lodhgd','10_jsd',77.18,'5571j',NULL,'vrk_i',83,'mt_mpd'),
 
(15,79000,'p7n78','rmeirb','c_vgf',70.45,'i5_fnc',NULL,'bh7spc',54,NULL),
 
(15,80000,'ymcvpc','ckbzlc',NULL,80.89,'swlged',NULL,'vvjg5c',26,'nlfey');
 
/*!40000 ALTER TABLE `t_xqlwp` ENABLE KEYS */;
 
UNLOCK TABLES;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 
 
update t_j_eqsc set wkey = 37, c_fm792b = PI();
 
 
 
let $query=
 
select * from t_j_eqsc
 
where t_j_eqsc.c_fm792b not in (
 
select PI() as c0 from t_xqlwp as ref_0); 
 
 
eval $query;
 
 
 
analyze table t_j_eqsc, t_xqlwp persistent for all;
 
 
 
eval $query;
 
 
 
drop table t_j_eqsc, t_xqlwp;

10.7 b9c2ae50

 
select * from t_j_eqsc
 
where t_j_eqsc.c_fm792b not in (
 
select PI() as c0 from t_xqlwp as ref_0);
 
wkey	pkey	c_r2ct0	c_fm792b	c_lbwsvd	c_2_hecd	c_iaswod	c_mhoxvc
 
 
 
 
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 
1	PRIMARY	t_j_eqsc	ALL	NULL	NULL	NULL	NULL	1	10.00	100.00	0.00	Using where
 
2	DEPENDENT SUBQUERY	ref_0	index	NULL	PRIMARY	4	NULL	1	1.00	100.00	100.00	Using index


wkey	pkey	c_r2ct0	c_fm792b	c_lbwsvd	c_2_hecd	c_iaswod	c_mhoxvc
 
37	32000	92	3.141592653589793	59	8kef5d	4	NULL
 
37	33000	40	3.141592653589793	20	optfvc	66	ikzfdd
 
37	34000	83	3.141592653589793	60	z7bs6c	69	f_58sb
 
37	35000	12	3.141592653589793	31	j45ne	28	NULL
 
37	36000	73	3.141592653589793	19	nm4by	87	NULL
 
37	37000	86	3.141592653589793	43	_y9uo	50	NULL
 
37	51000	NULL	3.141592653589793	8	NULL	NULL	NULL
 
37	52000	NULL	3.141592653589793	94	goiqgb	NULL	NULL
 
37	53000	NULL	3.141592653589793	73	scio7b	NULL	NULL
 
37	54000	NULL	3.141592653589793	60	huom8	NULL	NULL
 
 
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 
1	PRIMARY	t_j_eqsc	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using where
 
2	MATERIALIZED	ref_0	index	NULL	PRIMARY	4	NULL	29	29.00	100.00	100.00	Using index

The failure is random, re-run several times if it doesn't show up right away. Couldn't reproduce on 10.5 and lower versions.

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