Crash in Item_equal::contains with maria-5.3-mwl89

[Philip Stoev]

The following query:

SELECT table1 .`col_time_key`
FROM C table1 JOIN ( A table2 JOIN CC table3 ON table2 .`col_int_key` ) ON table2 .`col_int_key`
WHERE table3 .`col_int_nokey` <= SOME (
SELECT SUBQUERY1_t1 .`col_int_nokey`
FROM CC SUBQUERY1_t1 RIGHT JOIN CC SUBQUERY1_t2 STRAIGHT_JOIN C SUBQUERY1_t3 ON SUBQUERY1_t3 .`col_varchar_key` = SUBQUERY1_t2 .`col_varchar_nokey` ON SUBQUERY1_t3 .`col_int_key` = SUBQUERY1_t2 .`pk` AND 2
WHERE table3 .`pk` ) ;

produces the following stack trace:

#3 <signal handler called>
#4 0x00000000005fbdf8 in Item_equal::contains (this=0x7febf41f71f8, field=0x7febf41dc0d8) at item_cmpfunc.cc:5507
#5 0x00000000005aec77 in Item_field::find_item_equal (this=0x7febf41c1a48, cond_equal=0x7febf41f4378) at item.cc:4652
#6 0x000000000072e568 in eliminate_item_equal (cond=0x7febf41c1f68, upper_levels=0x7febf41f4378, item_equal=0x7febf41f4938) at sql_select.cc:9344
#7 0x000000000072eb49 in substitute_for_best_equal_field (cond=0x7febf41c1f68, cond_equal=0x7febf41c2048, table_join_idx=0x7febf41f5458)
    at sql_select.cc:9490
#8 0x0000000000717786 in JOIN::optimize (this=0x7febf41eb3f8) at sql_select.cc:1049
#9 0x000000000058e81b in st_select_lex::optimize_unflattened_subqueries (this=0x34c66c0) at sql_lex.cc:3121
#10 0x00000000007edb7f in JOIN::optimize_unflattened_subqueries (this=0x7febf41e5378) at opt_subselect.cc:3563
#11 0x0000000000718aca in JOIN::optimize (this=0x7febf41e5378) at sql_select.cc:1412
#12 0x000000000071cc44 in mysql_select (thd=0x34c3e30, rref_pointer_array=0x34c68d8, tables=0x7febf41bdc18, wild_num=0, fields=..., conds=0x7febf41c2a78,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7febf41e5328, unit=0x34c6240, select_lex=0x34c66c0)
    at sql_select.cc:2578
#13 0x0000000000714f28 in handle_select (thd=0x34c3e30, lex=0x34c61a0, result=0x7febf41e5328, setup_tables_done_option=0) at sql_select.cc:276
#14 0x00000000006a7c91 in execute_sqlcom_select (thd=0x34c3e30, all_tables=0x7febf41bdc18) at sql_parse.cc:5081
#15 0x000000000069ebb5 in mysql_execute_command (thd=0x34c3e30) at sql_parse.cc:2265
#16 0x00000000006aa308 in mysql_parse (thd=0x34c3e30,
    inBuf=0x7febf409b328 "SELECT table1 .`col_time_key`\nFROM C table1 JOIN ( A table2 JOIN CC table3 ON table2 .`col_int_key` ) ON table2 .`col_int_key`\nWHERE table3 .`col_int_nokey` <= SOME (\nSELECT SUBQUERY1_t1 .`col_"..., length=447, found_semicolon=0x7fec080c5c88) at sql_parse.cc:6027
#17 0x000000000069c426 in dispatch_command (command=COM_QUERY, thd=0x34c3e30,
    packet=0x35a7631 "SELECT table1 .`col_time_key`\nFROM C table1 JOIN ( A table2 JOIN CC table3 ON table2 .`col_int_key` ) ON table2 .`col_int_key`\nWHERE table3 .`col_int_nokey` <= SOME (\nSELECT SUBQUERY1_t1 .`col_"..., packet_length=450) at sql_parse.cc:1184
#18 0x000000000069b86d in do_command (thd=0x34c3e30) at sql_parse.cc:890
#19 0x0000000000698411 in handle_one_connection (arg=0x34c3e30) at sql_connect.cc:1153
#20 0x000000307ba07761 in start_thread (arg=0x7fec080c6710) at pthread_create.c:301
#21 0x000000307b6e150d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

maria-5.3 is not affected

[Timour Katchaounov (timour)]

Philip, please post the DDL for this test.

[Philip Stoev (pstoev-askmonty)]

Test case:

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

CREATE TABLE `A` (
  `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 DEFAULT CHARSET=latin1;
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...

[Timour Katchaounov (timour)]

Reduced example:

--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings

CREATE TABLE t1 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  c1 int(11) DEFAULT NULL,
  c2 int(11) DEFAULT NULL,
  c3 varchar(1) DEFAULT NULL,
  c4 varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY c2 (c2),
  KEY c3 (c3,c2));

INSERT INTO t1 VALUES (10,7,8,'v','v');
INSERT INTO t1 VALUES (11,1,9,'r','r');
INSERT INTO t1 VALUES (12,5,9,'a','a');

create table t1a like t1;
insert into t1a select * from t1;

create table t1b like t1;
insert into t1b select * from t1;

CREATE TABLE t2 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  c1 int(11) DEFAULT NULL,
  c2 int(11) DEFAULT NULL,
  c3 varchar(1) DEFAULT NULL,
  c4 varchar(1) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY c2 (c2),
  KEY c3 (c3,c2));

INSERT INTO t2 VALUES (1,NULL,2,'w','w');
INSERT INTO t2 VALUES (2,7,9,'m','m');

set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';

SELECT pk
FROM t1
WHERE c1 IN
      (SELECT t1a.c1
       FROM t1a RIGHT JOIN (t1b JOIN t2 ON t2.c3 = t1b.c4) ON (t2.c2 = t1b.pk AND 2)
       WHERE t1.pk) ;

[Timour Katchaounov (timour)]

SELECT pk
FROM t1
WHERE c1 IN
      (SELECT t1a.c1
       FROM (t1b JOIN t2 ON t2.c3 = t1b.c4) LEFT JOIN t1a ON (t1a.c2 = t1b.pk AND 2)
       WHERE t1.pk) ;

[Timour Katchaounov (timour)]

The crash appears when we call Item_field::find_item_equal() for the
field 'c2' while looking for it inside the Item_func_eq that represents
the equality 't1b.c4 = t2.c3".

The method Item_field::find_item_equal() calls ::contains() on an
object of type Item_func_eq. This call resolves into
Item_equal::contains(), which is implemented to loop over its
arguments as if they are stored in the list 'fields'. However, class
Item_func_eq stores its arguments in the array 'args' instead, and
the list Item_func_eq::fields contains junk. Access to this junk
results in a crash.

One alternative cause for the bug is that it is wrong to search for 'c2'
in this Item_func_eq. Then it is necessary to investigate the reason
what went wrong 'above' this call.

The actual cause of the crash is really bad design - storing arguments
of composite Items in two parallel ways - an array and a list, so
that either the array or the list contain the child items, while the
other storage is empty or contains junk.

One possible solution is to re-implement the ::contains method
for class Item_func_eq to iterate over the 'args' array. Another solution
is to make the storage of and access to child items systematic (either
via unifying the type of storage, or via providing an iterator over the
children).

[Timour Katchaounov (timour)]

The bug no longer appears after merging MWL#89 with the latest 5.3 tree.

Confirmed via parallel debugging that there no longer exists an
Item_func_eq object in the cond_equal->current_level list passed
to Item_field::find_item_equal.