Bug #40154 | main.subselect_sj2 fails sporadically on pushbuild | ||
---|---|---|---|
Submitted: | 19 Oct 2008 19:05 | Modified: | 22 Nov 2010 1:25 |
Reporter: | Sven Sandberg | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 6.0 | OS: | Any |
Assigned to: | Olav Sandstå | CPU Architecture: | Any |
Tags: | disabled, pushbuild, sporadic, subselect_sj2, test failure |
[19 Oct 2008 19:05]
Sven Sandberg
[20 Oct 2008 14:11]
Sergey Petrunya
Analysis of where this happens: * One failure in -falcon tree, on sapsrv2-max, following the pattern pasted below * One failure in -rpl tree, on sapsrv2-max, timeout * The rest are in -rpl tree on 32-bit windows, where all failures are the same result difference: main.subselect_sj2 [ fail ] CURRENT_TEST: main.subselect_sj2 --- e:/pb/bzr_mysql-6.0-rpl/85/mysql-6.0.8-alpha-pb85/mysql-test/r/subselect_sj2.result 2008-10-11 19:37:25.000000000 +0300 +++ e:\pb\bzr_mysql-6.0-rpl\85\mysql-6.0.8-alpha-pb85\mysql-test\r\subselect_sj2.reject 2008-10-11 21:18:01.238250000 +0300 @@ -79,20 +79,20 @@ 1 PRIMARY t3 ref b b 5 test.t0.a 1 End temporary select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); a b pk1 pk2 -0 0 0 0 -1 1 1 1 -2 2 2 2 -3 3 3 3 -4 4 4 4 -5 5 5 5 -6 6 6 6 -7 7 7 7 -8 8 8 8 -9 9 9 9 -10 10 10 10 -11 11 11 11 -12 12 12 12 -13 13 13 13 +0 NULL 0 0 +1 NULL 1 1 +2 NULL 2 2 +3 NULL 3 3 +4 NULL 4 4 +5 NULL 5 5 +6 NULL 6 6 +7 NULL 7 7 +8 NULL 8 8 +9 NULL 9 9 +10 NULL 10 10 +11 NULL 11 11 +12 NULL 12 12 +13 NULL 13 13 set join_buffer_size= @save_join_buffer_size; set max_heap_table_size= @save_max_heap_table_size; explain select * from t1 where a in (select b from t2);
[11 Feb 2009 18:57]
Sergey Petrunya
EXPLAIN of the failing query: explain select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Start temporary 1 PRIMARY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer 1 PRIMARY t3 ref b b 5 func 1 Using index condition; End temporary
[11 Feb 2009 20:07]
Sergey Petrunya
I've got a setting where the problem is repeatable: * machine win2003-x86, * A tree from here: https://intranet.mysql.com/secure/pushbuild/getlog.pl?dir=bzr_mysql-6.0-perf&entry=Marc_Al... * Build a release build in the same way as pushbuild did * Run this subset of tests: ./mysql-test-run.pl --suite=main --ps-protocol --mysqld=--binlog-format=row --force --testcase-timeout=900 t/st*.test t/su*.test * Important: do not have visual studio started until the code has reached JOIN::optimize() in the problematic query.
[11 Feb 2009 20:08]
Sergey Petrunya
And with those settings, the problem disappears if I use the debugger to manually disable index condition pushdown. It seems the problem is in innodb + index condition pushdown, a known problematic area.
[24 Feb 2009 13:43]
Alexander Nozdrin
A part of the test case was disabled.
[4 Jun 2010 14:16]
Olav Sandstå
The part of the test that triggered this failure is still disabled. We have fixed multiple bugs in the area of ICP for InnoDB that could have caused this failure so it is possible that this problem now is gone. My plan is to re-enable the disabled part of the test to verify if this bug is still re-producible.
[7 Jun 2010 11:42]
Olav Sandstå
I have removed the disabling of ICP for the select query that earlier failed randomly and done the following to "verify" that the test no longer fails: 1. Verified that the query still will use ICP. 2. Run the test a few hundred times on Solaris without seeing any failures. 3. Run the test with valgrind without seeing any warnings.
[7 Jun 2010 11:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/110344 3187 Olav Sandstaa 2010-06-07 Patch for Bug #40154 main.subselect_sj2 fails sporadically on pushbuild With Index Condition Pushdown (ICP) enabled one of the select queries in subselect_sj2 failed randomly. As a "work-around" for this test failure, ICP has earlier been disabled for this select query. The cause for the test failure in subselect_sj2 has likely been fixed by one of the earlier bug fixes related to ICP and InnoDB. This patch removes the previously introduced disabling of ICP for this select query. @ mysql-test/r/subselect_sj2.result Updated result file after removing disabling of ICP for one of the select queries. @ mysql-test/r/subselect_sj2_jcl6.result Updated result file after removing disabling of ICP for one of the select queries. @ mysql-test/t/subselect_sj2.test Remove previously disabling of ICP for a single select query that earlier made the test fail randomly.
[7 Jun 2010 11:56]
Øystein Grøvlen
Approved. One reviewer should be enough here.
[16 Aug 2010 6:31]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:04]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:25]
Paul DuBois
Bug is not in any released 5.6.x version. No changelog entry needed.