multi_range_read.cc:430: int DsMrr_impl::dsmrr_init(): Assertion `do_sort_keys || do_rowid_fetch' failed with join_cache_level=8, mrr_sort_keys=off

[Philip Stoev]

This query

SELECT table1 .`col_varchar_key`
FROM D table1 STRAIGHT_JOIN ( D table3 JOIN D table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ;

when run with join_cache_level=8 and optimizer_switch='mrr_sort_keys=off' causes the following backtrace:

mysqld: multi_range_read.cc:430: int DsMrr_impl::dsmrr_init(handler*, RANGE_SEQ_IF*, void*, uint, uint, HANDLER_BUFFER*): Assertion `do_sort_keys || do_rowid_fetch' failed.

/lib/libc.so.6(__assert_fail+0xf8) [0xa72d98]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(DsMrr_impl::dsmrr_init(handler*, st_range_seq_if*, void*, unsigned int, unsigned int, st_handler_buffer*)+0x1d4) [0x8498890]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(ha_innobase::multi_range_read_init(st_range_seq_if*, void*, unsigned int, unsigned int, st_handler_buffer*)+0x41) [0x866a751]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE_BKA::init_join_matching_records(st_range_seq_if*, unsigned int)+0x12c) [0x82da42c]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE_BKA_UNIQUE::join_matching_records(bool)+0x11e) [0x82db632]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE::join_records(bool)+0x6c) [0x82d9352]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(sub_select_cache(JOIN*, st_join_table*, bool)+0x1ae) [0x831f48e]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE::generate_full_extensions(unsigned char*)+0xc0) [0x82d9aa4]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE_BNL::join_matching_records(bool)+0x321) [0x82d9883]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE::join_records(bool)+0x6c) [0x82d9352]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN_CACHE::join_records(bool)+0x19e) [0x82d9484]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(sub_select_cache(JOIN*, st_join_table*, bool)+0xa2) [0x831f382]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x71) [0x831f59b]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld() [0x831eb55]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(JOIN::exec()+0xadf) [0x830320d]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x34a) [0x830560d]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x1cf) [0x82fdbff]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld() [0x829c1a4]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(mysql_execute_command(THD*)+0x881) [0x8292b7c]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x248) [0x829e365]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x87b) [0x8290632]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(do_command(THD*)+0x273) [0x828fb20]
/home/philips/bzr/maria-5.3-dsmrr-cpk/sql/mysqld(handle_one_connection+0x152) [0x828cb58]

[Philip Stoev (pstoev-askmonty)]

Test case:

--disable_warnings
--source include/have_innodb.inc

SET SESSION join_cache_level=9;
SET SESSION optimizer_switch='mrr_sort_keys=off';

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,6,NULL,'r','r');
INSERT INTO `D` VALUES (2,8,0,'c','c');
INSERT INTO `D` VALUES (97,7,0,'z','z');
INSERT INTO `D` VALUES (98,1,1,'j','j');
INSERT INTO `D` VALUES (99,7,8,'c','c');
INSERT INTO `D` VALUES (100,2,5,'f','f');
SELECT table1 .`col_varchar_key`
FROM D table1 STRAIGHT_JOIN ( D table3 JOIN D table4 ON table4 .`pk` = table3 .`col_int_nokey` ) ON table4 .`col_varchar_nokey` ;

[Sergey Petrunia (sergefp)]

Fixed in 5.3-dsmrr-cpk tree