Re: Sporadic result differences on query re-execution
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.

Re: Sporadic result differences on query re-execution
Apologies for the rather verbose test case, but note that the initial test case contained over 10K queries.

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

Re: Sporadic result differences on query re-execution
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') ;

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

INSERT /*! IGNORE */ INTO Z VALUES (-810876928, 'o', -783155200, 's', 'because', 'is', 'c', 'well', 'c', 'n', NULL)
, (947060736, 'like', -1097269248, 'ok', 'abcdefghij', 'abcdefghij', 'abcdefghij', 'q', 'be', 'up', NULL)
, (803209216, 'w', 0, 'he', 'abcdefghij', 'r', 'abcdefghij', 'had', 't', 'now', NULL) ;

CREATE TABLE `EE` ( `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1, `col_int` int, `col_varchar_1024_utf8_key` varchar(10) CHARACTER SET utf8, `col_varchar_1024_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_1024_utf8` varchar(10) CHARACTER SET utf8, `col_int_key` int, `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1, `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8, `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8, pk integer auto_increment, `col_varchar_1024_latin1_key` varchar(10) CHARACTER SET latin1, /Indices/ key (`col_varchar_10_latin1_key` ), key (`col_varchar_1024_utf8_key` ), key (`col_int_key` ), key (`col_varchar_10_utf8_key` ), primary key (pk), key (`col_varchar_1024_latin1_key` )) ENGINE=myisam;
INSERT /*! IGNORE */ INTO EE VALUES ('as', 6, 'abcdefghij', 'abcdefghij', 's', -1884815360, 'r', 'abcdefghij', 'abcdefghij', NULL, 'abcdefghij')
, ('been', 2, 'abcdefghij', 'y', 'abcdefghij', 673906688, 'it', 'n', 'abcdefghij', NULL, 'abcdefghij')
;

--error 0
SELECT *
FROM B AS table1
LEFT JOIN R AS table2 ON table1 .`col_varchar_10_utf8_key` = table2 .`col_varchar_1024_latin1_key`
WHERE table2 .`col_int_key` > 2;

--error 0
CREATE TABLE p1 AS
SELECT table1 .`col_int` field1 , table2 .`col_int` field2 , table1 .`col_int` field3
FROM V table1
RIGHT JOIN EE table2
JOIN Z table3 ON table2 .`col_int_key`
ON table1 .`pk` = table3 .`pk`
LEFT JOIN R AS table4
ON table3 .`pk` = table4 .`col_int`
WHERE table4 .`pk` <> 3 ;

--error 0

CREATE TABLE p2 AS
SELECT table1 .`col_int` field1 , table2 .`col_int` field2 , table1 .`col_int` field3
FROM V table1
RIGHT JOIN EE table2
JOIN Z table3 ON table2 .`col_int_key`
ON table1 .`pk` = table3 .`pk`
LEFT JOIN R AS table4
ON table3 .`pk` = table4 .`col_int`
WHERE table4 .`pk` <> 3 ;

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

Re: Sporadic result differences on query re-execution
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

Re: Sporadic result differences on query re-execution
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.

Re: Result differences on query re-execution
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"

Re: Result differences on query re-execution
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;
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)

MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4;
------------

------------

------------
4 rows in set (0.02 sec)

MariaDB [test]> select * from t1 where pk < 2 or pk between 3 and 4;
------------

------------

------------
2 rows in set (0.01 sec)

Re: Result differences on query re-execution
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.

Launchpad bug id: 665669