Crash in replace_where_subcondition with nested subquery and semijoin=on

[Philip Stoev]

The following query crashes maria 5.3 when executed as a prepared statement or a stored procedure:

SELECT `col_varchar_key`
FROM (
SELECT SUBQUERY1_t1 .*
FROM BB SUBQUERY1_t1 STRAIGHT_JOIN ( CC SUBQUERY1_t2 JOIN C ON SUBQUERY1_t2 .`col_varchar_key` ) ON SUBQUERY1_t2 .`col_varchar_key` AND ( 's' , 'r' ) IN (
SELECT CHILD_SUBQUERY1_t1 .`col_varchar_nokey` , CHILD_SUBQUERY1_t2 .`col_varchar_nokey`
FROM C CHILD_SUBQUERY1_t1 JOIN C CHILD_SUBQUERY1_t2 ON CHILD_SUBQUERY1_t1 .`col_int_nokey` ) ) table3 ;

backtrace:

#3 0x0827e9e0 in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x083b541b in replace_where_subcondition (join=0xb5d93ee0, expr=0xb5d72ddc, old_cond=0xb5d74320, new_cond=0xb5d8d070, do_fix_fields=false)
    at opt_subselect.cc:550
#6 0x083b5303 in convert_join_subqueries_to_semijoins (join=0xb5d93ee0) at opt_subselect.cc:508
#7 0x082fd3de in JOIN::optimize (this=0xb5d93ee0) at sql_select.cc:740
#8 0x08303865 in mysql_select (thd=0xa8b0fd0, rref_pointer_array=0xb5d71e74, tables=0xb5d722e8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2416201216, result=0xb5d92150, unit=0xb5d71f10, select_lex=0xb5d71d70) at sql_select.cc:2542
#9 0x08447f45 in mysql_derived_filling (thd=0xa8b0fd0, lex=0xb5d710f8, orig_table_list=0xb5d8c170) at sql_derived.cc:295
#10 0x08447837 in mysql_handle_derived (lex=0xb5d710f8, processor=0x8447d60 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
#11 0x082e4918 in open_and_lock_tables_derived (thd=0xa8b0fd0, tables=0xb5d8c170, derived=true) at sql_base.cc:5067
#12 0x082a0d0f in open_and_lock_tables (thd=0xa8b0fd0, tables=0xb5d8c170) at mysql_priv.h:1648
#13 0x0829a4b1 in execute_sqlcom_select (thd=0xa8b0fd0, all_tables=0xb5d8c170) at sql_parse.cc:5046
#14 0x082910b8 in mysql_execute_command (thd=0xa8b0fd0) at sql_parse.cc:2265
#15 0x084623f1 in sp_instr_stmt::exec_core (this=0xb5d8c370, thd=0xa8b0fd0, nextp=0xb609f2f8) at sp_head.cc:2927
#16 0x08461d5d in sp_lex_keeper::reset_lex_and_exec_core (this=0xb5d8c398, thd=0xa8b0fd0, nextp=0xb609f2f8, open_tables=false, instr=0xb5d8c370)
    at sp_head.cc:2748
#17 0x084621ca in sp_instr_stmt::execute (this=0xb5d8c370, thd=0xa8b0fd0, nextp=0xb609f2f8) at sp_head.cc:2870
#18 0x0845e4ce in sp_head::execute (this=0xb5d707f0, thd=0xa8b0fd0) at sp_head.cc:1249
#19 0x08460117 in sp_head::execute_procedure (this=0xb5d707f0, thd=0xa8b0fd0, args=0xa8b2c04) at sp_head.cc:1983
#20 0x0829818d in mysql_execute_command (thd=0xa8b0fd0) at sql_parse.cc:4419
#21 0x0829c8b9 in mysql_parse (thd=0xa8b0fd0, inBuf=0xb5d31900 "CALL stored_proc_23161()", length=24, found_semicolon=0xb60a0230) at sql_parse.cc:6027
#22 0x0828eaea in dispatch_command (command=COM_QUERY, thd=0xa8b0fd0, packet=0xa8c9329 "CALL stored_proc_23161()", packet_length=24) at sql_parse.cc:1184
#23 0x0828df90 in do_command (thd=0xa8b0fd0) at sql_parse.cc:890
#24 0x0828b0f0 in handle_one_connection (arg=0xa8b0fd0) at sql_connect.cc:1153
#25 0x00a08919 in start_thread () from /lib/libpthread.so.0
#26 0x00951e5e in clone () from /lib/libc.so.6

EXPLAIN:

1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

[Philip Stoev (pstoev-askmonty)]

Test case:

