Result differences on query re-execution

[Philip Stoev]

The following RQG command line

perl runall.pl --queries=1M --duration=300 --threads=1 --seed=time --reporter=Deadlock,Backtrace,ErrorLog --validator=Transformer --mysqld=--optimizer_switch=join_cache_hashed=off --notnull --engine=InnoDB --mysqld=--join_cache_level=6 --mysqld=--join_buffer_size=10K --grammar=conf/optimizer/outer_join.yy --gendata=conf/optimizer/outer_join.zz --queries=100000000 --basedir=/home/philips/bzr/maria-5.3 --seed=1287834481 --views --valgrind --duration=9000

reports a sporadic result difference in the following query:

# 2010-10-23T22:14:20 Original query: SELECT SQL_SMALL_RESULT table1 . `col_int` AS field1 , table2 . `col_int` AS field2 , table1 . `col_int` AS field3 , table1 . `col_int` AS field4 , table2 . `pk` AS field5 , table1 . `pk` AS field6 FROM vV_0 AS table1 RIGHT JOIN EE AS table2 LEFT JOIN vZ_0 AS table3 ON table2 . `col_int_key` = table3 . `pk` ON table1 . `pk` = table3 . `pk` LEFT JOIN vR_0 AS table4 ON table3 . `pk` = table4 . `col_int` WHERE table4 . `pk` <> 3 HAVING field3 <> 8 ORDER BY field1 DESC , field1 DESC , field2 failed transformation with Transformer DisableChosenPlan

# 2010-10-23T22:14:20 Transformed query: /* GenTest::Transform::DisableChosenPlan */ SET @switch_saved = @@optimizer_switch;; SET SESSION optimizer_switch='join_cache_bka=off';; SELECT SQL_SMALL_RESULT table1 . `col_int` AS field1 , table2 . `col_int` AS field2 , table1 . `col_int` AS field3 , table1 . `col_int` AS field4 , table2 . `pk` AS field5 , table1 . `pk` AS field6 FROM vV_0 AS table1 RIGHT JOIN EE AS table2 LEFT JOIN vZ_0 AS table3 ON table2 . `col_int_key` = table3 . `pk` ON table1 . `pk` = table3 . `pk` LEFT JOIN vR_0 AS table4 ON table3 . `pk` = table4 . `col_int` WHERE table4 . `pk` <> 3 HAVING field3 <> 8 ORDER BY field1 DESC , field1 DESC , field2 /* TRANSFORM_OUTCOME_UNORDERED_MATCH */ ;; SET SESSION optimizer_switch=@switch_saved

Unfortunately the failure does not repeat when the query is executed again or is run standalone as an MTR test case. Valgrind also does not report anything.

[Philip Stoev (pstoev-askmonty)]

Ok, here is what happens:

1. First some Query 1 is run.
2. Then, another Query 2 is run twice.
3. The two runs of Query 2 produce different results.

No join_cache , bka , MRR or other configurable optimizations seem to be involved. maria-5.3 is affected, but maria-5.2 and mysql-next-mr are not.

[Philip Stoev (pstoev-askmonty)]

Apologies for the rather verbose test case, but note that the initial test case contained over 10K queries.

[Philip Stoev (pstoev-askmonty)]

A much simpler test case:

--source include/have_innodb.inc

SET SESSION join_cache_level=0;
SET SESSION optimizer_use_mrr='disable';

