mysqld: sql_select.cc:12918: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed in maria-5.3-mwl89

[Philip Stoev]

The query from the test case produced the following assertion when run against maria-5.3-mwl89 using semijoin=off,materialization=off:

mysqld: sql_select.cc:12918: enum_nested_loop_state sub_select_cache(JOIN*, JOIN_TAB*, bool): Assertion `cache != __null' failed.

#8 0x00a72d98 in __assert_fail () from /lib/libc.so.6
#9 0x0831de6c in sub_select_cache (join=0xb5e8d308, join_tab=0xb5e9c4b8, end_of_records=false) at sql_select.cc:12918
#10 0x0831e88c in evaluate_join_record (join=0xb5e8d308, join_tab=0xb5e9c2f8, error=0) at sql_select.cc:13325
#11 0x0831e233 in sub_select (join=0xb5e8d308, join_tab=0xb5e9c2f8, end_of_records=false) at sql_select.cc:13134
#12 0x0831d632 in do_select (join=0xb5e8d308, fields=0xb5e920a8, table=0x0, procedure=0x0) at sql_select.cc:12680
#13 0x08303bd6 in JOIN::exec (this=0xb5e8d308) at sql_select.cc:2396
#14 0x08235ad7 in subselect_single_select_engine::exec (this=0xb5e6ff08) at item_subselect.cc:2741
#15 0x082300f1 in Item_subselect::exec (this=0xb5e6fe48) at item_subselect.cc:540
#16 0x08230fe1 in Item_singlerow_subselect::val_int (this=0xb5e6fe48) at item_subselect.cc:977
#17 0x081cf9d4 in Item::val_int_result (this=0xb5e6fe48) at item.h:779
#18 0x081cb86e in Item_cache_int::cache_value (this=0xb5e9da90) at item.cc:7712
#19 0x081d3638 in Item_cache_wrapper::cache (this=0xb5e9da30) at item.cc:6725
#20 0x081c8eea in Item_cache_wrapper::val_int (this=0xb5e9da30) at item.cc:6779
#21 0x081cf9d4 in Item::val_int_result (this=0xb5e9da30) at item.h:779
#22 0x083c4778 in make_sortkey (param=0xb6d48c18,
    to=0xb5ed87a8 "\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245"...,
    ref_pos=0xb5e763a8 "\230\001\360\265\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245\245h4z\025\355\003") at filesort.cc:852
#23 0x083c3dd4 in find_all_keys (param=0xb6d48c18, select=0x0, sort_keys=0xb5ed8610, buffpek_pointers=0xb6d48a50, tempfile=0xb6d48b34, indexfile=0x0)
    at filesort.cc:625
#24 0x083c298d in filesort (thd=0xae8e3e8, table=0xb5ec6dc8, sortorder=0xb5ea2010, s_length=5, select=0x0, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xb6d48d80) at filesort.cc:246
#25 0x083253f8 in create_sort_index (thd=0xae8e3e8, join=0xb5e83618, order=0xb5e737a0, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=true) at sql_select.cc:15880
#26 0x083038db in JOIN::exec (this=0xb5e83618) at sql_select.cc:2339
#27 0x08304308 in mysql_select (thd=0xae8e3e8, rref_pointer_array=0xae8fe5c, tables=0xb5e70538, wild_num=0, fields=..., conds=0xb5e73658, og_num=9,
    order=0x0, group=0xb5e737a0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb5e73ed8, unit=0xae8fac0, select_lex=0xae8fd58)
    at sql_select.cc:2597
#28 0x082fc7e3 in handle_select (thd=0xae8e3e8, lex=0xae8fa64, result=0xb5e73ed8, setup_tables_done_option=0) at sql_select.cc:276
#29 0x0829af04 in execute_sqlcom_select (thd=0xae8e3e8, all_tables=0xb5e70538) at sql_parse.cc:5081
#30 0x082918e4 in mysql_execute_command (thd=0xae8e3e8) at sql_parse.cc:2265
#31 0x0829d0e5 in mysql_parse (thd=0xae8e3e8,
    inBuf=0xb5e31900 "SELECT table1 . `pk` AS field1 , (\nSELECT MIN( SUBQUERY1_t1 . `col_int_key` ) AS SUBQUERY1_field1\nFROM ( B AS SUBQUERY1_t1 INNER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUE"..., length=1715, found_semicolon=0xb6d4a230) at sql_parse.cc:6027
#32 0x0828f316 in dispatch_command (command=COM_QUERY, thd=0xae8e3e8,
    packet=0xaea6451 "SELECT table1 . `pk` AS field1 , (\nSELECT MIN( SUBQUERY1_t1 . `col_int_key` ) AS SUBQUERY1_field1\nFROM ( B AS SUBQUERY1_t1 INNER JOIN C AS SUBQUERY1_t2 ON (SUBQUERY1_t2 . `col_int_key` = SUBQUE"..., packet_length=1716) at sql_parse.cc:1184
#33 0x0828e7bc in do_command (thd=0xae8e3e8) at sql_parse.cc:890
#34 0x0828b91c in handle_one_connection (arg=0xae8e3e8) at sql_connect.cc:1153
#35 0x00bea919 in start_thread () from /lib/libpthread.so.0
#36 0x00b2ccbe in clone () from /lib/libc.so.6

bzr version-info:

revision-id: <email address hidden>
date: 2010-09-30 18:32:44 +0300
build-date: 2010-10-01 10:24:23 +0300
revno: 2811
branch-nick: maria-5.3-mwl89

Unfortunately due to bug #643424 , it was not possible to apply automatic simplification to the query.

[Philip Stoev (pstoev-askmonty)]

Test case. Maria-5.3 is not affected.

SET SESSION optimizer_switch='semijoin=off,materialization=off';

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'01:27:35','v','v');
INSERT INTO `CC` VALUES (11,1,9,'19:48:31','r','r');
INSERT INTO `CC` VALUES (12,5,9,'00:00:00','a','a');
INSERT INTO `CC` VALUES (13,3,186,'19:53:05','m','m');
INSERT INTO `CC` VALUES (14,6,NULL,'19:18:56','y','y');
INSERT INTO `CC` VALUES (15,92,2,'10:55:12','j','j');
INSERT INTO `CC` VALUES (16,7,3,'00:25:00','d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'12:35:47','z','z');
INSERT INTO `CC` VALUES (18,3,133,'19:53:03','e','e');
INSERT INTO `CC` VALUES (19,5,1,'17:53:30','h','h');
INSERT INTO `CC` VALUES (20,1,8,'11:35:49','b','b');
INSERT INTO `CC` VALUES (21,2,5,NULL,'s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'06:01:40','e','e');
INSERT INTO `CC` VALUES (23,1,8,'05:45:11','j','j');
INSERT INTO `CC` VALUES (24,0,6,'00:00:00','e','e');
INSERT INTO `CC` VALUES (25,210,51,'00:00:00','f','f');
INSERT INTO `CC` VALUES (26,8,4,'06:11:01','v','v');
INSERT INTO `CC` VALUES (27,7,7,'13:02:46','x','x');
INSERT INTO `CC` VALUES (28,5,6,'21:44:25','m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'22:43:58','c','c');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,'11:28:45','w','w');
INSERT INTO `C` VALUES (2,7,9,'20:25:14','m','m');
INSERT INTO `C` VALUES (3,9,3,'13:47:24','m','m');
INSERT INTO `C` VALUES (4,7,9,'19:24:11','k','k');
INSERT INTO `C` VALUES (5,4,NULL,'15:59:13','r','r');
INSERT INTO `C` VALUES (6,2,9,'00:00:00','t','t');
INSERT INTO `C` VALUES (7,6,3,'15:15:04','j','j');
INSERT INTO `C` VALUES (8,8,8,'11:32:06','u','u');
INSERT INTO `C` VALUES (9,NULL,8,'18:32:33','h','h');
INSERT INTO `C` VALUES (10,5,53,'15:19:25','o','o');
INSERT INTO `C` VALUES (11,NULL,0,'19:03:19',NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'00:39:46','k','k');
INSERT INTO `C` VALUES (13,188,166,NULL,'e','e');
INSERT INTO `C` VALUES (14,2,3,'00:00:00','n','n');
INSERT INTO `C` VALUES (15,1,0,'13:12:11','t','t');
INSERT INTO `C` VALUES (16,1,1,'04:56:48','c','c');
INSERT INTO `C` VALUES (17,0,9,'19:56:05','m','m');
INSERT INTO `C` VALUES (18,9,5,'19:35:19',...

[Philip Stoev (pstoev-askmonty)]

Maybe related to http://bugs.mysql.com/bug.php?id=54511