MySQL Bugs: #58756: Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT

Bug #58756	Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT
Submitted:	6 Dec 2010 14:07	Modified:	13 Jan 2011 9:43
Reporter:	John Embretsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	bzr_WL1393	OS:	Any
Assigned to:	Tor Didriksen	CPU Architecture:	Any
Tags:	wl1393

Description:
When executing a query like:

SELECT col_time_key, col_datetime_key
FROM ( SELECT * FROM t1 ) AS table1 
HAVING ( 'r' , 'e' ) IN 
  ( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 )
ORDER BY col_datetime_key 
LIMIT 10;

mysqld segfaults with the following stacktrace:

---- called from signal handler with signal 11 (SIGSEGV) ------
  [8] heap_rrnd(info = 0x4597c20, record = 0x45975d0 "ó", pos = 0xe1 "<bad address 0xe1>"), line 39 in "hp_rrnd.c"
  [9] ha_heap::rnd_pos(this = 0x4597380, buf = 0x45975d0 "ó", pos = 0x458d6e0 "á"), line 394 in "ha_heap.cc"
  [10] handler::ha_rnd_pos(this = 0x4597380, buf = 0x45975d0 "ó", pos = 0x458d6e0 "á"), line 2233 in "handler.cc"
  [11] rr_from_pointers(info = 0x45b38d0), line 532 in "records.cc"
  [12] sub_select(join = 0x4595580, join_tab = 0x45b3848, end_of_records = false), line 17332 in "sql_select.cc"
  [13] do_select(join = 0x4595580, fields = 0x4532df0, table = (nil), procedure = (nil)), line 16868 in "sql_select.cc"
  [14] JOIN::exec(this = 0x4595580), line 3311 in "sql_select.cc"
  [15] mysql_select(thd = 0x4530ab0, rref_pointer_array = 0x4532ed0, tables = 0x4592280, wild_num = 0, fields = CLASS, conds = (nil), og_num = 1U, order = 0x4593cc8, group = (nil), having = 0x4593a40, proc_param = (nil), select_options = 2147748608ULL, result = 0x4594200, unit = 0x45326b0, select_lex = 0x4532ce8), line 3528 in "sql_select.cc"
  [16] handle_select(thd = 0x4530ab0, lex = 0x4532600, result = 0x4594200, setup_tables_done_option = 0), line 311 in "sql_select.cc"
  [17] execute_sqlcom_select(thd = 0x4530ab0, all_tables = 0x4592280), line 4493 in "sql_parse.cc"
  [18] mysql_execute_command(thd = 0x4530ab0), line 2089 in "sql_parse.cc"
  [19] mysql_parse(thd = 0x4530ab0, rawbuf = 0x457ab50 "SELECT col_time_key\nFROM \n( SELECT * FROM t1 ) AS table1 \nHAVING ( 'r' , 'e' ) IN \n( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 )\nORDER BY col_datetime_key \nLIMIT 10", length = 175U, parser_state = 0xfffffd7ffea8fa88), line 5537 in "sql_parse.cc"
  [20] dispatch_command(command = COM_QUERY, thd = 0x4530ab0, packet = 0x4572b01 "", packet_length = 176U), line 1075 in "sql_parse.cc"
  [21] do_command(thd = 0x4530ab0), line 815 in "sql_parse.cc"
  [22] do_handle_one_connection(thd_arg = 0x4530ab0), line 745 in "sql_connect.cc"
  [23] handle_one_connection(arg = 0x4530ab0), line 684 in "sql_connect.cc"
  [24] pfs_spawn_thread(arg = 0x4528240), line 1078 in "pfs.cc"
  [25] _thrp_setup(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff294ae4 
  [26] _lwp_start(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff294da0

This issue is observed against the implementation of WL#1393 (Optimizing filesort with small limit):

revision-id: tor.didriksen@oracle.com-20101206094649-dwt7fkm6m0esgyzv
date: 2010-12-06 10:46:49 +0100
revno: 3254
branch-nick: next-mr-opt-team-wl1393-merge

The issue seems to be isolated to the WL#1393 implementation - parent tree mysql-next-mr-opt-team is not affected.

How to repeat:
MTR test case:

--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings

CREATE TABLE t1 (
  col_time_key time DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  col_varchar_nokey varchar(1) DEFAULT NULL,
  KEY col_time_key (col_time_key),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES ('17:53:30','2005-11-10 12:40:29','h');
INSERT INTO t1 VALUES ('11:35:49','2009-04-25 00:00:00','b');
INSERT INTO t1 VALUES (NULL,'2002-11-27 00:00:00','s');
INSERT INTO t1 VALUES ('06:01:40','2004-01-26 20:32:32','e');
INSERT INTO t1 VALUES ('05:45:11','2007-10-26 11:41:40','j');
INSERT INTO t1 VALUES ('00:00:00','2005-10-07 00:00:00','e');
INSERT INTO t1 VALUES ('00:00:00','2000-07-15 05:00:34','f');
INSERT INTO t1 VALUES ('06:11:01','2000-04-03 16:33:32','v');
INSERT INTO t1 VALUES ('13:02:46',NULL,'x');
INSERT INTO t1 VALUES ('21:44:25','2001-04-25 01:26:12','m');
INSERT INTO t1 VALUES ('22:43:58','2000-12-27 00:00:00','c');

CREATE TABLE t2 (
  col_time_key time DEFAULT NULL,
  col_datetime_key datetime DEFAULT NULL,
  col_varchar_nokey varchar(1) DEFAULT NULL,
  KEY col_time_key (col_time_key),
  KEY col_datetime_key (col_datetime_key)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t2 VALUES ('11:28:45','2004-10-11 18:13:16','w');

SELECT col_time_key, col_datetime_key
FROM 
( SELECT * FROM t1 ) AS table1 
HAVING ( 'r' , 'e' ) IN 
  ( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 )
ORDER BY col_datetime_key 
LIMIT 10;

DROP TABLE t1;
DROP TABLE t2;

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/126203

3257 Tor Didriksen	2010-12-07
      Bug #58756 Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT
      Bug #58761 Crash in Field::is_null in field.h on subquery in WHERE clause
     @ mysql-test/include/order_by.inc
        New test case
     @ mysql-test/r/bug58756.result
        New test case.
     @ mysql-test/r/order_by_icp_mrr.result
        New test case.
     @ mysql-test/r/order_by_none.result
        New test case.
     @ mysql-test/t/bug58756.test
        New test case.
     @ sql/filesort.cc
        In save_index: allocate and copy record pointers for 'count' keys.
     @ sql/sql_select.cc
        table->sort.found_records is used in init_read_record(), don't set it to found_rows.
        Instead: use tab->records to set thd->limit_found_rows, since filesort()
        now always return number of found_rows.

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/126409

3257 Tor Didriksen	2010-12-09
      Bug #58756 Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT
        allocated too few record pointers in save_index()
      Bug #58761 Crash in Field::is_null in field.h on subquery in WHERE clause
        table->sort.found_records was wrong
     @ mysql-test/include/order_by.inc
        New test case
     @ mysql-test/r/order_by_icp_mrr.result
        New test case.
     @ mysql-test/r/order_by_none.result
        New test case.
     @ mysql-test/r/subselect_innodb.result
        New test case.
     @ mysql-test/t/subselect_innodb.test
        New test case.
     @ sql/filesort.cc
        In save_index: allocate and copy pointers for all records returned by find_all_keys()
     @ sql/sql_select.cc
        table->sort.found_records is used in init_read_record(), don't set it to found_rows.
        Instead: Always use tab->records to set thd->limit_found_rows, since filesort()
        now always returns number of found_rows.