MySQL Bugs: #63618: Assertion in ha_innobase::records_in

Bug #63618	Assertion in ha_innobase::records_in_range
Submitted:	6 Dec 2011 1:38	Modified:	1 Aug 2012 13:06
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5.20	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any
Tags:	assertion, innodb, records_in_range, regression

Description:
A debug-enabled server asserts in ha_innobase::records_in_range when executing a query:

mysqld: /repo/mysql-server/5.5/storage/innobase/handler/ha_innodb.cc:7530: virtual ha_rows ha_innobase::records_in_range(uint, key_range*, key_range*): Assertion `min_key || max_key' failed.

The assert was introduced in revision-id marko.makela@oracle.com-20111107113719-geae9ea2tpy1li52 (Mon 2011-11-07).

How to repeat:
Load attached dump.sql, execute query:

SELECT alias1 . `col_varchar_10_latin1_key` AS field1 , alias2 . `col_date_key` AS field2 FROM  B AS alias1  LEFT  JOIN F AS alias2 ON  alias1 . `col_varchar_1024_latin1_key` =  alias2 . `col_varchar_1024_utf8`  WHERE  alias2 . `pk`  IN ('s', 'l') AND  alias2 . `pk` > 't' AND alias2 . `pk` < 'z' OR  alias2 . `pk` > 'w' AND alias2 . `pk` <= 'zzzz' AND alias2 . `pk` NOT BETWEEN 'u' AND 'z' AND alias2 . `pk`  LIKE ( '_%' ) OR alias2 . `pk` >= 'd' AND alias2 . `pk` <= 'z' OR  alias2 . `pk` NOT BETWEEN 'g' AND 'z'  ORDER BY field1  , field1;

Tables dump

Attachment: dump.sql (application/octet-stream, text), 38.42 KiB.

I think there are multiple places in the server that can trigger this assertion, for example:

check_quick_keys, opt_range.cc:

      tmp=param->table->file->records_in_range(keynr,
                                               (min_key_length ? &min_range :
                                                (key_range*) 0),
                                               (max_key_length ? &max_range :
                                                (key_range*) 0));

Thank you for the bug report. Verified with current mysql-5.5:

...
111206 16:47:49 [Note] /home/openxs/dbs/5.5/bin/mysqld: ready for connections.
Version: '5.5.20-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: /home/openxs/bzr/mysql-5.5-work/storage/innobase/handler/ha_innodb.cc:7530: virtual ha_rows ha_innobase::records_in_range(uint, key_range*, key_range*): Assertion `min_key || max_key' failed.
14:47:58 UTC - mysqld got signal 6 ;
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=8388608
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 = 338519 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x260a080
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...
stack_bottom = 7f88e8286e68 thread_stack 0x40000
/home/openxs/dbs/5.5/bin/mysqld(my_print_stacktrace+0x35)[0x8c86f9]
/home/openxs/dbs/5.5/bin/mysqld(handle_fatal_signal+0x408)[0x754400]
/lib64/libpthread.so.0[0x3ffac0eeb0]
/lib64/libc.so.6(gsignal+0x35)[0x3ffa8330c5]
/lib64/libc.so.6(abort+0x186)[0x3ffa834a76]
/lib64/libc.so.6(__assert_fail+0xf5)[0x3ffa82b905]
/home/openxs/dbs/5.5/bin/mysqld[0x97196e]
/home/openxs/dbs/5.5/bin/mysqld[0x83f7a8]
/home/openxs/dbs/5.5/bin/mysqld[0x83ed11]
/home/openxs/dbs/5.5/bin/mysqld[0x8382c6]
/home/openxs/dbs/5.5/bin/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyyb+0xa4a)[0x832b8e]
/home/openxs/dbs/5.5/bin/mysqld[0x626008]
/home/openxs/dbs/5.5/bin/mysqld[0x62765d]
/home/openxs/dbs/5.5/bin/mysqld(_ZN4JOIN8optimizeEv+0xb3b)[0x620453]
/home/openxs/dbs/5.5/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2e6)[0x625d9c]
/home/openxs/dbs/5.5/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x19c)[0x61dfec]
/home/openxs/dbs/5.5/bin/mysqld[0x5fa628]
/home/openxs/dbs/5.5/bin/mysqld(_Z21mysql_execute_commandP3THD+0x920)[0x5f33b3]
/home/openxs/dbs/5.5/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x212)[0x5fc787]
/home/openxs/dbs/5.5/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xab7)[0x5f0ab1]
/home/openxs/dbs/5.5/bin/mysqld(_Z10do_commandP3THD+0x297)[0x5efde7]
/home/openxs/dbs/5.5/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x199)[0x6ce49e]
/home/openxs/dbs/5.5/bin/mysqld(handle_one_connection+0x33)[0x6cdfaa]
/lib64/libpthread.so.0[0x3ffac06ccb]
/lib64/libc.so.6(clone+0x6d)[0x3ffa8e0c2d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f88d0004c30): SELECT alias1 . `col_varchar_10_latin1_key` AS field1 , alias2 . `col_date_key` AS field2 FROM  B AS alias1  LEFT  JOIN F AS alias2 ON  alias1 . `col_varchar_1024_latin1_key` =  alias2 . `col_varchar_1024_utf8`  WHERE  alias2 . `pk`  IN ('s', 'l') AND  alias2 . `pk` > 't' AND alias2 . `pk` < 'z' OR  alias2 . `pk` > 'w' AND alias2 . `pk` <= 'zzzz' AND alias2 . `pk` NOT BETWEEN 'u' AND 'z' AND alias2 . `pk`  LIKE ( '_%' ) OR alias2 . `pk` >= 'd' AND alias2 . `pk` <= 'z' OR  alias2 . `pk` NOT BETWEEN 'g' AND 'z'  ORDER BY field1  , field1
Connection ID (thread ID): 1
Status: NOT_KILLED
...

