Bug #11869 Server crashes making a union join query with fulltext search
Submitted: 11 Jul 2005 19:18 Modified: 8 Aug 2005 15:49
Reporter: Markus Popp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:4.1.12-nt, 5.0.7-nt-beta/5.0.10 OS:Microsoft Windows (Windows XP1/Linux)
Assigned to: Sergey Petrunya CPU Architecture:Any

[11 Jul 2005 19:18] Markus Popp
Description:
I've got the following 3 tables:

CREATE TABLE `forum_beitraege` (
  `id` int(11) NOT NULL auto_increment,
  `thread` int(11) NOT NULL default '0',
  `nick` varchar(20) NOT NULL default '',
  `datum` datetime NOT NULL default '0000-00-00 00:00:00',
  `beitrag` longtext NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `thread` (`thread`),
  FULLTEXT KEY `beitrag` (`beitrag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;

CREATE TABLE `forum_foren` (
  `id` int(11) NOT NULL auto_increment,
  `text` varchar(100) NOT NULL default '',
  `beschreibung` longtext NOT NULL,
  `gruppe` int(11) NOT NULL default '0',
  `indexnr` int(11) NOT NULL default '0',
  `anzeige` char(1) NOT NULL default 'y',
  PRIMARY KEY  (`id`),
  KEY `text` (`text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;

CREATE TABLE `forum_threads` (
  `id` int(11) NOT NULL auto_increment,
  `forum` int(11) NOT NULL default '0',
  `betreff` varchar(70) NOT NULL default '',
  `nick` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `forum` (`forum`),
  FULLTEXT KEY `betreff` (`betreff`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;

... and ran this query, which caused the server (tested with 4.1.12 and 5.0.7) to crash:

select a.text, b.id, b.betreff
from forum_foren a inner join forum_threads b on a.id = b.forum inner join
forum_beitraege c on b.id = c.thread
where match(b.betreff) against ('+abc' IN BOOLEAN MODE)
group by a.text, b.id, b.betreff
union
select a.text, b.id, b.betreff
from forum_foren a inner join forum_threads b on a.id = b.forum inner join
forum_beitraege c on b.id = c.thread
where match(c.beitrag) against ('+abc' IN BOOLEAN MODE)
group by a.text, b.id, b.betreff
order by match(b.betreff) against ('+abc' IN BOOLEAN MODE) desc

How to repeat:
Creating the 3 tables and running the query below should repeat this error.
[11 Jul 2005 19:25] Markus Popp
The crash also happens, if you omit the both 'group by' clauses (which are in fact unnecessary).
[11 Jul 2005 20:33] Miguel Solorzano
Thank you for the bug report I was able to repeat also on Linux.

Call stack on Windows:

item_func.cc
--4375--

for (keynr=0 ; keynr < table->s->keys ; keynr++)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  {
    if ((table->key_info[keynr].flags & HA_FULLTEXT) &&
        (table->keys_in_use_for_query.is_set(keynr)))
    {
      ft_to_key[fts]=keynr;
      ft_cnt[fts]=0;
      fts++;
    }

>mysqld-debug.exe!Item_func_match::fix_index()  Line 4375 + 0x1e	C++
 mysqld-debug.exe!setup_ftfuncs(st_select_lex * select_lex=0x030cccd0)  Line 4090 + 0x8	C++
 mysqld-debug.exe!JOIN::prepare(Item * * * rref_pointer_array=0x030ccdf4, st_table_list * tables_init=0x030cd258, unsigned int wild_num=0, Item * conds_init=0x030c7fb0, unsigned int og_num=3, st_order * order_init=0x00000000, st_order * group_init=0x030c8170, Item * having_init=0x00000000, st_order * proc_param_init=0x00000000, st_select_lex * select_lex_arg=0x030cccd0, st_select_lex_unit * unit_arg=0x030ae6a0)  Line 387 + 0xf	C++
 mysqld-debug.exe!st_select_lex_unit::prepare(THD * thd_arg=0x030ae640, select_result * sel_result=0x030c8670, unsigned long additional_options=268435456, const char * tmp_table_alias=0x00a131d9)  Line 231 + 0xcc	C++
 mysqld-debug.exe!mysql_union(THD * thd=0x030ae640, st_lex * lex=0x030ae688, select_result * result=0x030c8670, st_select_lex_unit * unit=0x030ae6a0, unsigned long setup_tables_done_option=0)  Line 33 + 0x1f	C++
 mysqld-debug.exe!handle_select(THD * thd=0x030ae640, st_lex * lex=0x030ae688, select_result * result=0x030c8670, unsigned long setup_tables_done_option=0)  Line 228 + 0x1c	C++
 mysqld-debug.exe!mysql_execute_command(THD * thd=0x030ae640)  Line 2425 + 0x13	C++
 mysqld-debug.exe!mysql_parse(THD * thd=0x030ae640, char * inBuf=0x030cb630, unsigned int length=533)  Line 5382 + 0x9	C++
 mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x030ae640, char * packet=0x030c3571, unsigned int packet_length=534)  Line 1674 + 0x1d	C++
 mysqld-debug.exe!do_command(THD * thd=0x030ae640)  Line 1477 + 0x31	C++
 mysqld-debug.exe!handle_one_connection(void * arg=0x030ae640)  Line 1126 + 0x9	C++
 mysqld-debug.exe!pthread_start(void * param=0x030b2df0)  Line 63 + 0x7	C
 mysqld-debug.exe!_threadstart(void * ptd=0x030afc60)  Line 173 + 0xd	C
 kernel32.dll!7c80b50b() 	
 kernel32.dll!7c8399f3()
[11 Jul 2005 22:42] Miguel Solorzano
Backtrace on Linux:

[New Thread 1132243888 (LWP 9829)]
210711 19:38:19 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.10-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132444592 (LWP 9833)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132444592 (LWP 9833)]
0x0816cae1 in Item_func_match::fix_index (this=0x8e2e9c0) at item_func.cc:4386
4386      for (keynr=0 ; keynr < table->s->keys ; keynr++)
(gdb) backtrace full
#0  0x0816cae1 in Item_func_match::fix_index (this=0x8e2e9c0) at item_func.cc:4386
        item = (class Item_field *) 0x8e2e3e0
        ft_to_key = {2, 149107072, 1, 3, 2, 0, 149088176, 149088184, 0, 1, 7, 149106878, 148892120, 149107184, 149107072, 16786680, 8661838, 
  149107072, 1132439704, 136525807, 148853072, 1, 1132439704, 136525905, 149107072, 149107072, 0, 148890520, 148890512, 0, 0, 18106392, 2, 
  149107072, 148890716, 0, 149088192, 22629496, 1132439800, 136526516, 148853072, 149093256, 148890776, 149088376, 148889572, 149127420, 1, 
  139774678, 140932853, 1, 0, 0, 140224168, 1132439816, 1132439800, 135535616, 1132439856, 149107072, 2, 3, 2, 0, 1132439832, 136385803}
        ft_cnt = {1, 149086528, 0, 149086392, 149085840, 148890512, 148890520, 148890520, 7, 148890716, 0, 149107072, 142079968, 149085840, 
  148890512, 0, 875, 0, 1132441736, 136241849, 1132441680, 148853072, 0, 0, 16777216, 149107072, 1132439960, 149107072, 148886016, 1, 
  1165994008, 1076197937, 4294967295, 142081664, 1132439496, 136088527, 141966592, 1132439488, 1132439480, 135661876, 149087560, 149087256, 
  0, 140007556, 0, 1, 1132439528, 135662109, 149087440, 148889440, 148889440, 149088184, 149088176, 0, 1132439544, 135546904, 149088248, 
  141966592, 2, 135563929, 148853072, 149124048, 1132439640, 135565619}
        fts = 0
        keynr = 0
        max_cnt = 0
        mkeys = 0
        i = 2
#1  0x0821121a in setup_ftfuncs (select_lex=0x8dfdf60) at sql_base.cc:4083
        li = {<base_list_iterator> = {list = 0x8dfe020, el = 0x8e2ea78, prev = 0x8e2e588, current = 0x8e2ea78}, <No data fields>}
        lj = {<base_list_iterator> = {list = 0x8dfe020, el = 0x8e2e588, prev = 0x8dfe020, current = 0x8e2e588}, <No data fields>}
        ftf = (class Item_func_match *) 0x8e2e9c0
        ftf2 = (class Item_func_match *) 0x8e2e4d0
#2  0x082179dd in JOIN::prepare (this=0x8e373d0, rref_pointer_array=0x8dfe07c, tables_init=0x8dfe498, wild_num=0, conds_init=0x8e2e4d0, 
    og_num=3, order_init=0x0, group_init=0x8e2e688, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8dfdf60, unit_arg=0x8df51a0)
    at sql_select.cc:387
        _db_func_ = 0x90844a00 ""
        _db_file_ = 0x8dfdfe4 "¸âß\bXäß\b\003"
        _db_level_ = 148853072
        _db_framep_ = (char **) 0x8e373d0
#3  0x083076ee in st_select_lex_unit::prepare (this=0x8df51a0, thd_arg=0x8df5150, sel_result=0x8e2eb40, additional_options=268435456, 
    tmp_table_alias=0x86198b9 "") at sql_union.cc:220
        can_skip_order_by = true
        join = (JOIN *) 0x8e373d0
        lex_select_save = (SELECT_LEX *) 0x8dfdf60
        sl = (SELECT_LEX *) 0x8dfdf60
        first_select = (SELECT_LEX *) 0x8df537c
        tmp_result = (class select_result *) 0x8e2eb50
        is_union = true
        empty_table = (TABLE *) 0x8e36d78
        _db_func_ = 0xe2dce8 <Address 0xe2dce8 out of bounds>
---Type <return> to continue, or q <return> to quit---
        _db_file_ = 0x8df6118 "\016\201ë`"
        _db_level_ = 136082431
        _db_framep_ = (char **) 0x4026ee02
#4  0x08306e45 in mysql_union (thd=0x8df5150, lex=0x8df5190, result=0x8e2eb40, unit=0x8df51a0, setup_tables_done_option=0) at sql_union.cc:32
        _db_func_ = 0x8e2eb40 "\210¨[\bPQß\b¥¥¥¥¥¥¥¥è\232a\bPQß\b Qß\b"
        _db_file_ = 0x40180615 "\201Ãßi"
        _db_level_ = 149089088
        _db_framep_ = (char **) 0x8e2dce8
        res = 8
#5  0x08217380 in handle_select (thd=0x8df5150, lex=0x8df5190, result=0x8e2eb40, setup_tables_done_option=0) at sql_select.cc:228
        res = false
        select_lex = (SELECT_LEX *) 0x8df537c
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 148885160
        _db_framep_ = (char **) 0x0
#6  0x081e282c in mysql_execute_command (thd=0x8df5150) at sql_parse.cc:2424
        result = (class select_result *) 0x8e2eb40
        res = false
        result = 0
        lex = (LEX *) 0x8df5190
        select_lex = (SELECT_LEX *) 0x8df537c
        slave_fake_lock = false
        fake_prev_lock = (MYSQL_LOCK *) 0x0
        first_table = (TABLE_LIST *) 0x8dfcea8
        all_tables = (TABLE_LIST *) 0x8dfcea8
        unit = (SELECT_LEX_UNIT *) 0x8df51a0
        _db_func_ = 0x0
        _db_file_ = 0x0
        _db_level_ = 0
        _db_framep_ = (char **) 0x437fb04c
#7  0x081ea096 in mysql_parse (thd=0x8df5150, 
    inBuf=0x8dfc9b8 "select a.text, b.id, b.betreff\nfrom forum_foren a inner join forum_threads b on a.id = b.forum inner join\nforum_beitraege c on b.id = c.thread\nwhere match(b.betreff) against ('+abc' IN BOOLEAN MODE)\ng"..., length=533) at sql_parse.cc:5381
        lex = (LEX *) 0x8df5190
        _db_func_ = 0x878c000 "\210\021`\b"
        _db_file_ = 0x81e0bda "\203Ä \203=\024Äy\b"
        _db_level_ = 1132442408
        _db_framep_ = (char **) 0x437fbbb0
