MySQL Bugs: #20869: mysql seqfaults on specific dependent subquery with order desc

Bug #20869	mysql seqfaults on specific dependent subquery with order desc
Submitted:	5 Jul 2006 14:37	Modified:	20 Jul 2006 17:19
Reporter:	Alexander Y. Fomichev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0 - 5.1/5.0BK/5.1BK	OS:	Linux (linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
G' day, 
i've recently notice mysql (v.5.0.22) segfaults on query with specific dependent subquery. ( example on How to repeat: subsection )
[ realy i've simplify it, and results are very dependent on size of tables and
i've seen sometimes that query hangs forever or mysql complain no 'Incorrect key file' ]
Seems like ultimate reason is _mi_pack_key called with uninitialized indexes.
When compiled with debug=full ha_myisam::index_read asserts this, but i've no idea why _mi_check_index in mi_rkey pass ( in this regard 'Incorrect key file' is expected )
Segfault has occured only with ORDER BY DESC and if i understend it correctly
this is because QUICK_SELECT_DESC::reset don't inintialize indexes
after NESTED_LOOP_OK and join_free()->cleanup()->ha_index_or_rnd_end(), while
QUICK_RANGE_SELECT::reset() ( used by ORDER BY ASC ) do that.

this is calltrace:

Program received signal SIGABRT, Aborted.
[Switching to Thread 1141414240 (LWP 12984)]
0x00002b5d8f563279 in raise () from /lib/libc.so.6
(gdb) where
#0  0x00002b5d8f563279 in raise () from /lib/libc.so.6
#1  0x00002b5d8f56443c in abort () from /lib/libc.so.6
#2  0x00002b5d8f55d2ac in __assert_fail () from /lib/libc.so.6
#3  0x00000000006a9793 in ha_myisam::index_read (this=0x158d830, buf=0x158d980 "¥\001", key=0x15db280 "\001", key_len=16, 
    find_flag=HA_READ_BEFORE_KEY) at ha_myisam.cc:1173
#4  0x0000000000690c1a in QUICK_SELECT_DESC::get_next (this=0x15cc360) at opt_range.cc:6635
#5  0x000000000069a73c in rr_quick (info=0x15d9dd0) at records.cc:224
#6  0x000000000062dbab in join_init_read_record (tab=0x15d9d58) at sql_select.cc:10446
#7  0x000000000062c586 in sub_select (join=0x15d7258, join_tab=0x15d9d58, end_of_records=false) at sql_select.cc:9816
#8  0x000000000062c12d in do_select (join=0x15d7258, fields=0x15cd248, table=0x0, procedure=0x0) at sql_select.cc:9580
#9  0x0000000000617f36 in JOIN::exec (this=0x15d7258) at sql_select.cc:1737
#10 0x0000000000585282 in subselect_single_select_engine::exec (this=0x15ce818) at item_subselect.cc:1581
#11 0x0000000000580ffe in Item_subselect::exec (this=0x15c8858) at item_subselect.cc:198
#12 0x0000000000581dae in Item_singlerow_subselect::val_str (this=0x15c8858, str=0x15c8a28) at item_subselect.cc:477
#13 0x0000000000553f0c in Arg_comparator::compare_binary_string (this=0x15c89c8) at item_cmpfunc.cc:436
#14 0x000000000056001b in Arg_comparator::compare (this=0x15c89c8) at item_cmpfunc.h:65
#15 0x0000000000555471 in Item_func_eq::val_int (this=0x15c8920) at item_cmpfunc.cc:803
#16 0x000000000062c708 in evaluate_join_record (join=0x15c8b08, join_tab=0x15d8e00, error=0, report_error=0x15c1760 "")
    at sql_select.cc:9864
#17 0x000000000062c5cb in sub_select (join=0x15c8b08, join_tab=0x15d8e00, end_of_records=false) at sql_select.cc:9823
#18 0x000000000062c12d in do_select (join=0x15c8b08, fields=0x15c0be0, table=0x0, procedure=0x0) at sql_select.cc:9580
#19 0x0000000000617f36 in JOIN::exec (this=0x15c8b08) at sql_select.cc:1737
#20 0x00000000006184a2 in mysql_select (thd=0x15c06b8, rref_pointer_array=0x15c0d18, tables=0x15ccaa0, wild_num=1, fields=@0x15c0be0, 
    conds=0x15c8920, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153344, result=0x15c8ae8, 
    unit=0x15c0738, select_lex=0x15c0ae0) at sql_select.cc:1901