CREATE TABLE `B` ( `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, pk integer auto_increment, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, `col_int` int, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_int_key` int, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, /*Indices*/ key (`col_varchar_10_latin1_key` ), primary key (pk), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_10_utf8_key` ), key (`col_int_key` ), key (`col_varchar_1024_latin1_key` )) ENGINE=innodb;
CREATE TABLE `R` ( `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, pk integer auto_increment, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_int` int, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, `col_int_key` int, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, /*Indices*/ key (`col_varchar_1024_latin1_key` ), primary key (pk), key (`col_int_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_10_latin1_key` ), key (`col_varchar_10_utf8_key` )) ENGINE=innodb;
INSERT /*! IGNORE */ INTO R VALUES ('d', NULL, 'abcdefghij', 0, 'of', 1, 'k', 'g', 'w', 'h', 'they') ,
 ('l', NULL, 'abcdefghij', -1023016960, 'time', 4, 'abcdefghij', 'w', 'abcdefghijabcdefghij', 'been', 'abcdefghijonchz'),
  ('with', NULL, 's', 2139095040, 'lwvynbkqxkcpq', 6, 'so', 'abcdefghij', 'on', 'abcdefghij', 'abcdefghij') ,
  ('now', NULL, 'good', 2, 'y', -1275002880, 'b', 'well', 'then', 'abcdefghij', 'abcdefghij');

CREATE TABLE `V` ( `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_int` int, `col_int_key` int, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, pk integer auto_increment, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, /*Indices*/ key (`col_int_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_varchar_1024_latin1_key` ), primary key (pk), key (`col_varchar_10_utf8_key` ), key (`col_varchar_10_latin1_key` )) ENGINE=innodb;

INSERT /*! IGNORE */ INTO V VALUES ('abcdefghij', -1185611776, 6, 'abcdefghij', 'i', 'o', 'do', NULL, 'abcdefghij', 'c', 'o')
, ('q', 1823145984, -1234632704, 'ok', 'your', 'all', 'r', NULL, 'a', 't', 'abcdefghij')
, ('e', 749993984, -799604736, 'abcdefghij', 'abcdefghij', 'b', 'for', NULL, 't', 'n', 'abcdefghij')...

[Philip Stoev (pstoev-askmonty)]

Even simpler test case:

--source include/have_innodb.inc

CREATE TABLE t1 ( f1 int, f2 int, f4 int, f6 int, PRIMARY KEY (f2), KEY (f6)) ENGINE=InnoDB;
INSERT IGNORE INTO t1 VALUES ('0','4','2','0');

CREATE TABLE t2 (f2 int);
INSERT IGNORE INTO t2 VALUES ('2');

CREATE TABLE t3 (f4 int, f2 int) ;

CREATE TABLE t4 ( f4 int, f6 int) ;
INSERT IGNORE INTO t4 VALUES ('2','6');

CREATE TABLE t5 (f11 int) ENGINE=InnoDB;

SELECT * FROM t5 AS table1 LEFT JOIN t1 AS table2 ON table1 .`f11` = table2 .`f1` WHERE table2 .`f6` > 2;

CREATE TABLE p1 AS SELECT table1.f4 AS field1 , table2.f4 AS field2 , table1.f4 AS field3 FROM t3 table1 RIGHT JOIN t4 table2 JOIN t2 table3 ON table2.f6 ON table1.f2 = table3.f2 LEFT JOIN t1 AS table4 ON table3.f2 = table4.f4 WHERE table4.f2 <> 3;
CREATE TABLE p2 AS SELECT table1.f4 AS field1 , table2.f4 AS field2 , table1.f4 AS field3 FROM t3 table1 RIGHT JOIN t4 table2 JOIN t2 table3 ON table2.f6 ON table1.f2 = table3.f2 LEFT JOIN t1 AS table4 ON table3.f2 = table4.f4 WHERE table4.f2 <> 3;

--let $diff_table_1 = test.p1
--let $diff_table_2 = test.p2
--source include/diff_tables.inc

[Philip Stoev (pstoev-askmonty)]

Igor, this is not a join_cache bug, however the sporadic nature of the failure prevents the analysis of various sporadic failurs that were encountered during join_cache testing (which may or may not be manifestations of the same bug). So I will appreciate it if you can take a look.

[Philip Stoev (philip-stoev)]

This bug is still repeatable on the latest maria-5.3 that has wl128 and dsmrr-cpk trees merged in. Usually, the EXPLAIN contains either "Using MRR" or "Using join buffer - flat BNL"

[Igor Babaev (igorb-seattle)]

This problem easily can be reproduced for the current 5.3 tree on 64-bit Linux with the following simple test case:

create table t1 (pk int primary key, b int, c int default 0, index idx(b)) engine=innodb;
insert into t1(pk,b) values (3, 30), (2, 20), (9, 90), (7, 70), (4, 40), (5, 50), (10, 100), (12, 120);
set optimizer_use_mrr='disable';
set join_cache_level=0;
explain extended select * from t1 where b > 1000;
select * from t1 where pk < 2 or pk between 3 and 4;
select * from t1 where pk < 2 or pk between 3 and 4;

The last 3 commands return:
MariaDB [test]> explain extended select * from t1 where b > 1000;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | range | idx | idx | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4;
+----+------+------+
| pk | b | c |
+----+------+------+
| 3 | 30 | 0 |
| 4 | 40 | 0 |
| 3 | 30 | 0 |
| 4 | 40 | 0 |
+----+------+------+
4 rows in set (0.02 sec)

MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4;
+----+------+------+
| pk | b | c |
+----+------+------+
| 3 | 30 | 0 |
| 4 | 40 | 0 |
+----+------+------+
2 rows in set (0.01 sec)

[Igor Babaev (igorb-seattle)]

The wrong results are triggered by an invalid value of the flag handler::in_range_check_pushed_down just before
the scan of the range pk < 2 when executing the first select query. The value of the flag is TRUE though it is expected
to be FALSE. It is FALSE because the handler is taken from the previous (EXPLAIN) command that calls
ha_innobase::idx_cond_push() setting in_range_check_pushed_down to TRUE, but never calls ha_innobase::index_end
that would reset the value of the flag back to FALSE.