| Bug #59852 | EXPLAIN EXTENDED + UNION + 'ORDER BY x IN (subquery)' hides IN->EXISTS transform | ||
|---|---|---|---|
| Submitted: | 31 Jan 2011 16:14 | Modified: | 29 Jul 2013 18:35 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1,5.5,5.6 | OS: | Any |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[31 Jan 2011 16:14]
Guilhem Bichot
[31 Jan 2011 16:31]
Valeriy Kravchuk
Verified on Mac OS X:
macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.2-m5-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) engine=MyISAM;Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO t1 VALUES (1),(2);Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t2 (b INT);Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO t2 VALUES (1),(2);Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a in (SELECT a FROM t2 WHERE b = 1);
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using filesort |
| 2 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a` AS `a` from `test`.`t1` order by <in_optimizer>(`test`.`t1`.`a`,<exists>(select 1 from `test`.`t2` where ((`test`.`t2`.`b` = 1) and trigcond(((<cache>(`test`.`t1`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))
2 rows in set (0.00 sec)
mysql> EXPLAIN EXTENDED SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a in (SELECT a FROM t2
-> WHERE b = 1);
+----+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
| 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
| 3 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+----+--------------+------------+------+---------------+------+---------+------+------+----------+----------------+
4 rows in set, 2 warnings (0.00 sec)
mysql> show warnings\G*************************** 1. row ***************************
Level: Note
Code: 1276
Message: Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` in (select `test`.`t1`.`a` from `test`.`t2` where (`test`.`t2`.`b` = 1))
2 rows in set (0.00 sec)
[31 Jan 2011 16:39]
Guilhem Bichot
Effort: patch already written, so not much effort left: E2.
Risk: {EXPLAIN + UNION + subquery} follow "complex" code paths, so enabling transformations in EXPLAIN (which is still the right thing to do, and used to be done until BUG#49734 turned them off), is a bit risky: R3.
[31 Jan 2011 21:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/130093 3335 Guilhem Bichot 2011-01-31 Fix for BUG#59852 "EXPLAIN EXTENDED + UNION + 'ORDER BY x IN (subquery)' does not show IN->EXISTS transformation": make EXPLAIN show it, and fix the resulting crash by getting rid of a redundant, bad THD pointer. @ mysql-test/r/union.result result. Without the change to sql_select.cc, the last EXPLAIN would not show the IN->EXISTS transformation (would just show the original query). @ mysql-test/t/union.test test for bug @ sql/item_subselect.cc Item_subselect::thd and subselect_engine::thd are deleted. The first is replaced with the existing Item_subselect::unit::thd. The second one is replaced with the existing subselect_engine::item::unit::thd (same object as for the first one, actually: item points to the containing Item_subselect); for subselect_union_engine it's additionally possible to use subselect_union_engine::unit::thd. Before this change, Item_subselect::thd and subselect_engine::thd were set to NULL in constructors, and Item_subselect::fix_fields() corrected them. As Item_subselect::thd is used in subquery transformations (in Item_singlerow_subselect::select_transformer() called by JOIN::prepare()), it was necessary that Item_subselect::fix_fields() was called before JOIN::prepare() for the subquery's JOIN. And often it works indeed this way, as Item_subselect::fix_fields() calls JOIN::prepare(). But in "EXPLAIN select1 UNION select2 ORDER BY select3" (case of BUG 49734), we have four SELECT_LEX: select1-2-3 and the "fake UNION SELECT". EXPLAIN EXTENDED (mysql_explain_union()) calls JOIN::prepare() for select1, and select2. select3 is not in the ORDER BY clause of select2, so JOIN::prepare(select2) does not call Item_subselect::fix_fields(). Then mysql_explain_union() calls JOIN::exec(select2) which itself calls select_describe(select2). That function calls mysql_explain_union() on "inner units" of select2, and select3 is among them. That is weird, by the way: select3 is not in ORDER BY of select2 (ok) but it's a inner unit of select2 (weird); is this a parsing artifact? Shouldn't it be a inner unit of the fake SELECT? So we go into JOIN::prepare(select3), which calls resolve_subquery() to transform, and crashes on a still-NULL Item_subselect::thd pointer. If the crash is "leaped over" in gdb, then finally JOIN::prepare() is called for the fake UNION SELECT, which owns the ORDER BY clause, so it's only at this late moment, precisely in setup_order(), that Item_subselect::fix_fields() is called which sets Item_subselect::thd correctly. Too late. See also the comment at start of st_select_lex::print() about thd being NULL. The advantage of using SELECT_LEX_UNIT::thd pointers is that they are always kept correct by reinit_stmt_before_use(). The NULL thd pointer is eliminated, we now use a correct one, which avoids the crash. One may wonder whether, even with no crash thanks to this patch, it is still a logical problem to have: - JOIN::prepare() called before Item_subselect::fix_fields(). - select3 attached to select2 as inner unit but attached to fake SELECT as ORDER BY clause. Those oddities are a specificity of EXPLAIN UNION; without a UNION, in "EXPLAIN select 1 ORDER BY select3", select1 owns the ORDER BY clause, so setup_order() is called from JOIN::prepare(select1), which calls Item_subselect::fix_fields(), which calls JOIN::prepare() (JOIN of select3). It would be interesting to discuss this with reviewers. We also add assertions that various pointers-to-THD are equal. @ sql/sql_select.cc don't skip subquery transformations in EXPLAIN, so that the user is informed about them. This was skipped by the second part of the patch for BUG 49734, but it's not needed anymore, because we here fix the bad-THD cause in item_subselect.cc.
[9 Feb 2011 11:00]
Guilhem Bichot
Alas, the tree where I verified the bug contains the two revisions fixing the bugs above.
[29 Jul 2013 18:35]
Paul DuBois
Noted in 5.7.2 changelog. Some subquery transformations were not visible in EXPLAIN output.
