Crash in select_describe() with nested subqueries

[Philip Stoev]

The following query

EXPLAIN
SELECT `col_varchar_key`
FROM CC
WHERE (
SELECT SUBQUERY2_t1 .`col_int_nokey`
FROM CC SUBQUERY2_t1 JOIN CC ON ( 150 , 5 ) IN (
SELECT `col_int_key` CHILD_SUBQUERY1_field1 , `col_int_key` child_subquery1_field2
FROM CC
GROUP BY child_subquery1_field1 , child_subquery1_field2 ) ) ;

crashes on maria 5.3 and not on 5.2 or mysql 5.5.5 .

backtrace:

#3 0x0827e9e0 in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x0832ac27 in select_describe (join=0xb5f5e588, need_tmp_table=true, need_order=true, distinct=false, message=0x0) at sql_select.cc:18371
#6 0x083013e2 in JOIN::exec (this=0xb5f5e588) at sql_select.cc:1894
#7 0x083038ea in mysql_select (thd=0xa63dfb8, rref_pointer_array=0xb5f32af4, tables=0xb5f33060, wild_num=0, fields=..., conds=0x0, og_num=2, order=0x0,
    group=0xb5f33320, having=0x0, proc_param=0x0, select_options=2147764740, result=0xb5f528d0, unit=0xb5f32b90, select_lex=0xb5f329f0) at sql_select.cc:2556
#8 0x0832cd00 in mysql_explain_union (thd=0xa63dfb8, unit=0xb5f32b90, result=0xb5f528d0) at sql_select.cc:18833
#9 0x0832c910 in select_describe (join=0xb5f59718, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at sql_select.cc:18774
#10 0x083013e2 in JOIN::exec (this=0xb5f59718) at sql_select.cc:1894
#11 0x083038ea in mysql_select (thd=0xa63dfb8, rref_pointer_array=0xb5f31fbc, tables=0xb5f32410, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xb5f528d0, unit=0xb5f32058, select_lex=0xb5f31eb8) at sql_select.cc:2556
#12 0x0832cd00 in mysql_explain_union (thd=0xa63dfb8, unit=0xb5f32058, result=0xb5f528d0) at sql_select.cc:18833
#13 0x0832c910 in select_describe (join=0xb5f548a8, need_tmp_table=false, need_order=false, distinct=false, message=0x8896c84 "Impossible WHERE")
    at sql_select.cc:18774
#14 0x0831305f in return_zero_rows (join=0xb5f548a8, result=0xb5f528d0, tables=0xb5f31ca8, fields=..., send_row=false, select_options=2147764740,
    info=0x8896c84 "Impossible WHERE", having=0x0) at sql_select.cc:8357
#15 0x083010d5 in JOIN::exec (this=0xb5f548a8) at sql_select.cc:1856
#16 0x083038ea in mysql_select (thd=0xa63dfb8, rref_pointer_array=0xa63fa2c, tables=0xb5f31ca8, wild_num=0, fields=..., conds=0xb5f337b0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0xb5f528d0, unit=0xa63f690, select_lex=0xa63f928)
    at sql_select.cc:2556
#17 0x0832cd00 in mysql_explain_union (thd=0xa63dfb8, unit=0xa63f690, result=0xb5f528d0) at sql_select.cc:18833
#18 0x0829a547 in execute_sqlcom_select (thd=0xa63dfb8, all_tables=0xb5f31ca8) at sql_parse.cc:5059
#19 0x082910b8 in mysql_execute_command (thd=0xa63dfb8) at sql_parse.cc:2265
#20 0x0829c8b9 in mysql_parse (thd=0xa63dfb8,
    inBuf=0xb5f31900 "EXPLAIN\nSELECT `col_varchar_key`\nFROM CC\nWHERE (\nSELECT SUBQUERY2_t1 .`col_int_nokey`\nFROM CC SUBQUERY2_t1 JOIN CC ON ( 150 , 5 ) IN (\nSELECT `col_int_key` CHILD_SUBQUERY1_field1 , `col_int_ke"..., length=298, found_semicolon=0xb60ff230) at sql_parse.cc:6027
#21 0x0828eaea in dispatch_command (command=COM_QUERY, thd=0xa63dfb8, packet=0xa656311 "", packet_length=301) at sql_parse.cc:1184
#22 0x0828df90 in do_command (thd=0xa63dfb8) at sql_parse.cc:890
#23 0x0828b0f0 in handle_one_connection (arg=0xa63dfb8) at sql_connect.cc:1153
#24 0x00a08919 in start_thread () from /lib/libpthread.so.0
#25 0x00951e5e in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Test case:

CREATE TABLE `CC` (
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
);

INSERT INTO `CC` VALUES (7,8,'v');
INSERT INTO `CC` VALUES (1,9,'r');
INSERT INTO `CC` VALUES (5,9,'a');
INSERT INTO `CC` VALUES (3,186,'m');

EXPLAIN
SELECT `col_varchar_key`
FROM CC
WHERE (
SELECT SUBQUERY2_t1 .`col_int_nokey`
FROM CC SUBQUERY2_t1 JOIN CC ON ( 150 , 5 ) IN (
SELECT `col_int_key` CHILD_SUBQUERY1_field1 , `col_int_key` child_subquery1_field2
FROM CC
GROUP BY child_subquery1_field1 , child_subquery1_field2 ) ) ;

