| Bug #6365 | Server crash with NOT IN('', NULL) in where clause | ||
|---|---|---|---|
| Submitted: | 1 Nov 2004 15:21 | Modified: | 6 Nov 2004 6:38 |
| Reporter: | Sebastien C | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.7/4.1.8 BK source | OS: | Windows (Windows/Linux) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[1 Nov 2004 15:23]
Sebastien C
Added Version and OS to bug header for information.
[1 Nov 2004 15:37]
MySQL Verification Team
Could you please provide dump of tables for to test the offended query ? You can upload it at: ftp://ftp.mysql.com/pub/mysql/upload Thanks in advance
[1 Nov 2004 16:24]
Sebastien C
Thank you for your prompt answer. I cannot send you the original data as it is large (12 GB) and confidential. The good news is that I have been able to reproduce the crash with the following 3 SQL lines:
mysql> create temporary table test ( `JOBNAME` char(64) NOT NULL default '', `STARTRUN` datetime default NULL );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ( 'j1', "2004-09-10 12:23:34" );
Query OK, 1 row affected (0.00 sec)
mysql> select jobname,startrun from test where 1;
+---------+---------------------+
| jobname | startrun |
+---------+---------------------+
| j1 | 2004-09-10 12:23:34 |
+---------+---------------------+
1 row in set (0.00 sec)
And now the crash:
mysql> select jobname,startrun from test where startrun NOT IN('', NULL) ORDER BY startrun;
ERROR 2013: Lost connection to MySQL server during query
Thank you!
[1 Nov 2004 16:43]
MySQL Verification Team
Thank you the test case I was able to repeat. I will test on Linux with latest
Bk source.
Call Stack:
> mysqld.exe!String::charset() Line 81 + 0xa C++
mysqld.exe!Item_func_in::fix_length_and_dec() Line 1808 + 0x12 C++
mysqld.exe!Item_func::fix_fields(THD * thd=0x00e6a078, st_table_list * tables=0x00e5f7d0, Item * * ref=0x00e5f9b4) Line 240 C++
mysqld.exe!Item_func::fix_fields(THD * thd=0x00e6a078, st_table_list * tables=0x00e5f7d0, Item * * ref=0x00e60504) Line 227 + 0x2a C++
mysqld.exe!setup_conds(THD * thd=0x00e6a078, st_table_list * tables=0x00e5f7d0, Item * * conds=0x00e60504) Line 2596 + 0x2a C++
mysqld.exe!setup_without_group(THD * thd=0x00e6a078, Item * * ref_pointer_array=0x00e60540, st_table_list * tables=0x00e5f7d0, List<Item> & fields={...}, List<Item> & all_fields={...}, Item * * conds=0x00e60504, st_order * order=0x00e5faf8, st_order * group=0x00000000, int * hidden_group_fields=0x00e6047c) Line 229 + 0x11 C++
mysqld.exe!JOIN::prepare(Item * * * rref_pointer_array=0x00e6a2ac, st_table_list * tables_init=0x00e5f7d0, unsigned int wild_num=0, Item * conds_init=0x00e5f970, unsigned int og_num=1, st_order * order_init=0x00e5faf8, st_order * group_init=0x00000000, Item * having_init=0x00000000, st_order * proc_param_init=0x00000000, st_select_lex * select_lex_arg=0x00e6a1c0, st_select_lex_unit * unit_arg=0x00e6a0c4) Line 278 + 0x112 C++
mysqld.exe!mysql_select(THD * thd=0x00e6a078, Item * * * rref_pointer_array=0x00e6a2ac, st_table_list * tables=0x00e5f7d0, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x00e5f970, unsigned int og_num=1, st_order * order=0x00e5faf8, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned long select_options=8669696, select_result * result=0x00e5fb30, st_select_lex_unit * unit=0x00e6a0c4, st_select_lex * select_lex=0x00e6a1c0) Line 1564 + 0x34 C++
mysqld.exe!handle_select(THD * thd=0x00e6a078, st_lex * lex=0x00e6a0b8, select_result * result=0x00e5fb30) Line 193 + 0x87 C++
mysqld.exe!mysql_execute_command(THD * thd=0x00e6a078) Line 2003 + 0x11 C++
mysqld.exe!mysql_parse(THD * thd=0x00e6a078, char * inBuf=0x00e5f630, unsigned int length=83) Line 4050 + 0x9 C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00e6a078, char * packet=0x02ebd439, unsigned int packet_length=84) Line 1463 + 0x1d C++
mysqld.exe!do_command(THD * thd=0x00e6a078) Line 1278 + 0x31 C++
mysqld.exe!handle_one_connection(void * arg=0x00e6a078) Line 1022 + 0x9 C++
mysqld.exe!pthread_start(void * param=0x02eb5ff0) Line 63 + 0x7 C
mysqld.exe!_threadstart(void * ptd=0x02ebd878) Line 173 + 0xd C
kernel32.dll!7c80b50b()
kernel32.dll!7c8399f3()
[1 Nov 2004 18:45]
MySQL Verification Team
Tested also with latest BK 4.1 source tree on Linux Slackware:
miguel@hegel:~/dbs/4.1$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create temporary table test ( `JOBNAME` char(64) NOT NULL default '',
-> `STARTRUN` datetime default NULL );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test values ( 'j1', "2004-09-10 12:23:34" );
Query OK, 1 row affected (0.01 sec)
mysql> select jobname,startrun from test where 1;
+---------+---------------------+
| jobname | startrun |
+---------+---------------------+
| j1 | 2004-09-10 12:23:34 |
+---------+---------------------+
1 row in set (0.00 sec)
mysql> select jobname,startrun from test where startrun NOT IN('', NULL) ORDER
-> BY startrun;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[2 Nov 2004 13:42]
Marko Mäkelä
This bug is also occurs with MyISAM tables:
create temporary table test(jobname char(64) not null default '', startrun datetime default null) engine=myisam;
select jobname,startrun from test where startrun not in('',null) order by startrun;
[3 Nov 2004 18:47]
Sebastien C
Edited bug header again to make more explicit and useful in searches
[6 Nov 2004 6:38]
Igor Babaev
Actually we had a crash for any IN predicate of the form 'f IN (...,NULL,...)'
if column f had been declared to be of the character set different from the
default character set (e.g. f was declared to be of 'binary' character set,
while the default character set was latin1).
I built a simpler case than that was reported and added it to the tests in
fun_in.test.
ChangeSet
1.2090 04/11/04 20:39:52 igor@rurik.mysql.com +3 -0
func_in.result, func_in.test:
Added a case for bug #6365.
item_cmpfunc.cc:
Fixed bug #6365 : Server crashed when list of values
in IN predicate contains NULL while the tested field is
of the character type and not of the default set;
e.g. when f in 'f IN (NULL,'aa') belongs to binary
character set, while the default character set is latin1.
Description: The following query crashes the MySQL for Windows systematically on 4.1.6 and 4.1.7 (note that the EXPLAIN command will also choke on this query): SELECT t2.applgroup,t2.jobname,t2.memname,t1.startrun,t1.endrun,t1.elapsed,t1.oscompstat FROM ctm_cmr_jobinf t1, ctm_cmr_ajf t2, oa_synonyms t3 WHERE t1.applic = t3.app_synonym AND t1.ajf_date = '20041101' AND t2.odate = '20041031' AND t1.datacenter = t2.datacenter AND t1.orderno = t2.orderno AND t1.ajf_date = t2.ajf_date AND t3.oa_app='RMS' AND t2.CYCLIC != 'Y' AND t1.startrun NOT IN('', NULL) AND SUBSTRING(t1.datacenter,4,1) = 'P' ORDER BY t1.startrun, t1.endrun; When the clause 't1.startrun NOT IN('', NULL)' if removed, everything works fine. This used to work fine until recently (4.1.3?). Many thanks for your time. How to repeat: Just execute this query (with or without EXPLAIN): SELECT t2.applgroup,t2.jobname,t2.memname,t1.startrun,t1.endrun,t1.elapsed,t1.oscompstat FROM ctm_cmr_jobinf t1, ctm_cmr_ajf t2, oa_synonyms t3 WHERE t1.applic = t3.app_synonym AND t1.ajf_date = '20041101' AND t2.odate = '20041031' AND t1.datacenter = t2.datacenter AND t1.orderno = t2.orderno AND t1.ajf_date = t2.ajf_date AND t3.oa_app='RMS' AND t2.CYCLIC != 'Y' AND t1.startrun NOT IN('', NULL) AND SUBSTRING(t1.datacenter,4,1) = 'P' ORDER BY t1.startrun, t1.endrun;