Backtrace:

#3 <signal handler called>
#4 0x000000000071d973 in cp_buffer_from_ref (thd=0x17cb0128, table=0x17dcd910, ref=0x17dd9a00) at sql_select.cc:18450
#5 0x00000000007fb750 in get_quick_select_for_ref (thd=0x17cb0128, table=0x17dcd910, ref=0x17dd9a00, records=2) at opt_range.cc:10000
#6 0x0000000000741db2 in create_sort_index (thd=0x17cb0128, join=0x17dc14b0, order=0x17dbf3a0, filesort_limit=18446744073709551615,
select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:17954
#7 0x000000000075499e in JOIN::exec (this=0x17dc14b0) at sql_select.cc:2611
#8 0x000000000074ec60 in mysql_select (thd=0x17cb0128, rref_pointer_array=0x17cb2cd8, tables=0x17dbd910, wild_num=0, fields=..., conds=0x17dbfed8,
og_num=1, order=0x17dbf3a0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x17dc0168, unit=0x17cb25d0, select_lex=0x17cb2ab8)
at sql_select.cc:2889
#9 0x0000000000755002 in handle_select (thd=0x17cb0128, lex=0x17cb2530, result=0x17dc0168, setup_tables_done_option=0) at sql_select.cc:283
#10 0x00000000006a1fdc in execute_sqlcom_select (thd=0x17cb0128, all_tables=0x17dbd910) at sql_parse.cc:5087
#11 0x00000000006a3cba in mysql_execute_command (thd=0x17cb0128) at sql_parse.cc:2231
#12 0x00000000006aca53 in mysql_parse (thd=0x17cb0128,
rawbuf=0x17d35ba0 "SELECT alias1.f1 AS field1\nFROM\n(\nSELECT DISTINCT t1.\nFROM t1,t3\nWHERE t3.f2 = t1.f2 )\nAS alias1 , \n(\nSELECT t5.\nFROM t4, t5\nWHERE t5.f2 = t4.f2 )\nAS alias2 WHERE alias2.f2 = alias1.f3 \nORDER BY fi"..., length=204, found_semicolon=0x4238cf08) at sql_parse.cc:6088
#13 0x00000000006ad8eb in dispatch_command (command=COM_QUERY, thd=0x17cb0128,
packet=0x17d2c869 "SELECT alias1.f1 AS field1\nFROM\n(\nSELECT DISTINCT t1.\nFROM t1,t3\nWHERE t3.f2 = t1.f2 )\nAS alias1 , \n(\nSELECT t5.\nFROM t4, t5\nWHERE t5.f2 = t4.f2 )\nAS alias2 WHERE alias2.f2 = alias1.f3 \nORDER BY fi"..., packet_length=205) at sql_parse.cc:1208
#14 0x00000000006aeef9 in do_command (thd=0x17cb0128) at sql_parse.cc:906
#15 0x000000000069996f in handle_one_connection (arg=0x17cb0128) at sql_connect.cc:1178
#16 0x00000033b600673d in start_thread () from /lib64/libpthread.so.0
#17 0x00000033b58d40cd in clone () from /lib64/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where; Using filesort
1 PRIMARY t5 ref f2 f2 4 alias1.f3 1
1 PRIMARY t4 ref f2 f2 4 alias1.f3 2 Using index
2 DERIVED t3 system NULL NULL NULL NULL 1 Using temporary
2 DERIVED t1 ref f2 f2 4 const 2 Using where

test case:

CREATE TABLE t1 ( f1 varchar(1), f2 varchar(1), f3 varchar(1), KEY (f2));
INSERT INTO t1 VALUES (8,'x','x'),(7,'d','d'),(1,'r','r'),(7,'f','f'),(9,'y','y'),(NULL,'u','u'),(1,'m','m'),(9,NULL,NULL),(2,'o','o'),(9,'w','w'),(2,'m','m'),(4,'q','q'),(0,NULL,NULL),(4,'d','d'),(8,'g','g'),(NULL,'x','x'),(NULL,'f','f'),(0,'p','p'),(NULL,'j','j'),(8,'c','c');

CREATE TABLE t3 ( f1 varchar(1), f2 varchar(1));
INSERT INTO t3 VALUES (7,'x');

CREATE TABLE t4 ( f1 varchar(1), f2 varchar(1), f3 varchar(1) , KEY (f2));
INSERT INTO t4 VALUES (8,'x','x'),(7,'d','d'),(1,'r','r'),(7,'f','f'),(9,'y','y'),(NULL,'u','u'),(1,'m','m'),(9,NULL,NULL),(2,'o','o'),(9,'w','w'),(2,'m','m'),(4,'q','q'),(0,NULL,NULL),(4,'d','d'),(8,'g','g'),(NULL,'x','x'),(NULL,'f','f'),(0,'p','p'),(NULL,'j','j'),(8,'c','c');

CREATE TABLE t5 ( f1 int, f2 varchar(1), f3 varchar(1), KEY (f2,f1)) ;
INSERT INTO t5 VALUES (1,NULL,NULL),(5,'r','r');

SELECT alias1.f1 AS field1 FROM
(
SELECT DISTINCT t1.*
FROM t1,t3
WHERE t3.f2 = t1.f2 )
AS alias1 ,
(
SELECT t5.*
FROM t4, t5
WHERE t5.f2 = t4.f2 )
AS alias2 WHERE alias2.f2 = alias1.f3
ORDER BY field1 ;

bzr version-info:

revision-id: <email address hidden>
date: 2011-07-01 15:35:34 +0300
build-date: 2011-07-01 23:01:13 +0300
revno: 3074
branch-nick: maria-5.3