Details
Major
Description
There are two queries that are different only in that one of them accesses a table directly, while the other reads it from a "trivial" derived table, ie. through a "(SELECT * FROM t2) as alias" construct. EXPLAINs should be the same, but they are different:
CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (4),(6),(3),(5),(3),(246),(2),(9),(3),(8), (1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1),(7),(4),(7),(0),(2),(9),(4),(0),(9), (1),(3),(8),(8),(18),(84),(6),(3),(6),(6); explain extended SELECT * FROM t1, t2 AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where | | 1 | PRIMARY | alias | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where | | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | | | 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table
The query with FROM subquery hits this stack trace:
#0 Item_in_subselect::create_in_to_exists_cond (this=0xa2249e0, join_arg=0xa228518) at /home/psergey/dev2/5.5-show-explain-r21/sql/item_subselect.cc:2291
#1 0x08370d97 in JOIN::choose_subquery_plan (this=0xa228518, join_tables=3) at /home/psergey/dev2/5.5-show-explain-r21/sql/opt_subselect.cc:5237
#2 0x0828c746 in make_join_statistics (join=0xa228518, tables_list=..., conds=0xa229c60, keyuse_array=0xa228700) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:3756
#3 0x0828dd69 in JOIN::optimize_inner (this=0xa228518) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:1248
#4 0x0828f9d0 in JOIN::optimize (this=0xa228518) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:979
#5 0x08220b6f in st_select_lex::optimize_unflattened_subqueries (this=0xa20cfcc) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_lex.cc:3448
#6 0x08372e26 in JOIN::optimize_unflattened_subqueries (this=0xa225140) at /home/psergey/dev2/5.5-show-explain-r21/sql/opt_subselect.cc:4878
#7 0x0828f397 in JOIN::optimize_inner (this=0xa225140) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:1648
#8 0x0828f9d0 in JOIN::optimize (this=0xa225140) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:979
#9 0x08293619 in mysql_select (thd=0xa20b530, rref_pointer_array=0xa20d110, tables=0xa216eb8, wild_num=1, fields=..., conds=0xa224b10, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0xa224c18, unit=0xa20cb1c, select_lex=0xa20cfcc) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:3075
#10 0x08293a15 in mysql_explain_union (thd=0xa20b530, unit=0xa20cb1c, result=0xa224c18) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_select.cc:21996
#11 0x0822f493 in execute_sqlcom_select (thd=0xa20b530, all_tables=0xa216eb8) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_parse.cc:4614
#12 0x08231881 in mysql_execute_command (thd=0xa20b530) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_parse.cc:2185
#13 0x082398bd in mysql_parse (thd=0xa20b530, rawbuf=0xa216cb0 "explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )", length=115, parser_state=0x99267d70) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_parse.cc:5758
#14 0x0823a570 in dispatch_command (command=COM_QUERY, thd=0xa20b530, packet=0xa20d9e9 "", packet_length=115) at /home/psergey/dev2/5.5-show-explain-r21/sql/sql_parse.cc:1056
The query without FROM subquery doesn't.
Re: SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table
The difference comes from Item_allany_subselect::is_maxmin_applicable(). There, we have:
=== without FROM ===
(gdb) print abort_on_null
$33 = false
(gdb) print upper_item
$34 = (Item_func_not_all *) 0xa121600
(gdb) p upper_item->is_top_level_item()
$35 = true
(gdb) p join->select_lex->master_unit()->uncacheable
$36 = 0 '\000'
(gdb) p func->eqne_op()
$37 = false
=== with FROM ===
(gdb) print abort_on_null
$73 = false
(gdb) print upper_item
$74 = (Item_func_not_all *) 0xa247100
(gdb) p upper_item->is_top_level_item()
$75 = true
(gdb) p join->select_lex->master_unit()->uncacheable
$76 = 8 '\b'
(gdb) p func->eqne_op()
$77 = false
Re: SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table
That is, the difference is in join->select_lex->master_unit()->uncacheable, and it is 0 vs 8, UNCACHEABLE_EXPLAIN
Re: SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table
This patch seems to fix it:
=== modified file 'sql/item_subselect.cc'
— sql/item_subselect.cc 2012-06-07 22:19:36 +0000
+++ sql/item_subselect.cc 2012-06-14 22:02:49 +0000
@@ -1804,7 +1804,7 @@ bool Item_allany_subselect::is_maxmin_ap
WHERE condition.
*/
return (abort_on_null || (upper_item && upper_item->is_top_level_item())) &&
- !join->select_lex->master_unit()
>uncacheable && !func>eqne_op();
+ !(join->select_lex->master_unit()>uncacheable & ~UNCACHEABLE_EXPLAIN) && !func>eqne_op();
}
Launchpad bug id: 1013343
Fixed in MariaDB 10.0 (the fix pushed as part of SHOW EXPLAIN code).
Re: SUBQUERY changes to DEPENDENT SUBQUERY when using a trivial derived table
The problem is repeatable on 5.3 and 5.5 (earlier versions are not applicable because they don't support derived_merge)