Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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;
--------------------------------------------------------------------------------------
| 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)
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
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.