[Philip Stoev (pstoev-askmonty)]

Another test case

--disable_abort_on_error
SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,table_elimination=on';
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 1;
SET SESSION debug = '';
--enable_abort_on_error

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

CREATE TABLE `CC` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,'v','v');
INSERT INTO `CC` VALUES (9,'r','r');
INSERT INTO `CC` VALUES (9,'a','a');
INSERT INTO `CC` VALUES (186,'m','m');
INSERT INTO `CC` VALUES (NULL,'y','y');
INSERT INTO `CC` VALUES (2,'j','j');
INSERT INTO `CC` VALUES (3,'d','d');
INSERT INTO `CC` VALUES (0,'z','z');
INSERT INTO `CC` VALUES (133,'e','e');
INSERT INTO `CC` VALUES (1,'h','h');
INSERT INTO `CC` VALUES (8,'b','b');
INSERT INTO `CC` VALUES (5,'s','s');
INSERT INTO `CC` VALUES (5,'e','e');
INSERT INTO `CC` VALUES (8,'j','j');
INSERT INTO `CC` VALUES (6,'e','e');
INSERT INTO `CC` VALUES (51,'f','f');
INSERT INTO `CC` VALUES (4,'v','v');
INSERT INTO `CC` VALUES (7,'x','x');
INSERT INTO `CC` VALUES (6,'m','m');
INSERT INTO `CC` VALUES (4,'c','c');
CREATE TABLE `C` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,'w','w');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (3,'m','m');
INSERT INTO `C` VALUES (9,'k','k');
INSERT INTO `C` VALUES (NULL,'r','r');
INSERT INTO `C` VALUES (9,'t','t');
INSERT INTO `C` VALUES (3,'j','j');
INSERT INTO `C` VALUES (8,'u','u');
INSERT INTO `C` VALUES (8,'h','h');
INSERT INTO `C` VALUES (53,'o','o');
INSERT INTO `C` VALUES (0,NULL,NULL);
INSERT INTO `C` VALUES (5,'k','k');
INSERT INTO `C` VALUES (166,'e','e');
INSERT INTO `C` VALUES (3,'n','n');
INSERT INTO `C` VALUES (0,'t','t');
INSERT INTO `C` VALUES (1,'c','c');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (5,'y','y');
INSERT INTO `C` VALUES (6,'f','f');
INSERT INTO `C` VALUES (2,'d','d');

EXPLAIN
SELECT `col_int_key`
FROM C
WHERE (
SELECT SUBQUERY2_t1 .`col_int_key`
FROM CC SUBQUERY2_t1 STRAIGHT_JOIN CC SUBQUERY2_t2 ON SUBQUERY2_t2 .`col_varchar_key`
WHERE SUBQUERY2_t2 .`col_varchar_nokey` IN (
SELECT `col_varchar_nokey` CHILD_SUBQUERY1_field1
FROM CC
GROUP BY child_subquery1_field1 ) ) ;

DROP TABLE CC;
DROP TABLE C;

[Philip Stoev (philip-stoev)]

Reproducible with maria-5.3-mwl89

[Timour Katchaounov (timour)]

The first query in #1 works both in 5.3 and 5.3-mwl89.

The second query:
= works with current 5.3-mwl#89 (tip: revno: 2850, message: Fix LP BUG#680038)
= produces an incorrect error in 5.3-MWL#89 merged with 5.3:
  ERROR 1054 (42S22): Unknown column 'SUBQUERY2_t2.col_varchar_nokey' in 'IN/ALL/ANY subquery'
= exposes a crash with materialization=on in 5.3 (tip: revno: 2853, message: Fix LP BUG#680058):

- with materialization=on: crash in select_describe() in the lines:
        TABLE_LIST *real_table= table->pos_in_table_list;
 item_list.push_back(new Item_string(real_table->alias,
         strlen(real_table->alias),
because real_table == NULL.

- 5.3 with materialization=off:
+----+--------------------+--------------+-------+---------------+-------------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------+-------+---------------+-------------+---------+------+------+---------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
| 2 | SUBQUERY | SUBQUERY2_t1 | index | NULL | col_int_key | 5 | NULL | 20 | Using index |
| 2 | SUBQUERY | SUBQUERY2_t2 | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer |
| 3 | DEPENDENT SUBQUERY | CC | ALL | NULL | NULL | NULL | NULL | 20 | Using temporary; Using filesort |
+----+--------------------+--------------+-------+---------------+-------------+---------+------+------+---------------------------------+

[Timour Katchaounov (timour)]

The bug is no longer present in 5.3-mwl#89. From the EXPLAIN it is clear that
the cause is that the optimizer no longer tries to evaluate subqueries during
optimization in order to detect "impossible where".

[Timour Katchaounov (timour)]

Re-confirmed the bug is not present in 5.3-mwl89.

[Timour Katchaounov (timour)]

Test case pushed to 5.3. The bug itself has been fixed by MWL#89.