MariaDB

Crash in make_join_select on second execution of prepared statement with view

Bug #777809 reported by Philip Stoev
18
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Critical
Oleksandr "Sanja" Byelkin
MariaDB 5.1

Bug Description

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)
Changed in maria:
milestone: none → 5.2
Revision history for this message
Philip Stoev (pstoev-askmonty) wrote :

Not repeatable with: mysql 5.1, mysql 5.5

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

Sergey Petrunia (sergefp)
Changed in maria:
assignee: nobody → Sergey Petrunia (sergefp)
Revision history for this message
Sergey Petrunia (sergefp) wrote :

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.

Revision history for this message
Sergey Petrunia (sergefp) wrote :

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)

Igor Babaev (igorb-seattle)
Changed in maria:
status: New → Confirmed
importance: Undecided → Critical
Oleksandr "Sanja" Byelkin (sanja-byelkin)
Changed in maria:
assignee: Sergey Petrunia (sergefp) → Oleksandr "Sanja" Byelkin (sanja-byelkin)
Oleksandr "Sanja" Byelkin (sanja-byelkin)
Changed in maria:
status: Confirmed → In Progress
Oleksandr "Sanja" Byelkin (sanja-byelkin)
Changed in maria:
milestone: 5.2 → 5.1
status: In Progress → Fix Committed
Oleksandr "Sanja" Byelkin (sanja-byelkin)
Changed in maria:
status: Fix Committed → Fix Released
status: Fix Released → Fix Committed
Daniel Bartholomew (dbart)
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.