RQG: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.

[Philip Stoev]

When executing the query below, MariaDB 5.3 asserts as follows:

mysqld: item_subselect.cc:4635: virtual void subselect_partial_match_engine::print(String*, enum_query_type): Assertion `(0)' failed.

The assertion seems to be introduced by the following patch:

https://lists.launchpad.net/maria-developers/msg02447.html

Backtrace:

#8 0x00897de8 in __assert_fail () from /lib/libc.so.6
#9 0x08232040 in subselect_partial_match_engine::print (this=0x99c2da8, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_subselect.cc:4635
#10 0x08228d59 in Item_subselect::print (this=0x98e23c0, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_subselect.cc:559
#11 0x0822c8eb in Item_in_subselect::print (this=0x98e23c0, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_subselect.cc:1985
#12 0x081da127 in Item_func::print_args (this=0x9991ef0, str=0xb6e5f33c, from=0, query_type=QT_ORDINARY) at item_func.cc:418
#13 0x081da0ad in Item_func::print (this=0x9991ef0, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_func.cc:407
#14 0x081da127 in Item_func::print_args (this=0x98e24d0, str=0xb6e5f33c, from=0, query_type=QT_ORDINARY) at item_func.cc:418
#15 0x081da0ad in Item_func::print (this=0x98e24d0, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_func.cc:407
#16 0x081f0226 in Item_func_not::print (this=0x98e24d0, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_cmpfunc.cc:304
#17 0x081fb70e in Item_cond::print (this=0x98e2550, str=0xb6e5f33c, query_type=QT_ORDINARY) at item_cmpfunc.cc:4447
#18 0x0832547c in st_select_lex::print (this=0x98e1168, thd=0x97841a0, str=0xb6e5f33c, query_type=QT_ORDINARY) at sql_select.cc:18816
#19 0x081a0df8 in st_select_lex_unit::print (this=0x98e1300, str=0xb6e5f33c, query_type=QT_ORDINARY) at sql_lex.cc:2036
#20 0x08324e55 in TABLE_LIST::print (this=0x99919d8, thd=0x97841a0, eliminated_tables=0, str=0xb6e5f33c, query_type=QT_ORDINARY) at sql_select.cc:18678
#21 0x08324770 in print_table_array (thd=0x97841a0, eliminated_tables=0, str=0xb6e5f33c, table=0x99e70f8, end=0x99e70fc, query_type=QT_ORDINARY)
    at sql_select.cc:18509
#22 0x08324b78 in print_join (thd=0x97841a0, eliminated_tables=0, str=0xb6e5f33c, tables=0x9785b5c, query_type=QT_ORDINARY) at sql_select.cc:18603
#23 0x083253da in st_select_lex::print (this=0x9785a98, thd=0x97841a0, str=0xb6e5f33c, query_type=QT_ORDINARY) at sql_select.cc:18797
#24 0x081a0df8 in st_select_lex_unit::print (this=0x9785810, str=0xb6e5f33c, query_type=QT_ORDINARY) at sql_lex.cc:2036
#25 0x08294550 in execute_sqlcom_select (thd=0x97841a0, all_tables=0x99919d8) at sql_parse.cc:5117
#26 0x0828b069 in mysql_execute_command (thd=0x97841a0) at sql_parse.cc:2318
#27 0x08296857 in mysql_parse (thd=0x97841a0,
    inBuf=0x98e0c18 "EXPLAIN EXTENDED\nSELECT `col_int_key`\nFROM (\nSELECT SUBQUERY1_t1 .*\nFROM CC SUBQUERY1_t1 STRAIGHT_JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_varchar_key` AND SUBQUERY1_t1 .`col_int_nokey` NOT IN "..., length=242, found_semicolon=0xb6e60228) at sql_parse.cc:6079
#28 0x08288b11 in dispatch_command (command=COM_QUERY, thd=0x97841a0, packet=0x988f571 "", packet_length=245) at sql_parse.cc:1253
#29 0x08287d4f in do_command (thd=0x97841a0) at sql_parse.cc:891
#30 0x08284e96 in handle_one_connection (arg=0x97841a0) at sql_connect.cc:1599
#31 0x00a08919 in start_thread () from /lib/libpthread.so.0
#32 0x00951e5e in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

