LP:902356 - Crash in test_if_skip_sort_order with DISTINCT, view, InnoDB, on 64bit

#3 <signal handler called>
#4 0x000000000079b7ea in test_if_skip_sort_order (tab=0x304b4c8,
order=0x304bbc8, select_limit_arg=18446744073709551615, no_changes=false,
map=0x2fe6e08) at sql_select.cc:17834
#5 0x0000000000772075 in JOIN::optimize (this=0x3038190) at sql_select.cc:1617
#6 0x00000000005d2f59 in st_select_lex::optimize_unflattened_subqueries (
this=0x2e1d510) at sql_lex.cc:3127
#7 0x000000000085b969 in JOIN::optimize_unflattened_subqueries (
this=0x3030070) at opt_subselect.cc:4534
#8 0x0000000000771c01 in JOIN::optimize (this=0x3030070) at sql_select.cc:1566
#9 0x0000000000776c07 in mysql_select (thd=0x2e1ab88,
rref_pointer_array=0x2e1d760, tables=0x2faa3c8, wild_num=1, fields=...,
conds=0x2fac078, og_num=0, order=0x0, group=0x0, having=0x0,
proc_param=0x0, select_options=2147764736, result=0x2fe4a28,
unit=0x2e1d028, select_lex=0x2e1d510) at sql_select.cc:2951
#10 0x000000000076d5dd in handle_select (thd=0x2e1ab88, lex=0x2e1cf88,
result=0x2fe4a28, setup_tables_done_option=0) at sql_select.cc:283
#11 0x00000000006fc744 in execute_sqlcom_select (thd=0x2e1ab88,
all_tables=0x2faa3c8) at sql_parse.cc:5112
#12 0x00000000006f381a in mysql_execute_command (thd=0x2e1ab88)
at sql_parse.cc:2250
#13 0x00000000006ff124 in mysql_parse (thd=0x2e1ab88,
rawbuf=0x2faa160 "SELECT * FROM t1, t2\nWHERE ( 2, 9 ) IN\n( SELECT DISTINCT a, pk FROM view_t1 ) \nOR a = b", length=87, found_semicolon=0x7fac5453dc98)
at sql_parse.cc:6113
#14 0x00000000006f1032 in dispatch_command (command=COM_QUERY, thd=0x2e1ab88,
packet=0x2fffa59 "SELECT * FROM t1, t2\nWHERE ( 2, 9 ) IN\n( SELECT DISTINCT a, pk FROM view_t1 ) \nOR a = b", packet_length=87) at sql_parse.cc:1221
#15 0x00000000006f0366 in do_command (thd=0x2e1ab88) at sql_parse.cc:916
#16 0x00000000006ed2bc in handle_one_connection (arg=0x2e1ab88)
at sql_connect.cc:1191
#17 0x00007fac5381ca4f in start_thread () from /lib64/libpthread.so.0
#18 0x00007fac52c0682d in clone () from /lib64/libc.so.6

I could only reproduce it on one of two machines that I currently have access to:
openSUSE 11.3 (x86_64) – reproducible
Fedora 12 32-bit – not reproducible
Judging by the indicative number on the top of the stack trace, my guess is it's 64bit that makes the difference. However, if you cannot repeat it, please let me know and I will try to find more machines to experiment with.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-08 04:22:38 +0400
build-date: 2011-12-09 23:25:32 +0200
revno: 3337
branch-nick: maria-5.3

Also reproducible on 5.3.2 release if materialization=on,in_to_exists=on.

Minimal optimizer_switch: none
(It looks like materialization=on,in_to_exists=on are required, but they are defaults)

Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

EXPLAIN also crashes.

Test case:

--source include/have_innodb.inc

1. t1 must be InnoDB
   CREATE TABLE t1 ( pk INT PRIMARY KEY, a INT, KEY(a) )
   ENGINE=InnoDB;
   INSERT INTO t1 VALUES (0, 4),(8, 6);

CREATE TABLE t2 ( b INT, KEY(b) );
INSERT INTO t2 VALUES (7),(0);

1. The view needs to be UNDEFINED or MERGE
   CREATE VIEW view_t1 AS SELECT * FROM t1;

SELECT * FROM t1, t2
WHERE ( 2, 9 ) IN
( SELECT DISTINCT a, pk FROM view_t1 )
OR a = b;