The debug assertion is there to indicate questionable API usage. The InnoDB code does compute the right thing (consider the whole index) when given min=NULL,max=NULL. For full-index statistics, there are the index cardinality statistics that should probably be used instead.

on 5.5.21-debug i got this:

drop table if exists `g3`;
create table `g3`(`a` int primary key,`b` binary(1) not null,key (`b`))
engine=innodb partition by key() partitions 1;
insert into `g3` values (1,1),(2,2);
select 1 from `g3` where `b` <> 0x013f;

Version: '5.5.21-debug'  socket: ''  port: 3306  MySQL Community Server (GPL)
Assertion failed: min_key || max_key, file handler\ha_innodb.cc, line 7528
mysqld.exe!my_sigabrt_handler()[my_thr_init.c:501]
mysqld.exe!raise()[winsig.c:586]
mysqld.exe!abort()[abort.c:74]
mysqld.exe!_wassert()[assert.c:153]
mysqld.exe!ha_innobase::records_in_range()[ha_innodb.cc:7528]
mysqld.exe!ha_partition::estimate_rows()[ha_partition.cc:6354]
mysqld.exe!ha_partition::records_in_range()[ha_partition.cc:6399]
mysqld.exe!check_quick_keys()[opt_range.cc:7789]
mysqld.exe!check_quick_select()[opt_range.cc:7560]
mysqld.exe!get_key_scans_params()[opt_range.cc:4959]
mysqld.exe!SQL_SELECT::test_quick_select()[opt_range.cc:2322]
mysqld.exe!get_quick_record_count()[sql_select.cc:2615]
mysqld.exe!make_join_statistics()[sql_select.cc:3058]
mysqld.exe!JOIN::optimize()[sql_select.cc:1056]
mysqld.exe!mysql_select()[sql_select.cc:2565]
mysqld.exe!handle_select()[sql_select.cc:297]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4584]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2145]
mysqld.exe!mysql_parse()[sql_parse.cc:5621]
mysqld.exe!dispatch_command()[sql_parse.cc:1036]
mysqld.exe!do_command()[sql_parse.cc:773]
mysqld.exe!do_handle_one_connection()[sql_connect.cc:814]
mysqld.exe!handle_one_connection()[sql_connect.cc:733]
mysqld.exe!pthread_start()[my_winthread.c:61]
mysqld.exe!_callthreadstartex()[threadex.c:314]
mysqld.exe!_threadstartex()[threadex.c:297]

The failing assert was too strict and was removed from the mysql-5.5 source in the following change set:

   http://lists.mysql.com/commits/142717

This fix was included in 5.5.22.