Automatic test case:

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

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');
INSERT INTO `CC` VALUES (6,NULL,'y');
INSERT INTO `CC` VALUES (92,2,'j');
INSERT INTO `CC` VALUES (7,3,'d');
INSERT INTO `CC` VALUES (NULL,0,'z');
INSERT INTO `CC` VALUES (3,133,'e');
INSERT INTO `CC` VALUES (5,1,'h');
INSERT INTO `CC` VALUES (1,8,'b');
INSERT INTO `CC` VALUES (2,5,'s');
INSERT INTO `CC` VALUES (NULL,5,'e');
INSERT INTO `CC` VALUES (1,8,'j');
INSERT INTO `CC` VALUES (0,6,'e');
INSERT INTO `CC` VALUES (210,51,'f');
INSERT INTO `CC` VALUES (8,4,'v');
INSERT INTO `CC` VALUES (7,7,'x');
INSERT INTO `CC` VALUES (5,6,'m');
INSERT INTO `CC` VALUES (NULL,4,'c');
CREATE TABLE `C` (
  `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 `C` VALUES (NULL,2,'w');
INSERT INTO `C` VALUES (7,9,'m');
INSERT INTO `C` VALUES (9,3,'m');
INSERT INTO `C` VALUES (7,9,'k');
INSERT INTO `C` VALUES (4,NULL,'r');
INSERT INTO `C` VALUES (2,9,'t');
INSERT INTO `C` VALUES (6,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (NULL,8,'h');
INSERT INTO `C` VALUES (5,53,'o');
INSERT INTO `C` VALUES (NULL,0,NULL);
INSERT INTO `C` VALUES (6,5,'k');
INSERT INTO `C` VALUES (188,166,'e');
INSERT INTO `C` VALUES (2,3,'n');
INSERT INTO `C` VALUES (1,0,'t');
INSERT INTO `C` VALUES (1,1,'c');
INSERT INTO `C` VALUES (0,9,'m');
INSERT INTO `C` VALUES (9,5,'y');
INSERT INTO `C` VALUES (NULL,6,'f');
INSERT INTO `C` VALUES (4,2,'d');

 EXPLAIN EXTENDED
SELECT `col_int_key`
FROM (
SELECT SUBQUERY1_t1 .*
FROM CC SUBQUERY1_t1 STRAIGHT_JOIN C SUBQUERY1_t2 ON SUBQUERY1_t2 .`col_varchar_key` AND SUBQUERY1_t1 .`col_int_nokey` NOT IN (
SELECT `col_int_key`
FROM C ) ) table1 ;

DROP TABLE CC;
DROP TABLE C;

[Timour Katchaounov (timour)]

Analsys:

The cause for this bug is that MariaDB 5.3 still processes derived tables (subqueries in the FROM clause)
by fully executing them during the parse phase. This will be remedied by MWL#106 once merged into the
main 5.3.

The assert statement is triggered when MATERIALIZATION is ON for EXPLAIN EXTENDED for derived
tables with an IN subquery as follows:
- mysql_parse calls JOIN::exec for the derived table as if it is regular execution (not explain).
- When materialization is ON, this call goes all the way to subselect_hash_sj_engine::exec, which
  creates a partial match engine because of NULL presence.
- In order to proceed with normal execution, the hash_sj engine substitutes itself with the created
  partial match engine.
- After the parse phase it turns out that this execution was part of EXPLAIN EXTENDED, which in
  turn calls Item_cond::print -> ... -> Item_subselect::print, which calls engine->print().
  Since subselect_hash_sj_engine::exec substituted the current Item_subselect engine with a
  partial match engine, eventually we call its ::print() method. However the partial match engines are
  designed only for execution, hence there is no implementation of this print() method.

Possible solutions:
1. best solution - merge MWL#106, then derived tables will not be materialized at all during EXPLAIN,
  and we will never execute subqueries inside derived tables during EXPLAIN.
2. Detect somehow that subselect_hash_sj_engine::exec is called during EXPLAIN. This is hard to do
  as upper JOIN::exec doesn't set the SELECT_DESCRIBE flag.
3. Implement both print() methods of the partial match engines to call subselect_hash_sj_engine::print().
4. Remove the assert, and wait for (1) above.

[Timour Katchaounov (timour)]

Simplified test case:

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

CREATE TABLE t1 (a1 int DEFAULT NULL, a2 int DEFAULT NULL);
INSERT INTO t1 VALUES (NULL,2);
INSERT INTO t1 VALUES (4,NULL);
CREATE TABLE t2 (b1 int DEFAULT NULL, b2 int DEFAULT NULL);
INSERT INTO t2 VALUES (6,NULL);
INSERT INTO t2 VALUES (NULL,0);

SET @@optimizer_switch='materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on';

EXPLAIN EXTENDED
SELECT * FROM (SELECT * FROM t1 WHERE a1 NOT IN (SELECT b2 FROM t2)) table1;

DROP TABLE t2;
DROP TABLE t1;

[Timour Katchaounov (timour)]

According to Igor's suggestion, I will take approach 4. above - remove the assert.