Crash in QUICK_RANGE_SELECT::reset with derived_with_keys

[Philip Stoev]

backtrace:

#4 <signal handler called>
#5 0x083d3cbd in QUICK_RANGE_SELECT::reset (this=0xae734fe0) at opt_range.cc:10827
#6 0x083eb06a in find_all_keys (param=0xae8fdef4, select=0xae536ea0, sort_keys=0xae54d190, buffpek_pointers=0xae8fdd2c, tempfile=0xae8fde10, indexfile=0x0)
    at filesort.cc:561
#7 0x083ea0bf in filesort (thd=0x9a286f8, table=0xae72a018, sortorder=0xae734aa0, s_length=1, select=0xae536ea0, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xae8fe060) at filesort.cc:240
#8 0x08345291 in create_sort_index (thd=0x9a286f8, join=0xae72d8a8, order=0xae714350, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:18125
#9 0x08321d9a in JOIN::exec (this=0xae72d8a8) at sql_select.cc:2622
#10 0x08322872 in mysql_select (thd=0x9a286f8, rref_pointer_array=0x9a2a274, tables=0xae712840, wild_num=1, fields=..., conds=0x0, og_num=1,
    order=0xae714350, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae7143f8, unit=0x9a29e58, select_lex=0x9a2a138)
    at sql_select.cc:2900
#11 0x0831a76f in handle_select (thd=0x9a286f8, lex=0x9a29dfc, result=0xae7143f8, setup_tables_done_option=0) at sql_select.cc:283
#12 0x082b4ef0 in execute_sqlcom_select (thd=0x9a286f8, all_tables=0xae712840) at sql_parse.cc:5112
#13 0x082abcad in mysql_execute_command (thd=0x9a286f8) at sql_parse.cc:2250
#14 0x082b751f in mysql_parse (thd=0x9a286f8,
    rawbuf=0xae7125f0 "SELECT *\nFROM t3 JOIN\n( SELECT t2.* FROM t1, t2 ) AS alias2\nON ( alias2.col_varchar_nokey = t3.col_varchar_key ) \nORDER BY CONCAT(alias2.col_varchar_nokey)", length=155, found_semicolon=0xae8ff228) at sql_parse.cc:6112
#15 0x082a98fc in dispatch_command (command=COM_QUERY, thd=0x9a286f8,
    packet=0xae721329 "SELECT *\nFROM t3 JOIN\n( SELECT t2.* FROM t1, t2 ) AS alias2\nON ( alias2.col_varchar_nokey = t3.col_varchar_key ) \nORDER BY CONCAT(alias2.col_varchar_nokey)", packet_length=155) at sql_parse.cc:1221
#16 0x082a8d57 in do_command (thd=0x9a286f8) at sql_parse.cc:916
#17 0x082a5d3b in handle_one_connection (arg=0x9a286f8) at sql_connect.cc:1191
#18 0x00821919 in start_thread () from /lib/libpthread.so.0
#19 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 system NULL NULL NULL NULL 1 Using filesort
1 PRIMARY <derived2> ref key0 key0 55 const 10 Using where
2 DERIVED t1 ALL NULL NULL NULL NULL 12
2 DERIVED t2 ALL NULL NULL NULL NULL 841 Using join buffer (flat, BNL join)

minimal switch: derived_merge=on
full 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=off,derived_merge=off,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,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

bzr version-info
revision-id: <email address hidden>
date: 2011-10-28 11:23:30 +0400
build-date: 2011-10-28 12:15:13 +0300
revno: 3257
branch-nick: maria-5.3

[Philip Stoev (pstoev-askmonty)]

test case:

