Bug #699 SUBSELECTION AND IN-KEYWORD
Submitted: 23 Jun 2003 6:56 Modified: 25 Jun 2003 7:00
Reporter: Johan Timmerman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:4.1.0-alpha-max-nt OS:Microsoft Windows (Win XP)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[23 Jun 2003 6:56] Johan Timmerman
Description:
"Lost connection to MySQL server during query" when executing the following query:

SELECT stc_daytypedata.daytypeid
FROM stc_tttdaytypeperiodtimerelation, stc_daytypedata
WHERE stc_tttdaytypeperiodtimerelation.tttid = 1
  AND stc_tttdaytypeperiodtimerelation.daytypeid =  stc_daytypedata.daytypeid
  AND stc_daytypedata.daytypeid NOT IN
  (SELECT stc_daytypedata.daytypeid
   FROM stc_vridata, stc_locationvrirelation, stc_grouplocationrelation,  
        stc_grouptttrelation, stc_tttdaytypeperiodtimerelation, stc_daytypedata
   WHERE stc_vridata.vriid = 1
     AND stc_vridata.vriid = stc_locationvrirelation.vriid
     AND stc_locationvrirelation.locationid =   
         stc_grouplocationrelation.locationid
     AND stc_grouplocationrelation.groupid = stc_grouptttrelation.groupid
     AND stc_grouptttrelation.tttid = stc_tttdaytypeperiodtimerelation.tttid
     AND stc_tttdaytypeperiodtimerelation.daytypeid = stc_daytypedata.daytypeid
     AND stc_tttdaytypeperiodtimerelation.tttid != 1
   GROUP BY stc_daytypedata.daytypeid)
GROUP BY stc_daytypedata.daytypeid;

How to repeat:
By executing the query again (after re-run winmysqladmin.exe)

Suggested fix:
No suggestion right now
[23 Jun 2003 7:05] Alexander Keremidarski
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

"How-to-repeat" section name should be read as "How to repeat same problem on another (developer's) machine".

Please provide your tables definitions as from SHOW CREATE TABLE or mysqldump output as well as couple of INSERTs enough to reproduce bug
[23 Jun 2003 7:06] Miguel Solorzano
Could you please provide us with a more complete case with
the table structure ? uploading them into a zip file at:

ftp://support.mysql.com/pub/mysql/secret/
[23 Jun 2003 12:04] Miguel Solorzano
Thank you for the bug report I was able to repeat with the
last BK tree 4.1.

sql/sql_select.cc
--1228--
 // Some tables may have been const
      curr_join->tmp_having->update_used_tables();
      JOIN_TAB *table= &curr_join->join_tab[const_tables];
      table_map used_tables= curr_join->const_table_map | table->table->map;
      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Below back trace on Windows:

>	mysqld.exe!JOIN::exec()  Line 1231 + 0xb	C++
 	mysqld.exe!subselect_single_select_engine::exec()  Line 852	C++
 	mysqld.exe!Item_subselect::exec()  Line 132	C++
 	mysqld.exe!Item_in_subselect::val_int()  Line 417 + 0x8	C++
 	mysqld.exe!Item::val_int_result()  Line 100 + 0x17	C++
 	mysqld.exe!Item_in_optimizer::val_int()  Line 482 + 0x17	C++
 	mysqld.exe!Item_int_func::val()  Line 193 + 0x17	C++
 	mysqld.exe!Item_func_not::val_int()  Line 59 + 0x15	C++
 	mysqld.exe!sub_select(JOIN * join=0x0326de58, st_join_table * join_tab=0x03271de0, int end_of_records=0)  Line 5078 + 0x11	C++
 	mysqld.exe!sub_select(JOIN * join=0x0326de58, st_join_table * join_tab=0x03271ca0, int end_of_records=0)  Line 5080 + 0x15	C++
 	mysqld.exe!do_select(JOIN * join=0x0326de58, List<Item> * fields=0x00000000, st_table * table=0x03273968, Procedure * procedure=0x00000000)  Line 4965 + 0xf	C++
 	mysqld.exe!JOIN::exec()  Line 1008 + 0x11	C++
 	mysqld.exe!mysql_select(THD * thd=0x011f5ad8, Item * * * rref_pointer_array=0x011f5ec4, st_table_list * tables=0x032510f0, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x0326d9e0, unsigned int og_num=1, st_order * order=0x00000000, st_order * group=0x0326daf0, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned long select_options=84448256, select_result * result=0x0326de48, st_select_lex_unit * unit=0x011f5d5c, st_select_lex * select_lex=0x011f5e74, int tables_and_fields_initied=0)  Line 1400	C++
 	mysqld.exe!handle_select(THD * thd=0x011f5ad8, st_lex * lex=0x011f5d50, select_result * result=0x0326de48)  Line 184 + 0x93	C++
 	mysqld.exe!mysql_execute_command(THD * thd=0x011f5ad8)  Line 1777 + 0x11	C++
 	mysqld.exe!mysql_parse(THD * thd=0x011f5ad8, char * inBuf=0x03250be0, unsigned int length=987)  Line 3619 + 0x9	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x011f5ad8, char * packet=0x0324cb21, unsigned int packet_length=988)  Line 1294 + 0x1d	C++
 	mysqld.exe!do_command(THD * thd=0x011f5ad8)  Line 1094 + 0x37	C++
 	mysqld.exe!handle_one_connection(void * arg=0x011f5ad8)  Line 876 + 0x9	C++
 	mysqld.exe!pthread_start(void * param=0x0324ca68)  Line 63 + 0x7	C
 	mysqld.exe!_threadstart(void * ptd=0x011f6aa8)  Line 173 + 0xd	C
 	KERNEL32.DLL!77e8b2d8()
[24 Jun 2003 0:33] Lenz Grimmer
Assigned to Subselect team
[25 Jun 2003 7:00] Oleksandr Byelkin
diff -Nrc a/sql/sql_select.cc b/sql/sql_select.cc 
*** a/sql/sql_select.cc Wed Jun 25 16:55:23 2003 
--- b/sql/sql_select.cc Wed Jun 25 16:55:23 2003 
*************** 
*** 1227,1233 **** 
      { 
        // Some tables may have been const 
        curr_join->tmp_having->update_used_tables(); 
!       JOIN_TAB *table= &curr_join->join_tab[const_tables]; 
        table_map used_tables= curr_join->const_table_map | table->table->map; 
 
        Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, 
--- 1227,1233 ---- 
      { 
        // Some tables may have been const 
        curr_join->tmp_having->update_used_tables(); 
!       JOIN_TAB *table= &curr_join->join_tab[curr_join->const_tables]; 
        table_map used_tables= curr_join->const_table_map | table->table->map; 
 
        Item* sort_table_cond= make_cond_for_table(curr_join->tmp_having, 
*************** 
*** 1263,1269 **** 
          We can abort sorting after thd->select_limit rows if we there is no 
          WHERE clause for any tables after the sorted one. 
        */ 
!       JOIN_TAB *table= &curr_join->join_tab[const_tables+1]; 
        JOIN_TAB *end_table= &curr_join->join_tab[tables]; 
        for (; table < end_table ; table++) 
        { 
--- 1263,1269 ---- 
          We can abort sorting after thd->select_limit rows if we there is no 
          WHERE clause for any tables after the sorted one. 
        */ 
!       JOIN_TAB *table= &curr_join->join_tab[curr_join->const_tables+1]; 
        JOIN_TAB *end_table= &curr_join->join_tab[tables]; 
        for (; table < end_table ; table++) 
        {