repeatedly losing connection to server during queries with left join

[Aarti Pai]

Refer to subselect_sj.test

Repeatedly losing connection to server during queries with left joins
All such instances have been commented out.

drizzletest: At line 76: query 'select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5' failed: 2013: Lost connection to Drizzle server during query

The result from queries just before the failure was:
< snip >
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
0 0
1 1
2 2
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10` join `test`.`t12`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
subqueries within outer joins go into ON expr.
select * from
t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10))
where t1.A < 5;

More results from queries before failure can be found in /export/home/bzrwork/drizzle/tests/var/log/subselect_sj.log

[Jay Pipes (jaypipes)]

Confirmed on latest trunk. Investigating cause now. Setting to critical since it is a crashing bug.

[Jay Pipes (jaypipes)]

GDB stacktrace output:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x404a1950 (LWP 1693)]
0x00000000004bfc9d in replace_where_subcondition (join=0x21af660,
    old_cond=0x21a1ab8, new_cond=0x21b3220, do_fix_fields=false)
    at sql_select.cc:12371
12371 if (join->conds->type() == Item::COND_ITEM) {
(gdb) where
#0 0x00000000004bfc9d in replace_where_subcondition (join=0x21af660,
    old_cond=0x21a1ab8, new_cond=0x21b3220, do_fix_fields=false)
    at sql_select.cc:12371
#1 0x00000000004dc565 in JOIN::flatten_subqueries (this=0x21af660)
    at sql_select.cc:3113
#2 0x00000000004e1d6d in mysql_select (session=0x1c46290,
    rref_pointer_array=0x1c47a38, tables=0x21a0638, wild_num=1,
    fields=@0x1c47958, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2147500032, result=0x21a2018,
    unit=0x1c475d8, select_lex=0x1c47860) at sql_select.cc:2688
#3 0x00000000004e7140 in handle_select (session=0x1c46290, lex=0x1c475b8,
    result=0x21a2018, setup_tables_done_option=0) at sql_select.cc:299
#4 0x00000000004af6b3 in execute_sqlcom_select (session=0x1c46290,
    all_tables=0x21a0638) at sql_parse.cc:2094
#5 0x00000000004b0257 in mysql_execute_command (session=0x1c46290)
    at sql_parse.cc:1044
#6 0x00000000004b359d in mysql_parse (session=0x1c46290,
    inBuf=0x21a0440 "select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and B.a in (select pk from t10))", length=87, found_semicolon=0x404a0e28)
    at sql_parse.cc:2334
#7 0x00000000004b3cc7 in dispatch_command (command=COM_QUERY,
    session=0x1c46290,
    packet=0x2198411 "select * from t1 left join (t2 A, t2 B) on ( A.a= t1.a and---Type <return> to continue, or q <return> to quit---
 B.a in (select pk from t10))", packet_length=87) at sql_parse.cc:587
#8 0x00000000004b474f in do_command (session=0x1c46290) at sql_parse.cc:409
#9 0x00000000005e6829 in libevent_thread_proc (arg=0x0)
    at pool_of_threads.cc:452
#10 0x00007f6f36e363ea in start_thread () from /lib/libpthread.so.0
#11 0x00007f6f36ba3c6d in clone () from /lib/libc.so.6
#12 0x0000000000000000 in ?? ()

[Jay Pipes (jaypipes)]

Assigning to myself. I have tracked down a solution and have a local fix. Testing now.

[Jay Pipes (jaypipes)]

Fix is in ~jaypipes/drizzle/bug-fixes.