Bug #43354 Use key hint can crash server in explain extended query
Submitted: 4 Mar 2009 8:55 Modified: 18 Mar 2009 15:53
Reporter: Zigmund Bulinsh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.31, 5.0, 5.1, 6.0 bzr OS:Any (Linux, Windows Vista SP1)
Assigned to: Georgi Kodinov
Tags: use key hint crashes the server.
Triage: Triaged: D1 (Critical)

[4 Mar 2009 8:55] Zigmund Bulinsh
Description:
explain extended query crashes server if there is no specified key in the table when using "use key" hint.

Provided query do not work, because server tels "Key 'id' doesn't exist in table 'p'".

But if we run thgis query with "explain extended" before, than MySQL crashes.

How to repeat:
Apply dump provided file to any database, then run this query:

explain extended
select
  CONCAT(SUP.name, IFNULL(CONCAT(' ', SUP.surname), '')) supplier,
  SUP.disabled supplier_disabled,
  SUP.sex,
  SUP.need_vat,
  SUP.agreement,
  SUP.country_id,
  SUP.language_id,
  SUP.time_zone_id,
  SUP.description as supplier_description,
  SKI.*,
  IFNULL((select (1 - SUM(points)/SUM(quantity)) * 100 from supplier_quality where supplier_id = SUP.id), 100) quality_index,
  CONVERT(not EXISTS(select * from supplier_availability where supplier_id = SUP.id and
	IF(
    ISNULL(start_date),
    NOW() < due_date,
    IF(
      ISNULL(due_date),
      NOW() > start_date,
      NOW() between start_date and due_date))
  ), UNSIGNED) available,
  COUNT(S.id) sub_task_count
from
  suppliers SUP
    join supplier_skills SKI
    on SUP.id = SKI.supplier_id
    left join (
      sub_tasks S,
      tasks T,
      projects P use key(id))
    on (
      SUP.id = S.supplier_id and
      S.task_id = T.id and
      T.project_id = P.id and
      S.finished <> 0 and
      S.deleted = 0 and
      T.deleted = 0 and
      P.deleted = 0)
group by
  SUP.id, SKI.id
[4 Mar 2009 9:35] Sveta Smirnova
Thank you for the report.

Verified as described.

Backtrace from version 5.1:

Thread 1 (process 28535):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x0857a456 in my_write_core (sig=11) at stacktrace.c:310
#3  0x0823e2dc in handle_segfault (sig=11) at mysqld.cc:2505
#4  <signal handler called>
#5  0x081969c4 in Item_sum::print (this=0x8f7fed8, str=0xb745f464, query_type=QT_ORDINARY) at item_sum.cc:448
#6  0x0817ddeb in Item::print_item_w_name (this=0x8f7fed8, str=0xb745f464, query_type=QT_ORDINARY) at item.cc:451
#7  0x082d7e1f in st_select_lex::print (this=0x8f200ec, thd=0x8f1ed58, str=0xb745f464, query_type=QT_ORDINARY) at sql_select.cc:16583
#8  0x0816a5dc in st_select_lex_unit::print (this=0x8f1fe7c, str=0xb745f464, query_type=QT_ORDINARY) at sql_lex.cc:1952
#9  0x0824c2ed in execute_sqlcom_select (thd=0x8f1ed58, all_tables=0x8f7ffe0) at sql_parse.cc:4894
#10 0x08251d4f in mysql_execute_command (thd=0x8f1ed58) at sql_parse.cc:2204
#11 0x08259f29 in mysql_parse (thd=0x8f1ed58, 
    inBuf=0x8f7fcc8 "explain extended\nselect\nCOUNT(S.id) sub_task_count\nfrom\nsuppliers SUP\nleft join (\nsub_tasks S,\nprojects P use key(id)\n)\non (SUP.id = S.supplier_id)", length=147, 
    found_semicolon=0xb74602fc) at sql_parse.cc:5810
