RQG: crash in Item_ref::val_str with subquery cache

[Philip Stoev]

This query:

SELECT SUBQUERY2_t1 .`col_int_key`
FROM B SUBQUERY2_t1 JOIN CC ON SUBQUERY2_t1 .`col_int_key`
WHERE table1 .`col_varchar_key` ) , table2 .`col_varchar_nokey` field10
FROM D table1 JOIN ( C table2 STRAIGHT_JOIN C table3 ON table2 .`pk` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
GROUP BY field10 ;

causes the following backtrace:

#3 0x0827e8f8 in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x081c7bd8 in Item_ref::val_str (this=0xb5e461d8, tmp=0xb5e463c0) at item.cc:6465
#6 0x081f8fe0 in Arg_comparator::compare_string (this=0xb5e46374) at item_cmpfunc.cc:1277
#7 0x081deb78 in Arg_comparator::compare (this=0xb5e46374) at item_cmpfunc.h:81
#8 0x081fad0f in Item_func_eq::val_int (this=0xb5e462f8) at item_cmpfunc.cc:1970
#9 0x0849757e in Expression_cache_tmptable::check_value (this=0xb5e457a8, value=0xb60d1a64) at sql_expression_cache.cc:250
#10 0x081c8556 in Item_cache_wrapper::check_cache (this=0xb5e456d0) at item.cc:6710
#11 0x081c8a6c in Item_cache_wrapper::val_str (this=0xb5e456d0, str=0xb5e45ac4) at item.cc:6827
#12 0x081c0c84 in Item_copy_string::copy (this=0xb5e45ab8) at item.cc:3452
#13 0x08327ae8 in copy_fields (param=0xb5e3fecc) at sql_select.cc:17251
#14 0x083202ab in end_send_group (join=0xb5e3b238, join_tab=0xb5e16e60, end_of_records=false) at sql_select.cc:14236
#15 0x0831df08 in evaluate_join_record (join=0xb5e3b238, join_tab=0xb5e16ca0, error=0) at sql_select.cc:13294
#16 0x0831d8af in sub_select (join=0xb5e3b238, join_tab=0xb5e16ca0, end_of_records=false) at sql_select.cc:13103
#17 0x0831df08 in evaluate_join_record (join=0xb5e3b238, join_tab=0xb5e16ae0, error=0) at sql_select.cc:13294
#18 0x0831d9fd in sub_select (join=0xb5e3b238, join_tab=0xb5e16ae0, end_of_records=false) at sql_select.cc:13142
#19 0x0831ccae in do_select (join=0xb5e3b238, fields=0xb5e3ffd8, table=0x0, procedure=0x0) at sql_select.cc:12649
#20 0x083030cf in JOIN::exec (this=0xb5e3b238) at sql_select.cc:2355
#21 0x08303802 in mysql_select (thd=0xae49e90, rref_pointer_array=0xae4b904, tables=0xb5d49780, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0,
    group=0xb5e15840, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb5e158e8, unit=0xae4b568, select_lex=0xae4b800) at sql_select.cc:2556
#22 0x082fbecf in handle_select (thd=0xae49e90, lex=0xae4b50c, result=0xb5e158e8, setup_tables_done_option=0) at sql_select.cc:276
#23 0x0829a5f0 in execute_sqlcom_select (thd=0xae49e90, all_tables=0xb5d49780) at sql_parse.cc:5081
#24 0x08290fd0 in mysql_execute_command (thd=0xae49e90) at sql_parse.cc:2265
#25 0x0829c7d1 in mysql_parse (thd=0xae49e90,
    inBuf=0xb5d480b0 "/* cache is on */ SELECT COUNT( DISTINCT table2 .`col_int_key` ) , (\nSELECT SUBQUERY2_t1 .`col_int_key`\nFROM B SUBQUERY2_t1 JOIN CC ON SUBQUERY2_t1 .`col_int_key`\nWHERE table1 .`col_varchar_key`"..., length=394, found_semicolon=0xb60d3230) at sql_parse.cc:6027