#21 0x0000000000612ee7 in handle_select (thd=0x15c06b8, lex=0x15c0720, result=0x15c8ae8, setup_tables_done_option=0) at sql_select.cc:238
#22 0x00000000005d50d8 in mysql_execute_command (thd=0x15c06b8) at sql_parse.cc:2499
#23 0x00000000005dd78d in mysql_parse (thd=0x15c06b8, 
    inBuf=0x15cc898 "SELECT * from t1,t2 WHERE t1.t1 = (SELECT t1.t1 FROM t1 WHERE t1.t1 < t2.t1  AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t1 DESC LIMIT 1)", length=133) at sql_parse.cc:5695
#24 0x00000000005d30f8 in dispatch_command (command=COM_QUERY, thd=0x15c06b8, packet=0x15c4809 "", packet_length=134) at sql_parse.cc:1736
#25 0x00000000005d27cf in do_command (thd=0x15c06b8) at sql_parse.cc:1522
#26 0x00000000005d18f6 in handle_one_connection (arg=0x15c06b8) at sql_parse.cc:1165
#27 0x00002b5d8ed9009b in start_thread () from /lib/libpthread.so.0
#28 0x00002b5d8f5eb933 in clone () from /lib/libc.so.6
#29 0x0000000000000000 in ?? ()

How to repeat:
>C compiler:    gcc (GCC) 3.4.5 (Gentoo 3.4.5, ssp-3.4.5-1.0, pie-8.7.9)
>C++ compiler:  gcc (GCC) 3.4.5 (Gentoo 3.4.5, ssp-3.4.5-1.0, pie-8.7.9)
>Environment:
        <machine, os, target, libraries (multiple lines)>
System: Linux alien 2.6.17-rc3 #2 SMP Thu May 4 20:02:33 MSD 2006 x86_64 Intel(R) Xeon(TM) CPU 3.00GHz GNU/Linux
Architecture: x86_64

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc/x86_64-pc-linux-gnu/3.4.5/specs
Configured with: /var/tmp/portage/gcc-3.4.5/work/gcc-3.4.5/configure --prefix=/usr 
--bindir=/usr/x86_64-pc-linux-gnu/gcc-bin/3.4.5 
--includedir=/usr/lib/gcc/x86_64-pc-linux-gnu/3.4.5/include 
--datadir=/usr/share/gcc-data/x86_64-pc-linux-gnu/3.4.5 
--mandir=/usr/share/gcc-data/x86_64-pc-linux-gnu/3.4.5/man 
--infodir=/usr/share/gcc-data/x86_64-pc-linux-gnu/3.4.5/info 
--with-gxx-include-dir=/usr/lib/gcc/x86_64-pc-linux-gnu/3.4.5/include/g++-v3 
--host=x86_64-pc-linux-gnu --build=x86_64-pc-linux-gnu --disable-altivec 
--enable-nls --without-included-gettext --with-system-zlib --disable-checking 
--disable-werror --disable-libunwind-exceptions --enable-multilib 
--disable-libgcj --enable-languages=c,c++ --enable-shared 
--enable-threads=posix --enable-__cxa_atexit --enable-clocale=gnu

Thread model: posix
gcc version 3.4.5 (Gentoo 3.4.5, ssp-3.4.5-1.0, pie-8.7.9)
Compilation info: CC='gcc'  CFLAGS='-march=nocona -O4 -pipe -D_GNU_SOURCE -DHAVE_ERRNO_AS_DEFINE=1'  CXX='gcc'  CXXFLAGS='-march=nocona -O4
-pipe -D_GNU_SOURCE -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun  6 19:45 /lib/libc.so.6 -> libc-2.3.6.so
-rwxr-xr-x 1 root root 1248608 Jun  6 19:45 /lib/libc-2.3.6.so
-rw-r--r-- 1 root root 3882318 Jun  6 19:45 /usr/lib/libc.a
-rwxr-xr-x 1 root root 210 Jun  6 19:45 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr' '--libdir=/usr/lib' '--exec-prefix=/usr' 
'--datadir=/usr/share' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' 
'--mandir=/usr/share/man' '--infodir=/usr/share/info' 
'--includedir=/usr/include' '--localstatedir=/var/lib/mysql' 
'--enable-assembler' '--with-mysql-user=mysql' '--with-innodb' 
'--with-ndbcluster' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' 
'--with-charset=cp1251' '--with-extra-charsets=latin1,koi8r,cp1251,utf8' 
'--with-openssl' '--without-readline' '--without-libedit' 
'--enable-thread-safe-client' '--with-named-thread-libs=-lpthread' 
'--build=x86_64-pc-linux-gnu' 
'CFLAGS=-march=nocona -O4 -pipe -D_GNU_SOURCE -DHAVE_ERRNO_AS_DEFINE=1' 
'CXXFLAGS=-march=nocona -O4 -pipe -D_GNU_SOURCE -felide-constructors -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW' 
'CXX=gcc' 'build_alias=x86_64-pc-linux-gnu'

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `i1` int(11) NOT NULL default '0',
  `i2` int(11) NOT NULL default '0',
  `t1` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`i1`,`i2`,`t1`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (24,1,'2005-03-03 16:31:31'),(24,1,'2005-05-27 12:40:07'),(24,1,'2005-05-27 12:40:08'),(24,1,'2005-05-27 12:40:10'),(24,1,'2005-05-27 12:40:25'),(24,1,'2005-05-27 12:40:30'),(24,2,'2005-03-03 13:43:05'),(24,2,'2005-03-03 16:23:31'),(24,2,'2005-03-03 16:31:30'),(24,2,'2005-05-27 12:37:02'),(24,2,'2005-05-27 12:40:06');
