Bug #37851 Crash in test_if_skip_sort_order tab->select is zero
Submitted: 3 Jul 2008 20:20 Modified: 20 Nov 2010 23:08
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0 OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: ICP, index condition pushdown, regression
Triage: Triaged: D1 (Critical)

[3 Jul 2008 20:20] Philip Stoev
Description:
When executing an EXPLAIN EXTENDED on a query containing aggregates and a subquery, mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085b0d94 in my_write_core (sig=11) at stacktrace.c:307
#3  0x08213e88 in handle_segfault (sig=11) at mysqld.cc:2638
#4  <signal handler called>
#5  0x082841ac in test_if_skip_sort_order (tab=0xa5d0488, order=0xa5d6110, select_limit=4294967295, no_changes=false, map=0xa601908) at sql_select.cc:15875
#6  0x0828be53 in JOIN::exec (this=0xa5ceb10) at sql_select.cc:2350
#7  0x0828e1ea in mysql_select (thd=0xadb1be78, rref_pointer_array=0xa5d56b4, tables=0xa5d5ac8, wild_num=0, fields=@0xa5d5644, conds=0xa5d5fd0, og_num=1,
    order=0x0, group=0xa5d6110, having=0xa5d6230, proc_param=0x0, select_options=2147764740, result=0xa5d6868, unit=0xa5d5748, select_lex=0xa5d55b0)
    at sql_select.cc:3001
#8  0x0828e518 in mysql_explain_union (thd=0xadb1be78, unit=0xa5d5748, result=0xa5d6868) at sql_select.cc:19356
#9  0x08290e13 in select_describe (join=0xa655f78, need_tmp_table=false, need_order=false, distinct=false,
    message=0x8682530 "Impossible WHERE noticed after reading const tables") at sql_select.cc:19297
#10 0x0828bc15 in JOIN::exec (this=0xa655f78) at sql_select.cc:8950
#11 0x0828e1ea in mysql_select (thd=0xadb1be78, rref_pointer_array=0xadb1d35c, tables=0xa5d5170, wild_num=0, fields=@0xadb1d2ec, conds=0xa5d6750, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xa5d6868, unit=0xadb1cfc8, select_lex=0xadb1d258)
    at sql_select.cc:3001
#12 0x0828e518 in mysql_explain_union (thd=0xadb1be78, unit=0xadb1cfc8, result=0xa5d6868) at sql_select.cc:19356
#13 0x0822001e in execute_sqlcom_select (thd=0xadb1be78, all_tables=0xa5d5170) at sql_parse.cc:4825
#14 0x08220f51 in mysql_execute_command (thd=0xadb1be78) at sql_parse.cc:2015
#15 0x0822a01e in mysql_parse (thd=0xadb1be78,
    inBuf=0xa5d4b08 "EXPLAIN EXTENDED SELECT MIN( OUTR . `int_key` ) AS X FROM B AS OUTR WHERE EXISTS ( SELECT INNR . `pk` FROM CC AS INNR WHERE INNR . `int_key` IS NULL GROUP BY INNR . `pk` HAVING X <> 40 ) AND NOT OUTR "..., length=228, found_semicolon=0xadcca314) at sql_parse.cc:5811
#16 0x0822a911 in dispatch_command (command=COM_QUERY, thd=0xadb1be78, packet=0xadb1d891 "", packet_length=231) at sql_parse.cc:1051
#17 0x0822ba39 in do_command (thd=0xadb1be78) at sql_parse.cc:724
#18 0x0821bee0 in handle_one_connection (arg=0xadb1be78) at sql_connect.cc:1153
#19 0x0057d32f in start_thread () from /lib/libpthread.so.0
#20 0x0049a27e in clone () from /lib/libc.so.6

The crash is here:

