Crash in make_join_select on second execution of prepared statement with view

[Philip Stoev]

Repeatable in maria-5.2, maria-5.3. Not repeatable in mysql-5.5 . The following query:

SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0

crashes on its second execution as a prepared statement. This particular example uses an ON condition that is not a boolean expression, but the bug has also been observed with a realistic ON condition.

backtrace:

#3 0x08289e44 in handle_segfault (sig=11) at mysqld.cc:2774
#4 <signal handler called>
#5 0x0831aee5 in make_join_select (join=0xae535b40, select=0xae513b40, cond=0xae512fb0) at sql_select.cc:7179
#6 0x0830ae14 in JOIN::optimize (this=0xae535b40) at sql_select.cc:1130
#7 0x0830fd2d in mysql_select (thd=0x9a04da8, rref_pointer_array=0xae52a5c4, tables=0xae52ac88, wild_num=0, fields=..., conds=0xae512fb0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xae52b648, unit=0xae52a224, select_lex=0xae52a4c0)
    at sql_select.cc:2687
#8 0x0830860f in handle_select (thd=0x9a04da8, lex=0xae52a1c8, result=0xae52b648, setup_tables_done_option=0) at sql_select.cc:282
#9 0x082a5954 in execute_sqlcom_select (thd=0x9a04da8, all_tables=0xae52ac88) at sql_parse.cc:5094
#10 0x0829c79e in mysql_execute_command (thd=0x9a04da8) at sql_parse.cc:2239
#11 0x0834f168 in Prepared_statement::execute (this=0xae527f78, expanded_query=0xae8e078c, open_cursor=false) at sql_prepare.cc:3677
#12 0x0834e650 in Prepared_statement::execute_loop (this=0xae527f78, expanded_query=0xae8e078c, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3352
#13 0x0834cf6c in mysql_sql_stmt_execute (thd=0x9a04da8) at sql_prepare.cc:2613
#14 0x0829c7c7 in mysql_execute_command (thd=0x9a04da8) at sql_parse.cc:2248
#15 0x082a7ef7 in mysql_parse (thd=0x9a04da8, rawbuf=0xae512eb0 "EXECUTE prep_stmt", length=17, found_semicolon=0xae8e1228) at sql_parse.cc:6094
#16 0x0829a427 in dispatch_command (command=COM_QUERY, thd=0x9a04da8, packet=0x9a5f901 "EXECUTE prep_stmt", packet_length=17) at sql_parse.cc:1215
#17 0x08299885 in do_command (thd=0x9a04da8) at sql_parse.cc:904
#18 0x08296938 in handle_one_connection (arg=0x9a04da8) at sql_connect.cc:1154
#19 0x00821919 in start_thread () from /lib/libpthread.so.0
#20 0x0076acce in clone () from /lib/libc.so.6

test case:

CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ;
INSERT IGNORE INTO t1 VALUES (20, 2);

CREATE TABLE t2 ( f3 int NOT NULL ) ;
INSERT IGNORE INTO t2 VALUES (7);

CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;

PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0';

EXECUTE prep_stmt;
EXECUTE prep_stmt;

[Philip Stoev (pstoev-askmonty)]

Not repeatable with: mysql 5.1, mysql 5.5

Repeatable with: maria-5.1, maria-5.2, maria-5.3

[Sergey Petrunia (sergefp)]

Use of a VIEW is essential to repeat the bug. If I replace the v2 VIEW with base table behind it, the bug goes away.

[Sergey Petrunia (sergefp)]

Also, it seems essential that table t2 is a constant table. If one changes it to innodb (1-row innodb tables are not constant), the crash goes away:

MariaDB [j34]> PREPARE prep_stmt FROM 'explain SELECT t1.f6 FROM v2 LEFT JOIN t1 ON v2.f3 WHERE t1.f1 != 0';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [j34]> EXECUTE prep_stmt;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

MariaDB [j34]> EXECUTE prep_stmt;
ERROR 2013 (HY000): Lost connection to MySQL server during query
...

unknown [j34]> alter table t2 engine=innodb;
No connection. Trying to reconnect...
Connection id: 1
Current database: j34

Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [j34]> PREPARE prep_stmt FROM 'explain SELECT t1.f6 FROM v2 LEFT JOIN t1 ON v2.f3 WHERE t1.f1 != 0';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

MariaDB [j34]> EXECUTE prep_stmt;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

MariaDB [j34]> EXECUTE prep_stmt;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.01 sec)