[MDEV-3464] LP:777809 - Crash in make_join_select on second execution of prepared statement with view Created: 2011-05-05  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug777809.xml    

 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;

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-05-06 ]

Re: Crash in make_join_select on second execution of prepared statement with view
Not repeatable with: mysql 5.1, mysql 5.5

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

Comment by Sergei Petrunia [ 2011-07-19 ]

Re: Crash in make_join_select on second execution of prepared statement with view
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.

Comment by Sergei Petrunia [ 2011-07-19 ]

Re: Crash in make_join_select on second execution of prepared statement with view
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)

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 777809

Generated at Thu Feb 08 06:48:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.