15870           keys
15871         */
15872         if (table->covering_keys.is_set(ref_key))
15873           usable_keys.intersect(table->covering_keys);
15874         if (tab->pre_idx_push_select_cond)
15875           tab->select_cond= tab->select->cond= tab->pre_idx_push_select_cond; <<<<<< HERE
15876         if ((new_ref_key= test_if_subkey(order, table, ref_key, ref_key_parts,
15877                                          &usable_keys)) < MAX_KEY)
15878         {
15879           /* Found key that can be used to retrieve data in sorted order */

(gdb) print tab
$1 = (JOIN_TAB *) 0xa5d0488
(gdb) print tab->select_cond
$2 = (COND *) 0x0
(gdb) print tab->select
$3 = (SQL_SELECT *) 0x0
(gdb) print tab->select->cond
Cannot access memory at address 0x4
(gdb) print tab->pre_idx_push_select_cond
$4 = (class Item *) 0xa5d5fd0

The query in question is:

EXPLAIN EXTENDED
SELECT MIN( OUTR . `int_key` ) AS X
FROM B AS OUTR WHERE EXISTS (
 SELECT INNR . `pk`
 FROM CC AS INNR
 WHERE INNR . `int_key` IS NULL
 GROUP BY INNR . `pk`
 HAVING X <> 40
)
AND NOT OUTR . `int_nokey` <> OUTR . `pk`;

How to repeat:
If this happens again, a repeatable test case will be provided.
[3 Jul 2008 20:32] Philip Stoev
Test case simplified to the bare essentials:

CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`)
) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,1),(2,6),(3,0);

EXPLAIN EXTENDED
SELECT MIN(t1.pk)
FROM t1 WHERE EXISTS (
 SELECT t2.pk
 FROM t2
 WHERE t2.int_key IS NULL
 GROUP BY t2.pk
);

The EXPLAIN EXTENDED, the WHERE on int_key and the GROUP BY are all requred.
[9 Jul 2008 16:05] Philip Stoev
Setting to Open so that the bug verification team can check previous releases.
[7 Aug 2008 15:47] Miguel Solorzano
Not repeatable on 5.1/5.0 and 6.0.4 released version.

Version: '6.0.7-alpha-nt-debug-log'  socket: ''  port: 3600  Source distribution
080807 12:36:30 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388572
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337737 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3636380
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
006C26F9    mysqld.exe!test_if_skip_sort_order()[sql_select.cc:15895]
006A4710    mysqld.exe!JOIN::exec()[sql_select.cc:2357]
006A62E0    mysqld.exe!mysql_select()[sql_select.cc:3003]
006CC254    mysqld.exe!mysql_explain_union()[sql_select.cc:19378]
006CBC56    mysqld.exe!select_describe()[sql_select.cc:19319]
006B464F    mysqld.exe!return_zero_rows()[sql_select.cc:8949]
006A453C    mysqld.exe!JOIN::exec()[sql_select.cc:2325]
006A62E0    mysqld.exe!mysql_select()[sql_select.cc:3003]
006CC254    mysqld.exe!mysql_explain_union()[sql_select.cc:19378]
00666280    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4849]
0065EF8B    mysqld.exe!mysql_execute_command()[sql_parse.cc:2107]
006681ED    mysqld.exe!mysql_parse()[sql_parse.cc:5841]
0065D505    mysqld.exe!dispatch_command()[sql_parse.cc:1120]
0065CD3D    mysqld.exe!do_command()[sql_parse.cc:807]
0076CA67    mysqld.exe!handle_one_connection()[sql_connect.cc:1153]
0084684D    mysqld.exe!pthread_start()[my_winthread.c:86]
00B7C877    mysqld.exe!_threadstart()[thread.c:196]
7C80B713    kernel32.dll!GetModuleFileNameA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 036CE020=EXPLAIN EXTENDED
SELECT MIN(t1.pk)
FROM t1 WHERE EXISTS (
 SELECT t2.pk
 FROM t2
 WHERE t2.int_key IS NULL
 GROUP BY t2.pk
)
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
[7 Sep 2008 19:27] 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/53457

2688 Sergey Petrunia	2008-09-05
      BUG#37851: Crash in test_if_skip_sort_order tab->select is zero
      - Don't touch tab->select->cond in test_if_skip_sort_order(). We only use tab->select_cond at
        this and later stages.
[1 Nov 2008 18:30] Bugs System
Pushed into 6.0.7-alpha  (revid:sergefp@mysql.com-20080905192307-hutnawwpqgoi8wtu) (version source revid:sergefp@mysql.com-20080905192307-hutnawwpqgoi8wtu) (pib:5)
[12 Nov 2008 16:23] Sergey Petrunya
Note for the changelog:

mysqld could crash when one issues a query that could use an index to produce a query that could use one index to produce the desired ordering and another index for range access with index condition pushdown.
[12 Nov 2008 16:23] Sergey Petrunya
Note for the changelog:

mysqld could crash when one issues a query that could use an index to produce a query that could use one index to produce the desired ordering and another index for range access with index condition pushdown.
[13 Nov 2008 3:21] Paul Dubois
Noted in 6.0.9 changelog.
[16 Aug 2010 6:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:22] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 23:09] Paul Dubois
Noted in 5.6.1 changelog.