---Type <return> to continue, or q <return> to quit---
#8  0x081e0c3a in dispatch_command (command=COM_QUERY, thd=0x8df5150, 
    packet=0x8e18789 "select a.text, b.id, b.betreff\nfrom forum_foren a inner join forum_threads b on a.id = b.forum inner join\nforum_beitraege c on b.id = c.thread\nwhere match(b.betreff) against ('+abc' IN BOOLEAN MODE)\ng"..., packet_length=534) at sql_parse.cc:1674
        packet_end = 0x8dfcbcd ""
        net = (NET *) 0x8df5898
        error = false
        _db_func_ = 0x6076f8 <Address 0x6076f8 out of bounds>
        _db_file_ = 0x0
        _db_level_ = 136924480
        _db_framep_ = (char **) 0x437fb348
#9  0x081e0521 in do_command (thd=0x8df5150) at sql_parse.cc:1477
        packet = 0x8e18788 "\003select a.text, b.id, b.betreff\nfrom forum_foren a inner join forum_threads b on a.id = b.forum inner join\nforum_beitraege c on b.id = c.thread\nwhere match(b.betreff) against ('+abc' IN BOOLEAN MODE)\n"...
        old_timeout = 30
        packet_length = 534
        net = (NET *) 0x8df5898
        command = COM_QUERY
        _db_func_ = 0x8df6194 "ÿÿÿÿ"
        _db_file_ = 0x81ba1e7 "\203Ä\020ÉÃU\211å\203ì\b\203ì\fÿu\bè\217"
        _db_level_ = 1132442472
        _db_framep_ = (char **) 0x1010
