Bug #1172 Some types of join queries with "force index" option cause server crash
Submitted: 1 Sep 2003 4:38 Modified: 3 Sep 2003 13:03
Reporter: Sergey Petrunia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0-alpha OS:Linux (Linux, Win2k)
Assigned to: Sergei Golubchik CPU Architecture:Any

[1 Sep 2003 4:38] Sergey Petrunia
Description:
Join queries with "force index" option that use "range checked for each row" cause server to crash. There are no crashes without "force index" option. 
A test case that always causes the crash with official binaries is supplied below.

How to repeat:
CREATE TABLE t0 (
  key1 int(11) NOT NULL default '0',
  KEY i1 (key1)
);
INSERT INTO t0 VALUES (0),(0),(1),(1);

CREATE TABLE t1 (
  keya int(11) NOT NULL default '0',
  KEY i1 (keya)
);
INSERT INTO t1 VALUES (0),(0),(1),(1),(2),(2);

--this causes the crash:
explain select * from t0 force index(i1), t1 force index(i1) where 
 (t0.key1 <t1.keya + 1) and t1.keya=3;

Suggested fix:
The following is a speculation on why this happens:

The problem is in SQL_SELECT::test_quick_select function. With "force index" option full table read time is set to DBL_MAX at the beginning.
  Then if get_mm_tree returns SEL_TREE of type KEY with one SEL_ARG of type SEL_ARG::MAYBE_KEY, it would rely on being not choosen by returning high read time about HA_POS_ERROR*(1+1/TIME_FOR_COMPARE). 
  This value is still less than DBL_MAX, so the SEL_TREE in question gets choosen and passed to get_quick_select function which seems to assume that it is not passed SEL_ARGs of type SEL_ARG::MAYBE_KEY and will crash on it.
[1 Sep 2003 5:25] MySQL Verification Team
Thanks you for the bug report. I was able to repeat on Win2k
with server built with latest bk 4.1 tree.

Below call stack:

mysqld.exe!get_quick_keys(st_qsel_param * param=0x1cb2e2b0, QUICK_SELECT * quick=0x032a6e50, st_key_part * key=0x03285010, SEL_ARG * key_tree=0x00000000, char * min_key=0x1cb2e3e8, unsigned int min_key_flag=0, char * max_key=0x1cb2eae6, unsigned int max_key_flag=0)  Line 2244 + 0x3	C++

mysqld.exe!get_quick_keys(st_qsel_param * param=0x1cb2e2b0, QUICK_SELECT * quick=0x032a6e50, st_key_part * key=0x03285010, SEL_ARG * key_tree=0x032850a8, char * min_key=0x1cb2e3e8, unsigned int min_key_flag=0, char * max_key=0x1cb2eae6, unsigned int max_key_flag=0)  Line 2247 + 0x28	C++

mysqld.exe!get_quick_select(st_qsel_param * param=0x1cb2e2b0, unsigned int idx=0, SEL_ARG * key_tree=0x032850a8)  Line 2215 + 0x3c	C++

mysqld.exe!SQL_SELECT::test_quick_select(unsigned long keys_to_use=1, unsigned __int64 prev_tables=2, unsigned long limit=4294967295, int force_quick_range=0)  Line 727 + 0x27	C++
mysqld.exe!make_join_select(JOIN * join=0x03296410, SQL_SELECT * select=0x03297710, Item * cond=0x03296090)  Line 3329 + 0x62	C++

mysqld.exe!JOIN::optimize()  Line 629 + 0x1d	C++

mysqld.exe!mysql_select(THD * thd=0x0120ca28, Item * * * rref_pointer_array=0x0120ce7c, st_table_list * tables=0x03295b88, unsigned int wild_num=1, List<Item> & fields={...}, Item * conds=0x03296090, unsigned int og_num=0, st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned long select_options=84448260, select_result * result=0x03296128, st_select_lex_unit * unit=0x0120ccac, st_select_lex * select_lex=0x0120cd94, int tables_and_fields_initied=0)  Line 1527 + 0x8	C++

mysqld.exe!mysql_explain_select(THD * thd=0x0120ca28, st_select_lex * select_lex=0x0120cd94, const char * type=0x0077fd44, select_result * result=0x03296128)  Line 8797 + 0x84	C++

mysqld.exe!mysql_explain_union(THD * thd=0x0120ca28, st_select_lex_unit * unit=0x0120ccac, select_result * result=0x03296128)  Line 8767 + 0xe1	C++

mysqld.exe!mysql_execute_command(THD * thd=0x0120ca28)  Line 1798 + 0x17	C++

mysqld.exe!mysql_parse(THD * thd=0x0120ca28, char * inBuf=0x03295a58, unsigned int length=105)  Line 3723 + 0x9	C++

mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x0120ca28, char * packet=0x032919f1, unsigned int packet_length=106)  Line 1335 + 0x1d	C++

mysqld.exe!do_command(THD * thd=0x0120ca28)  Line 1126 + 0x37	C++

mysqld.exe!handle_one_connection(void * arg=0x0120ca28)  Line 905 + 0x9	C++

mysqld.exe!pthread_start(void * param=0x03284e90)  Line 63 + 0x7	C

mysqld.exe!_threadstart(void * ptd=0x0328eb98)  Line 173 + 0xd	C

KERNEL32.DLL!7969987c()
[3 Sep 2003 13:03] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

yes, Sergey - you were right.

Fixed in 4.0.16 and 4.1.1