Bug #65245 RQG: Query returns different result under REPEATABLE READ

Description:
The RQG test grammar repeatable_read.yy started failing after the patch for Bug#13807811 (btr_pcur_restore_position() can skip a record) was merged into MySQL 5.5.23. The test is designed to test the REPEATABLE READ isolation level by re-executing SELECT using various table access methods within the same transaction.

Sample output from failure:

# 2012-05-08T18:20:36 Query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8 returns different result when executed with additional predicate '/* no additional predicate */' (18 vs. 17 rows).
# 2012-05-08T18:20:36 --- /tmp//randgen5771-1336501236-server0.dump	2012-05-08 18:20:36.000000000 +0000
# 2012-05-08T18:20:36 +++ /tmp//randgen5771-1336501236-server1.dump	2012-05-08 18:20:36.000000000 +0000
# 2012-05-08T18:20:36 @@ -15,4 +15,3 @@
# 2012-05-08T18:20:36  9
# 2012-05-08T18:20:36  9
# 2012-05-08T18:20:36  9
# 2012-05-08T18:20:36 -9
# 2012-05-08T18:20:36 Full result from the original query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9
9
# 2012-05-08T18:20:36 Full result from the follow-up query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8 /* no additional predicate */
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9
# 2012-05-08T18:20:36 Executing the same queries a second time:
# 2012-05-08T18:20:36 Full result from the repeat of the query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9
# 2012-05-08T18:20:36 Full result from the repeat of the query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8 /* no additional predicate */
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9

How to repeat:
export RQG_HOME=~/randgen/ MASTER_MYPORT=3306

perl $RQG_HOME/gentest.pl \
 --dsn=dbi:mysql:host=127.0.0.1:port=$MASTER_MYPORT:user=root:database=test \
 --gendata=$RQG_HOME/conf/transactions/transactions.zz \
 --grammar=$RQG_HOME/conf/transactions/repeatable_read.yy \
 --validator=RepeatableRead \
 --reporters=Deadlock,ErrorLog,Backtrace \
 --engine=InnoDB \
 --queries=100000 \
 --duration=60

Thank you for the report.

Verified as described.

Fixed by patch for Bug#14399148 - InnoDB tables under load produce duplicate copies of rows in queries.

Revision-id: marko.makela@oracle.com-20120809065529-utqnp7akvug8zmui

Davi, thanks for verifying the fix. Again, I am sorry for breaking the code.