#10 0x081df72f in handle_one_connection (arg=0x8df5150) at sql_parse.cc:1126
        error = 0
        net = (NET *) 0x8df5898
        thd = (class THD *) 0x8df5150
        launch_time = 0
        set = {__val = {0 <repeats 32 times>}}
#11 0x4017daa7 in start_thread () from /lib/tls/libpthread.so.0
No symbol table info available.
#12 0x402aec2e in clone () from /lib/tls/libc.so.6
No symbol table info available.
(gdb)
[13 Jul 2005 15:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27012
[14 Jul 2005 13:08] Sergey Petrunya
Fix pushed into 4.1.13 tree
[14 Jul 2005 18:55] Sergey Petrunya
The crash has been eliminated, but now one can get "table doesn't support FULLTEXT" errors for queries with UNION + ORDER BY.
This will be fixed (discussed with Sergei), I'm working on it.
[6 Aug 2005 1:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27953
[6 Aug 2005 17:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27959
[7 Aug 2005 16:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27972
[7 Aug 2005 17:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27973
[7 Aug 2005 18:09] Sergey Petrunya
Pushed into 4.1.14, 5.0.12 trees
[8 Aug 2005 15:49] Mike Hillyer
Documented in 5.0.12 and 4.1.14 changelogs:

<listitem><para><literal>UNION</literal> query with <literal>FULLTEXT</literal> could cause server crash. (Bug #11869)</para></listitem>