UNLOCK TABLES;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `i1` int(11) NOT NULL default '0',
  `i2` int(11) NOT NULL default '0',
  `t1` datetime default NULL,
  PRIMARY KEY  (`i1`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
LOCK TABLES `t2` WRITE;
INSERT INTO `t2` VALUES (1,24,'2006-06-20 12:29:40');
UNLOCK TABLES;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;

SELECT * from t1,t2 WHERE t1.t1 = (SELECT t1.t1 FROM t1 WHERE t1.t1 < t2.t1  AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t1 DESC LIMIT 1);

Suggested fix:
I've ugly workaround which fix issue, but this is not 'suggested'
fix of course.

--- a/sql/opt_range.cc	2006-05-25 12:56:56.000000000 +0400
+++ b/sql/opt_range.cc	2006-07-05 14:58:58.000000000 +0400
@@ -6576,7 +6576,13 @@
   delete q;
 }
 
-
+int QUICK_SELECT_DESC::reset(void)
+{
+  file->inited == handler::NONE && (error= file->ha_index_init(index));
+  next=0;
+  rev_it.rewind();
+  return 0;
+}
 int QUICK_SELECT_DESC::get_next()
 {
   DBUG_ENTER("QUICK_SELECT_DESC::get_next");
diff -urN a/sql/opt_range.h b/sql/opt_range.h
--- a/sql/opt_range.h	2006-05-25 12:56:43.000000000 +0400
+++ b/sql/opt_range.h	2006-07-05 14:59:04.000000000 +0400
@@ -668,7 +668,7 @@
 #ifdef NOT_USED
   bool test_if_null_range(QUICK_RANGE *range, uint used_key_parts);
 #endif
-  int reset(void) { next=0; rev_it.rewind(); return 0; }
+  int reset(void);
   List<QUICK_RANGE> rev_ranges;
   List_iterator<QUICK_RANGE> rev_it;
 };

I'v just forget to say that 5.1 ( actually 5.1.12-20060608 ) affected too but it's more predictable and i've only seen 'ERROR 126 (HY000): Incorrect key 
file for table' at the same conditions.

Back Trace 5.0.23BK Suse Linux 32-bit

Attachment: bt-20869.txt (text/plain), 8.89 KiB.

Thank you for the bug report. Verified on Linux Suse 10 32-bit:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * from t1,t2 WHERE t1.t1 = (SELECT t1.t1 FROM t1 WHERE t1.t1 < t2.t1  AND
    -> t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t1 DESC LIMIT 1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * from t1,t2 WHERE t1.t1 = (SELECT t1.t1 FROM t1 WHERE t1.t1 < t2.t1  AND
    -> t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t1 DESC LIMIT 1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * from t1,t2 WHERE t1.t1 = (SELECT t1.t1 FROM t1 WHERE t1.t1 < t2.t1  AND
    -> t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t1 DESC LIMIT 1);
Empty set (0.00 sec)

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

AFAICS it works, thanks.
mysql> SELECT * from t1,t2 WHERE t1.t1 = (SELECT t1.t1 FROM t1 WHERE t1.t1 < t2.t1  AND t1.i2=1 AND t2.i1=t1.i1 ORDER BY t1.t1 DESC LIMIT 1);
Empty set (0.00 sec)

The bug caused a crash of the server if a subquery with
ORDER BY DESC used the range access method.
The bug happened because the method QUICK_SELECT_DESC::reset
was not reworked after MRR interface had been introduced

Fixed in 5.0.25

Noted in 5.0.25 changelog.