CREATE TABLE `CC` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `C` (
  `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,
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `BB` (
  `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,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (8,8,NULL,NULL);

CREATE PROCEDURE stored_proc_23161 ( )
SELECT `col_varchar_key`
FROM (
SELECT SUBQUERY1_t1 .*
FROM BB SUBQUERY1_t1 STRAIGHT_JOIN ( CC SUBQUERY1_t2 JOIN C ON SUBQUERY1_t2 .`col_varchar_key` ) ON SUBQUERY1_t2 .`col_varchar_key` AND ( 's' , 'r' ) IN (
SELECT CHILD_SUBQUERY1_t1 .`col_varchar_nokey` , CHILD_SUBQUERY1_t2 .`col_varchar_nokey`
FROM C CHILD_SUBQUERY1_t1 JOIN C CHILD_SUBQUERY1_t2 ON CHILD_SUBQUERY1_t1 .`col_int_nokey` ) ) table3 ;
CALL stored_proc_23161();
DROP TABLE CC;
DROP TABLE C;
DROP TABLE BB;

[Philip Stoev (pstoev-askmonty)]

With prepared statements:

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `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_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v','v');
INSERT INTO `CC` VALUES (11,9,'r','r');
INSERT INTO `CC` VALUES (12,9,'a','a');
INSERT INTO `CC` VALUES (13,186,'m','m');
INSERT INTO `CC` VALUES (14,NULL,'y','y');
INSERT INTO `CC` VALUES (15,2,'j','j');
INSERT INTO `CC` VALUES (16,3,'d','d');
INSERT INTO `CC` VALUES (17,0,'z','z');
INSERT INTO `CC` VALUES (18,133,'e','e');
INSERT INTO `CC` VALUES (19,1,'h','h');
INSERT INTO `CC` VALUES (20,8,'b','b');
INSERT INTO `CC` VALUES (21,5,'s','s');
INSERT INTO `CC` VALUES (22,5,'e','e');
INSERT INTO `CC` VALUES (23,8,'j','j');
INSERT INTO `CC` VALUES (24,6,'e','e');
INSERT INTO `CC` VALUES (25,51,'f','f');
INSERT INTO `CC` VALUES (26,4,'v','v');
INSERT INTO `CC` VALUES (27,7,'x','x');
INSERT INTO `CC` VALUES (28,6,'m','m');
INSERT INTO `CC` VALUES (29,4,'c','c');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `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_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'w','w');
INSERT INTO `C` VALUES (2,9,'m','m');
INSERT INTO `C` VALUES (3,3,'m','m');
INSERT INTO `C` VALUES (4,9,'k','k');
INSERT INTO `C` VALUES (5,NULL,'r','r');
INSERT INTO `C` VALUES (6,9,'t','t');
INSERT INTO `C` VALUES (7,3,'j','j');
INSERT INTO `C` VALUES (8,8,'u','u');
INSERT INTO `C` VALUES (9,8,'h','h');
INSERT INTO `C` VALUES (10,53,'o','o');
INSERT INTO `C` VALUES (11,0,NULL,NULL);
INSERT INTO `C` VALUES (12,5,'k','k');
INSERT INTO `C` VALUES (13,166,'e','e');
INSERT INTO `C` VALUES (14,3,'n','n');
INSERT INTO `C` VALUES (15,0,'t','t');
INSERT INTO `C` VALUES (16,1,'c','c');
INSERT INTO `C` VALUES (17,9,'m','m');
INSERT INTO `C` VALUES (18,5,'y','y');
INSERT INTO `C` VALUES (19,6,'f','f');
INSERT INTO `C` VALUES (20,2,'d','d');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `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_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,NULL,NULL);

PREPARE st1 FROM 'SELECT table1 .`pk`
FROM CC table1
STRAIGHT_JOIN ( BB table2 STRAIGHT_JOIN C ON table2 .`col_int_key` )
ON ( table1 .`col_varchar_key` , table2 .`col_varchar_key` )
IN ( SELECT `col_varchar_nokey` , `col_varchar_key` FROM CC ) ';

EXECUTE st1;

[Philip Stoev (pstoev-askmonty)]

Still repeatable with maria-5.3 . simplified test case:

CREATE TABLE t1 ( f1 int(11), f3 varchar(1), f4 varchar(1)) ;

CREATE TABLE t2 ( f2 int(11), KEY (f2));

CREATE TABLE t3 ( f4 varchar(1)) ;

PREPARE st1 FROM '
SELECT *
FROM t1
STRAIGHT_JOIN ( t2 STRAIGHT_JOIN t3 ON t2.f2 )
ON (t1.f3) IN ( SELECT f4 FROM t1 )
';
EXECUTE st1;

[Philip Stoev (pstoev-askmonty)]

Not reproducible with semijoin=off. So, assigning to Sergey.

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

[Philip Stoev (pstoev-askmonty)]

Still repeatable -- run subselect_no_mat.test with --view-protocol

[Philip Stoev (pstoev-askmonty)]

Even simpler test case without prepared statements:

SET SESSION optimizer_switch='semijoin=on,materialization=off,firstmatch=off,loosescan=off';

CREATE TABLE t1 ( f1 int) ;

CREATE TABLE t2 ( f1 int) ;

CREATE TABLE t3 ( f1 int) ;

SELECT * FROM (
        SELECT t3.*
        FROM t2 STRAIGHT_JOIN t3
        ON t3.f1
        AND (t3.f1 ) IN (
                SELECT t1.f1
                FROM t1
        )
) AS alias1;