Bug #40154 main.subselect_sj2 fails sporadically on pushbuild
Submitted: 19 Oct 2008 21:05 Modified: 25 Nov 2008 11:25
Reporter: Sven Sandberg
Status: Verified
Category:Tests: Server Severity:S2 (Serious)
Version:6.0 OS:Any
Assigned to: Bugs System Target Version:
Tags: pushbuild, test failure, sporadic, subselect_sj2, disabled
Triage: Triaged: D2 (Serious)

[19 Oct 2008 21:05] Sven Sandberg 
Description:
main.subselect_sj2 fails sporadically on pushbuild with the following message:

CURRENT_TEST: main.subselect_sj2
---
C:/cygwin/home/pushbuild/pb1/pb/bzr_mysql-6.0-rpl/87/mysql-6.0.8-alpha-pb87/mysql-test/r/subselect_sj2.result	2008-10-14
13:12:29.000000000 +0300
+++
C:\cygwin\home\pushbuild\pb1\pb\bzr_mysql-6.0-rpl\87\mysql-6.0.8-alpha-pb87\mysql-test\r\subselect_sj2.reject	2008-10-14
22:22:35.211750000 +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);

mysqltest: Result content mismatch

 - saving 'e:/var-n_mix-100/log/main.subselect_sj2/' to
'e:/var-n_mix-100/log/main.subselect_sj2/'

Retrying test, attempt(2/3)...

How to repeat:
https://intranet.mysql.com/secure/pushbuild/showpush.pl?dir=bzr_mysql-6.0-rpl&order=87
vm-win2003-32-a/n_mix
xref: http://tinyurl.com/58qo4z
[20 Oct 2008 16: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 19: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 21: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 21: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 14:43] Alexander Nozdrin 
A part of the test case was disabled.