CREATE TABLE t2 ( pk varchar(33), col_varchar_key varchar(3) NOT NULL , col_varchar_nokey varchar(52) NOT NULL ) ;
INSERT INTO t2 VALUES ('NICSpanish','NIC','Spanish'),('NERHausa','NER','Hausa'),('NGAJoruba','NGA','Joruba'),('NIUNiue','NIU','Niue'),('NFKEnglish','NFK','English'),('NORNorwegian','NOR','Norwegian'),('CIVAkan','CIV','Akan'),('OMNArabic','OMN','Arabic'),('PAKPunjabi','PAK','Punjabi'),('PLWPalau','PLW','Palau'),('PANSpanish','PAN','Spanish'),('PNGPapuan Langua','PNG','Papuan Languages'),('PRYSpanish','PRY','Spanish'),('PERSpanish','PER','Spanish'),('PCNPitcairnese','PCN','Pitcairnese'),('MNPPhilippene La','MNP','Philippene Langu'),('PRTPortuguese','PRT','Portuguese'),('PRISpanish','PRI','Spanish'),('POLPolish','POL','Polish'),('GNQFang','GNQ','Fang'),('QATArabic','QAT','Arabic'),('FRAFrench','FRA','French'),('GUFCreole French','GUF','Creole French'),('PYFTahitian','PYF','Tahitian'),('REUCreole French','REU','Creole French'),('ROMRomanian','ROM','Romanian'),('RWARwanda','RWA','Rwanda'),('SWESwedish','SWE','Swedish'),('SHNEnglish','SHN','English'),('KNACreole Englis','KNA','Creole English'),('LCACreole French','LCA','Creole French'),('VCTCreole Englis','VCT','Creole English'),('SPMFrench','SPM','French'),('DEUGerman','DEU','German'),('SLBMalenasian La','SLB','Malenasian Langu'),('ZMBBemba','ZMB','Bemba'),('WSMSamoan-Englis','WSM','Samoan-English'),('SMRItalian','SMR','Italian'),('STPCrioulo','STP','Crioulo'),('SAUArabic','SAU','Arabic'),('SENWolof','SEN','Wolof'),('SYCSeselwa','SYC','Seselwa'),('SLEMende','SLE','Mende'),('SGPChinese','SGP','Chinese'),('SVKSlovak','SVK','Slovak'),('SVNSlovene','SVN','Slovene'),('SOMSomali','SOM','Somali'),('LKASingali','LKA','Singali'),('SDNArabic','SDN','Arabic'),('FINFinnish','FIN','Finnish'),('SURSranantonga','SUR','Sranantonga'),('SWZSwazi','SWZ','Swazi'),('CHEGerman','CHE','German'),('SYRArabic','SYR','Arabic'),('TJKTadzhik','TJK','Tadzhik'),('TWNMin','TWN','Min'),('TZANyamwesi','TZA','Nyamwesi'),('DNKDanish','DNK','Danish'),('THAThai','THA','Thai'),('TGOEwe','TGO','Ewe'),('TKLTokelau','TKL','Tokelau'),('TONTongan','TON','Tongan'),('TTOEnglish','TTO','English'),('TCDSara','TCD','Sara'),('CZECzech','CZE','Czech'),('TUNArabic','TUN','Arabic'),('TURTurkish','TUR','Turkish'),('TKMTurkmenian','TKM','Turkmenian'),('TCAEnglish','TCA','English'),('TUVTuvalu','TUV','Tuvalu'),('UGAGanda','UGA','Ganda'),('UKRUkrainian','UKR','Ukrainian'),('HUNHungarian','HUN','Hungarian'),('URYSpanish','URY','Spanish'),('NCLMalenasian La','NCL','Malenasian Langu'),('NZLEnglish','NZL','English'),('UZBUzbek','UZB','Uzbek'),('BLRBelorussian','BLR','Belorussian'),('WLFWallis','WLF','Wallis'),('VUTBislama','VUT','Bislama'),('VATItalian','VAT','Italian'),('VENSpanish','VEN','Spanish'),('RUSRussian','RUS','Russian'),('VNMVietnamese','VNM','Vietnamese'),('ESTEstonian','EST','Estonian'),('USAEnglish','USA','English'),('VIREnglish','VIR','English'),('UMIEnglish','UMI','English'),('ZWEShona','ZWE','Shona'),('PSEArabic','PSE','Arabic'),('AFGDari','AFG','Dari'),('NLDFries','NLD','Fries'),('ANTEnglish','ANT','English'),('ALBGreek','ALB','Greek'),('DZABerberi','DZA','Berberi'),('ASMEnglish','ASM','English'),('ANDCatalan','AND...

[Philip Stoev (pstoev-askmonty)]

Also observed without a function in the ORDER BY clause.

[Philip Stoev (pstoev-askmonty)]

Bug http://bugs.mysql.com/bug.php?id=10244 is from 2005 with an identical backtrace.