[MDEV-3777] LP:612543 - Crash in Item_field::used_tables() with view + subquery + prepared statements - JIRA

Details

Type: Task
Status: Closed
Priority: Minor
Resolution: Fixed
Fix Version/s: None
Component/s: None
Labels:
- Launchpad
- MariaDB_5.3

Description

When executing the following query as a prepared statement twice, maria 5.3 crashes if certain optimizations are disabled. 5.2 is not affected.

query:

SELECT table1 .`col_int_key`
FROM C table1 JOIN view_B table2 ON table1 .`col_varchar_key`
WHERE table2 .`col_varchar_nokey` IN (
SELECT `col_varchar_key`
FROM C )

explain:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY B system NULL NULL NULL NULL 1 100.00
1 PRIMARY table1 index NULL col_varchar_key 9 NULL 20 100.00 Using where; Using index
2 SUBQUERY C index NULL col_varchar_key 9 NULL 20 100.00 Using index
Warnings:
Note 1003 select `test`.`table1`.`col_int_key` AS `col_int_key` from `test`.`C` `table1` join `test`.`B` where (<in_optimizer>('f','f' in ( <materialize> (select `test`.`C`.`col_varchar_key` from `test`.`C` ), <primary_index_lookup>('f' in <temporary table> on distinct_key where (('f' = `materialized subselect`.`col_varchar_key`))))) and `test`.`table1`.`col_varchar_key`)

backtrace:

#3 0x0827e86a in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x081bdb4e in Item_field::used_tables (this=0xb5e728b0) at item.cc:2265
#6 0x0823402c in Item_in_subselect::update_used_tables (this=0xb5e58358) at item_subselect.cc:2194
#7 0x081e10e7 in Item_func::update_used_tables (this=0xb5e58730) at item_func.cc:384
#8 0x08313ca3 in build_equal_items_for_cond (thd=0x98eb4f8, cond=0xb5e58730, inherited=0xb5e73940) at sql_select.cc:9055
#9 0x08313809 in build_equal_items_for_cond (thd=0x98eb4f8, cond=0xb5e738a8, inherited=0xb5e73940) at sql_select.cc:8973
#10 0x08313d08 in build_equal_items (thd=0x98eb4f8, cond=0xb5e738a8, inherited=0x0, join_list=0xb5e71e08, cond_equal_ref=0xb5e66988) at sql_select.cc:9136
#11 0x08316754 in optimize_cond (join=0xb5e61b78, conds=0xb5e738a8, join_list=0xb5e71e08, cond_value=0xb5e668dc) at sql_select.cc:10403
#12 0x082fce38 in JOIN::optimize (this=0xb5e61b78) at sql_select.cc:802
#13 0x083030a4 in mysql_select (thd=0x98eb4f8, rref_pointer_array=0xb5e71e50, tables=0xb5e72628, wild_num=0, fields=..., conds=0xb5e738a8, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0xb5e584c0, unit=0xb5e71a94, select_lex=0xb5e71d34)
at sql_select.cc:2542
#14 0x082fb71b in handle_select (thd=0x98eb4f8, lex=0xb5e71a38, result=0xb5e584c0, setup_tables_done_option=0) at sql_select.cc:276
#15 0x0829a164 in execute_sqlcom_select (thd=0x98eb4f8, all_tables=0xb5e72628) at sql_parse.cc:5081
#16 0x08290b3c in mysql_execute_command (thd=0x98eb4f8) at sql_parse.cc:2265
#17 0x0834247f in Prepared_statement::execute (this=0xb5e73508, expanded_query=0xb619f410, open_cursor=false) at sql_prepare.cc:3588
#18 0x0834197e in Prepared_statement::execute_loop (this=0xb5e73508, expanded_query=0xb619f410, open_cursor=false, packet=0x0, packet_end=0x0)
at sql_prepare.cc:3263
#19 0x0834036a in mysql_sql_stmt_execute (thd=0x98eb4f8) at sql_prepare.cc:2533
#20 0x08290b66 in mysql_execute_command (thd=0x98eb4f8) at sql_parse.cc:2274
#21 0x0829c325 in mysql_parse (thd=0x98eb4f8, inBuf=0xb5e58978 "EXECUTE st1", length=11, found_semicolon=0xb61a0230) at sql_parse.cc:6027
#22 0x0828e5f2 in dispatch_command (command=COM_QUERY, thd=0x98eb4f8, packet=0x98ed519 "EXECUTE st1", packet_length=11) at sql_parse.cc:1184
#23 0x0828dae0 in do_command (thd=0x98eb4f8) at sql_parse.cc:890
#24 0x0828ac78 in handle_one_connection (arg=0x98eb4f8) at sql_connect.cc:1153
#25 0x00a08919 in start_thread () from /lib/libpthread.so.0
#26 0x00951e5e in clone () from /lib/libc.so.6

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

LPexportBug612543.xml
14 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Philip Stoev added a comment - 2010-08-02 13:18

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
SET SESSION optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off,subquery_cache=off';

CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_date_nokey` date DEFAULT NULL,
`col_time_key` time DEFAULT NULL,
`col_time_nokey` time DEFAULT NULL,
`col_datetime_key` datetime DEFAULT NULL,
`col_datetime_nokey` datetime 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_date_key` (`col_date_key`),
KEY `col_time_key` (`col_time_key`),
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `B` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','f','f');

CREATE VIEW `view_B` AS select * FROM B;

CREATE TABLE `C` (
`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 `C` VALUES (2,'w','w');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (3,'m','m');
INSERT INTO `C` VALUES (9,'k','k');
INSERT INTO `C` VALUES (NULL,'r','r');
INSERT INTO `C` VALUES (9,'t','t');
INSERT INTO `C` VALUES (3,'j','j');
INSERT INTO `C` VALUES (8,'u','u');
INSERT INTO `C` VALUES (8,'h','h');
INSERT INTO `C` VALUES (53,'o','o');
INSERT INTO `C` VALUES (0,NULL,NULL);
INSERT INTO `C` VALUES (5,'k','k');
INSERT INTO `C` VALUES (166,'e','e');
INSERT INTO `C` VALUES (3,'n','n');
INSERT INTO `C` VALUES (0,'t','t');
INSERT INTO `C` VALUES (1,'c','c');
INSERT INTO `C` VALUES (9,'m','m');
INSERT INTO `C` VALUES (5,'y','y');
INSERT INTO `C` VALUES (6,'f','f');
INSERT INTO `C` VALUES (2,'d','d');

PREPARE st1 FROM "SELECT table1 .`col_int_key`
FROM C table1 JOIN view_B table2 ON table1 .`col_varchar_key`
WHERE table2 .`col_varchar_nokey` IN (
SELECT `col_varchar_key`
FROM C ) ";

EXECUTE st1;
EXECUTE st1;

Show

Philip Stoev added a comment - 2010-08-02 13:18 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements SET SESSION optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off,subquery_cache=off'; CREATE TABLE `B` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `col_int_nokey` int(11) DEFAULT NULL, `col_int_key` int(11) DEFAULT NULL, `col_date_key` date DEFAULT NULL, `col_date_nokey` date DEFAULT NULL, `col_time_key` time DEFAULT NULL, `col_time_nokey` time DEFAULT NULL, `col_datetime_key` datetime DEFAULT NULL, `col_datetime_nokey` datetime 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_date_key` (`col_date_key`), KEY `col_time_key` (`col_time_key`), KEY `col_datetime_key` (`col_datetime_key`), KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; INSERT INTO `B` VALUES (1,1,7,'1900-01-01','1900-01-01','01:13:38','01:13:38','2005-02-05 00:00:00','2005-02-05 00:00:00','f','f'); CREATE VIEW `view_B` AS select * FROM B; CREATE TABLE `C` ( `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 `C` VALUES (2,'w','w'); INSERT INTO `C` VALUES (9,'m','m'); INSERT INTO `C` VALUES (3,'m','m'); INSERT INTO `C` VALUES (9,'k','k'); INSERT INTO `C` VALUES (NULL,'r','r'); INSERT INTO `C` VALUES (9,'t','t'); INSERT INTO `C` VALUES (3,'j','j'); INSERT INTO `C` VALUES (8,'u','u'); INSERT INTO `C` VALUES (8,'h','h'); INSERT INTO `C` VALUES (53,'o','o'); INSERT INTO `C` VALUES (0,NULL,NULL); INSERT INTO `C` VALUES (5,'k','k'); INSERT INTO `C` VALUES (166,'e','e'); INSERT INTO `C` VALUES (3,'n','n'); INSERT INTO `C` VALUES (0,'t','t'); INSERT INTO `C` VALUES (1,'c','c'); INSERT INTO `C` VALUES (9,'m','m'); INSERT INTO `C` VALUES (5,'y','y'); INSERT INTO `C` VALUES (6,'f','f'); INSERT INTO `C` VALUES (2,'d','d'); PREPARE st1 FROM "SELECT table1 .`col_int_key` FROM C table1 JOIN view_B table2 ON table1 .`col_varchar_key` WHERE table2 .`col_varchar_nokey` IN ( SELECT `col_varchar_key` FROM C ) "; EXECUTE st1; EXECUTE st1;

Hide

Permalink

Philip Stoev added a comment - 2011-02-26 10:02

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Repeatable with maria-5.3 . Not repeatable with maria-5.3-mwl89

simplified test case:

SET SESSION optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off,subquery_cache=off';

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

CREATE TABLE t2 ( f3 varchar(1));

CREATE VIEW v2 AS SELECT * FROM t2;

PREPARE st1 FROM "SELECT *
FROM t1 JOIN v2 ON t1.f2
WHERE v2.f3 IN (
SELECT f2
FROM t1 ) ";

EXECUTE st1;
EXECUTE st1;

Show

Philip Stoev added a comment - 2011-02-26 10:02 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements Repeatable with maria-5.3 . Not repeatable with maria-5.3-mwl89 simplified test case: SET SESSION optimizer_switch = 'partial_match_rowid_merge=off,partial_match_table_scan=off,semijoin=off,subquery_cache=off'; CREATE TABLE t1 ( f1 int(11), f2 varchar(1)); CREATE TABLE t2 ( f3 varchar(1)); CREATE VIEW v2 AS SELECT * FROM t2; PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 WHERE v2.f3 IN ( SELECT f2 FROM t1 ) "; EXECUTE st1; EXECUTE st1;

Hide

Permalink

Igor Babaev added a comment - 2011-03-04 06:01

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
If one adds rows into the tables:

insert into t1 values (2,'x'), (5,'y');
insert into t2 values ('x'), ('z');

one still have a crash on the second execution of the prepared statement:

MariaDB [test]> PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [test]> EXECUTE st1;
--------------

--------------

2	x	x
5	y	x

--------------
2 rows in set (0.01 sec)

MariaDB [test]> EXECUTE st1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Show

Igor Babaev added a comment - 2011-03-04 06:01 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements If one adds rows into the tables: insert into t1 values (2,'x'), (5,'y'); insert into t2 values ('x'), ('z'); one still have a crash on the second execution of the prepared statement: MariaDB [test] > PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; Query OK, 0 rows affected (0.00 sec) Statement prepared MariaDB [test] > EXECUTE st1; ----- ---- ----- f1 f2 f3 ----- ---- ----- 2 x x 5 y x ----- ---- ----- 2 rows in set (0.01 sec) MariaDB [test] > EXECUTE st1; ERROR 2013 (HY000): Lost connection to MySQL server during query

Hide

Permalink

Timour Katchaounov added a comment - 2011-03-08 15:51

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Not repeatable in 5.3-mwl89.

The most likely reason there is no crash in mwl89 is that in mwl89
all materialization-related transformations/initializations are
repeated for each re-execution (and are not stored in PS memory).

(see LP BUG#611396 - both bugs exposes a general problem with
PS+views+materialization)

Show

Timour Katchaounov added a comment - 2011-03-08 15:51 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements Not repeatable in 5.3-mwl89. The most likely reason there is no crash in mwl89 is that in mwl89 all materialization-related transformations/initializations are repeated for each re-execution (and are not stored in PS memory). (see LP BUG#611396 - both bugs exposes a general problem with PS+views+materialization)

Hide

Permalink

Timour Katchaounov added a comment - 2011-03-08 19:55

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Test case for 5.3-mwl#89 for both non-semijoin
subquery strategies:
---------------------------------------------------------------

CREATE TABLE t1 ( f1 int(11), f2 varchar(1));
CREATE TABLE t2 ( f3 varchar(1));
insert into t1 values (2,'x'), (5,'y');
insert into t2 values ('x'), ('z');
CREATE VIEW v2 AS SELECT * FROM t2;

set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
EXECUTE st1;
EXECUTE st1;

set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
PREPARE st2 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
EXECUTE st2;
EXECUTE st2;

set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off';
PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )";
EXECUTE st3;
EXECUTE st3;

Show

Timour Katchaounov added a comment - 2011-03-08 19:55 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements Test case for 5.3-mwl#89 for both non-semijoin subquery strategies: --------------------------------------------------------------- CREATE TABLE t1 ( f1 int(11), f2 varchar(1)); CREATE TABLE t2 ( f3 varchar(1)); insert into t1 values (2,'x'), (5,'y'); insert into t2 values ('x'), ('z'); CREATE VIEW v2 AS SELECT * FROM t2; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; PREPARE st1 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; EXECUTE st1; EXECUTE st1; set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; PREPARE st2 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; EXECUTE st2; EXECUTE st2; set @@optimizer_switch='materialization=on,in_to_exists=on,semijoin=off'; PREPARE st3 FROM "SELECT * FROM t1 JOIN v2 ON t1.f2 > 'a' WHERE v2.f3 IN ( SELECT f2 FROM t1 )"; EXECUTE st3; EXECUTE st3;

Hide

Permalink

Timour Katchaounov added a comment - 2011-07-07 14:23

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
Test case pushed to 5.3.

Show

Timour Katchaounov added a comment - 2011-07-07 14:23 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements Test case pushed to 5.3.

Hide

Permalink

Timour Katchaounov added a comment - 2011-07-07 14:23

Re: Crash in Item_field::used_tables() with view + subquery + prepared statements
The bug itself has been fixed by MWL#89.

Show

Timour Katchaounov added a comment - 2011-07-07 14:23 Re: Crash in Item_field::used_tables() with view + subquery + prepared statements The bug itself has been fixed by MWL#89.

Hide

Permalink

Rasmus Johansson added a comment - 2011-12-13 15:24

Launchpad bug id: 612543

Show

Rasmus Johansson added a comment - 2011-12-13 15:24 Launchpad bug id: 612543

People

Assignee:

Timour Katchaounov

Reporter:

Philip Stoev

Votes:

0 Vote for this issue

Watchers:

0 Start watching this issue

Dates

Created:

2010-08-02 13:16

Updated:

2012-10-04 13:26

Resolved:

2012-10-04 12:53

Agile

View on Board