#26 0x0828ea02 in dispatch_command (command=COM_QUERY, thd=0xae49e90, packet=0xae62271 "", packet_length=397) at sql_parse.cc:1184
#27 0x0828dea8 in do_command (thd=0xae49e90) at sql_parse.cc:890
#28 0x0828b008 in handle_one_connection (arg=0xae49e90) at sql_connect.cc:1153
#29 0x00a08919 in start_thread () from /lib/libpthread.so.0
#30 0x00951e5e in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Partially-simplified test case below. Unfortunately unnecessary fields could not be removed:

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_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime 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=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (19,NULL,6,'2004-08-20','2004-08-20','05:03:03','05:03:03','2007-04-19 00:19:53','2007-04-19 00:19:53','f','f');
INSERT INTO `C` VALUES (20,4,2,'1900-01-01','1900-01-01','18:38:59','18:38:59','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d');

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_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

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_date_key` date DEFAULT NULL,
  `col_date_nokey` date DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_time_nokey` time DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
  KEY `col_time_key` (`col_time_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`...

[Philip Stoev (pstoev-askmonty)]

An almost minimal test case.

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
);
INSERT INTO `C` VALUES (2,7,'m');
INSERT INTO `C` VALUES (20,4,'d');

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
);

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`)
);
INSERT INTO `BB` VALUES (10,8,NULL);

SET @@optimizer_switch='subquery_cache=off';

/* cache is off */ SELECT (
SELECT `col_int_nokey`
FROM D
WHERE table1 .`col_varchar_nokey` )
FROM BB table1 JOIN C table2 ON table2 .`pk` ;

SET @@optimizer_switch='subquery_cache=on';

/* cache is on */ SELECT (
SELECT `col_int_nokey`
FROM D
WHERE table1 .`col_varchar_nokey` )
FROM BB table1 JOIN C table2 ON table2 .`pk` ;

[Philip Stoev (pstoev-askmonty)]

If ORDER BY is added to the above query, a different backtrace is observed:

SELECT (
SELECT `col_int_nokey`
FROM D
WHERE table1 .`col_varchar_nokey` ) field13
FROM BB table1 JOIN C table2 ON table2 .`pk`
ORDER BY field13

mysqld: sql_expression_cache.cc:133: void Expression_cache_tmptable::init(): Assertion `*item' failed.

/lib/libc.so.6(__assert_fail+0xf8) [0x897de8]
/home/philips/bzr/maria-5.3/sql/mysqld(Expression_cache_tmptable::init()+0x1ac) [0x8496f20]
/home/philips/bzr/maria-5.3/sql/mysqld(Expression_cache_tmptable::check_value(Item**)+0x51) [0x849748f]
/home/philips/bzr/maria-5.3/sql/mysqld(Item_cache_wrapper::check_cache()+0x5e) [0x81c8556]
/home/philips/bzr/maria-5.3/sql/mysqld(Item_cache_wrapper::val_int()+0xa2) [0x81c87ae]
/home/philips/bzr/maria-5.3/sql/mysqld(Item::send(Protocol*, String*)+0x16f) [0x81c6057]
/home/philips/bzr/maria-5.3/sql/mysqld(Item_cache_wrapper::send(Protocol*, String*)+0x47) [0x81d14c9]
/home/philips/bzr/maria-5.3/sql/mysqld(select_send::send_data(List<Item>&)+0x106) [0x8267a92]
/home/philips/bzr/maria-5.3/sql/mysqld() [0x831fa14]
/home/philips/bzr/maria-5.3/sql/mysqld() [0x831df08]
/home/philips/bzr/maria-5.3/sql/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x1ee) [0x831d8af]
/home/philips/bzr/maria-5.3/sql/mysqld() [0x831ccae]
/home/philips/bzr/maria-5.3/sql/mysqld(JOIN::exec()+0x26f1) [0x83030cf]
/home/philips/bzr/maria-5.3/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) [0x8303802]
/home/philips/bzr/maria-5.3/sql/mysqld(handle_select(THD*, st_lex*, select_result*, unsigned long)+0x1cf) [0x82fbecf]
/home/philips/bzr/maria-5.3/sql/mysqld() [0x829a5f0]
/home/philips/bzr/maria-5.3/sql/mysqld(mysql_execute_command(THD*)+0x881) [0x8290fd0]
/home/philips/bzr/maria-5.3/sql/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x248) [0x829c7d1]
/home/philips/bzr/maria-5.3/sql/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x8c3) [0x828ea02]
/home/philips/bzr/maria-5.3/sql/mysqld(do_command(THD*)+0x273) [0x828dea8]
/home/philips/bzr/maria-5.3/sql/mysqld(handle_one_connection+0x152) [0x828b008]