#12 0x0825aa2f in dispatch_command (command=COM_QUERY, thd=0x8f1ed58, packet=0x8f6cda9 "", packet_length=147) at sql_parse.cc:1216
#13 0x0825baee in do_command (thd=0x8f1ed58) at sql_parse.cc:857
#14 0x0824a713 in handle_one_connection (arg=0x8f1ed58) at sql_connect.cc:1115
#15 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#16 0x003b74fe in clone () from /lib/libc.so.6
[4 Mar 2009 9:35] Sveta Smirnova
Simplified test case:

DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `sub_tasks`;
CREATE TABLE `sub_tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

explain extended
select
  COUNT(S.id) sub_task_count
from
  suppliers SUP
    left join (
      sub_tasks S,
      projects P use key(id)
	  )
on (SUP.id = S.supplier_id);
[10 Mar 2009 14:08] 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/commits/68773

2772 Georgi Kodinov	2009-03-10
      Bug #43354: Use key hint can crash server in explain extended query
      
      The copy of the original arguments of a aggregate function was not
      initialized until after fix_fields().
      Sometimes (e.g. when there's an error processing the statement)
      the print() can be called with no corresponding fix_fields() call.
      
      Fixed by adding a check if the Item is fixed before using the arguments
      copy.
      
      Introduced by the fix for bug #34773.
     @ mysql-test/r/explain.result
        Bug #43354: test case
     @ mysql-test/t/explain.test
        Bug #43354: test case
     @ sql/item_sum.cc
        Bug #43354: use the argument list copy only if it's initialized
[11 Mar 2009 12:11] 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/commits/68865

2772 Georgi Kodinov	2009-03-11
      Bug #43354: Use key hint can crash server in explain extended query
            
      The copy of the original arguments of a aggregate function was not
      initialized until after fix_fields().
      Sometimes (e.g. when there's an error processing the statement)
      the print() can be called with no corresponding fix_fields() call.
            
      Fixed by adding a check if the Item is fixed before using the arguments
      copy.
     @ mysql-test/r/explain.result
        Bug #43354: test case
     @ mysql-test/t/explain.test
        Bug #43354: test case
     @ sql/item_sum.cc
        Bug #43354: use the argument list copy only if it's initialized
[11 Mar 2009 12:14] 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/commits/68867

2773 Georgi Kodinov	2009-03-11
      Bug #43354: Use key hint can crash server in explain extended query
            
      The copy of the original arguments of a aggregate function was not
      initialized until after fix_fields().
      Sometimes (e.g. when there's an error processing the statement)
      the print() can be called with no corresponding fix_fields() call.
            
      Fixed by adding a check if the Item is fixed before using the arguments
      copy.
     @ mysql-test/r/explain.result
        Bug #43354: test case
     @ mysql-test/t/explain.test
        Bug #43354: test case
     @ sql/item_sum.cc
        Bug #43354: use the argument list copy only if it's initialized
[13 Mar 2009 19:04] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:joro@sun.com-20090311122959-b3yc3klo4zg2jrqz) (merge vers: 5.1.33) (pib:6)
[13 Mar 2009 19:10] Bugs System
Pushed into 5.0.80 (revid:chad@mysql.com-20090312173213-viivy35mb79n1zlg) (version source revid:chad@mysql.com-20090312173213-viivy35mb79n1zlg) (merge vers: 5.0.80) (pib:6)
[14 Mar 2009 23:47] Paul Dubois
Noted in 5.0.80, 5.1.33 changelogs.

Use of USE INDEX hints could cause EXPLAIN EXTENDED to crash.

Setting report to NDI pending push into 6.0.x.
[18 Mar 2009 13:17] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:joro@sun.com-20090311125204-hs1k76o45eq7zjoy) (merge vers: 6.0.11-alpha) (pib:6)
[18 Mar 2009 15:53] Paul Dubois
Noted in 6.0.11 changelog.
[9 May 2009 16:41] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:39] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:36] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)