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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1,5.5,5.6 OS:Any
Assigned to: Guilhem Bichot CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R3 (Medium) / E2 (Low)

[31 Jan 2011 16:14] Guilhem Bichot
Description:
I'm using 5.1 revision-id:dao-gang.qu@sun.com-20101229035257-32f4p072xij1zkmj

Consider
EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a in (SELECT a FROM t2 WHERE b = 1);
show warnings;
EXPLAIN EXTENDED SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a in (SELECT a FROM t2 WHERE b = 1);
show warnings;
The second query is like the first except that I added a second SELECT with UNION (ORDER BY is a global clause applied after the UNION).

The first EXPLAIN EXTENDED shows the IN->EXISTS subquery transformation:

| Note  | 1003 | 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`)))) |

which is correct.
But the second does not show the transformation:

| Note  | 1003 | 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)) |

which is wrong, because the transformation is actually performed when one executes SELECT (without EXPLAIN EXTENDED), as I see in the --debug trace (look for "<in_optimizer>" there).
So EXPLAIN EXTENDED does not show what SELECT actually does, it's a bug.

Analysis of this bug follows.
Statements like
EXPLAIN EXTENDED
SELECT * FROM t1 UNION SELECT * FROM t1
  ORDER BY (SELECT a FROM t2 WHERE b = 12);
used to crash: BUG#49734; the crash happened when the optimizer tried subquery transformations on the subquery. This crash was fixed in MySQL 5.1 by simply disabling subquery transformations when in EXPLAIN.
The consequence is that those transformations are not visible in EXPLAIN though they are done at execution.

How to repeat:
CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a));
INSERT INTO t1 VALUES (1),(2);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (1),(2);
EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a in (SELECT a FROM t2 WHERE b = 1);
# shows IN->EXISTS
show warnings;
EXPLAIN EXTENDED SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a in (SELECT a FROM t2 WHERE b = 1);
# does not
show warnings;
DROP TABLE t1,t2;

Suggested fix:
The fix of BUG#49734 should be partially undone (only item "2." of its commit comment). Subquery transformations are then done again, so the crash comes back:

Program received signal SIGSEGV, Segmentation fault.
0x00000000004963f4 in Item_singlerow_subselect::select_transformer (
    this=0xee3bd0, join=0xef5098) at item_subselect.cc:440
440       Query_arena *arena= thd->stmt_arena;
(gdb) p thd
$1 = (struct THD *) 0x0
(gdb) bt
#0  0x00000000004963f4 in Item_singlerow_subselect::select_transformer (
    this=0xee3bd0, join=0xef5098) at item_subselect.cc:440
#1  0x000000000056d1fd in JOIN::prepare (this=0xef5098, 
    rref_pointer_array=0xee2f50, tables_init=0xee3598, wild_num=0, 
    conds_init=0xee3a58, og_num=0, order_init=0x0, group_init=0x0, 
    having_init=0x0, proc_param_init=0x0, select_lex_arg=0xee2d80, 
    unit_arg=0xee3038) at sql_select.cc:549
#2  0x00000000005746cd in mysql_select (thd=0xe836f8, 
    rref_pointer_array=0xee2f50, tables=0xee3598, wild_num=0, fields=..., 
    conds=0xee3a58, og_num=0, order=0x0, group=0x0, having=0x0, 
    proc_param=0x0, select_options=2147764740, result=0xeef098, unit=0xee3038, 
    select_lex=0xee2d80) at sql_select.cc:2524
#3  0x000000000059ac06 in mysql_explain_union (thd=0xe836f8, unit=0xee3038, 
    result=0xeef098) at sql_select.cc:16924
#4  0x000000000059a7e2 in select_describe (join=0xef1228, 
    need_tmp_table=false, need_order=false, distinct=false, message=0x0)
    at sql_select.cc:16865
#5  0x000000000057231f in JOIN::exec (this=0xef1228) at sql_select.cc:1892
#6  0x00000000006c1467 in st_select_lex_unit::exec (this=0xe851e0)
    at sql_union.cc:502
#7  0x000000000059aacc in mysql_explain_union (thd=0xe836f8, unit=0xe851e0, 
    result=0xeef098) at sql_select.cc:16906
#8  0x0000000000507dc4 in execute_sqlcom_select (thd=0xe836f8, 
---Type <return> to continue, or q <return> to quit---
    all_tables=0xee2050) at sql_parse.cc:5136
#9  0x00000000004fefbd in mysql_execute_command (thd=0xe836f8)
    at sql_parse.cc:2293
#10 0x000000000050a473 in mysql_parse (thd=0xe836f8, 
    rawbuf=0xee1de8 "EXPLAIN EXTENDED\nSELECT * FROM t1 UNION SELECT * FROM t1\nORDER BY (SELECT a FROM t2 WHERE b = 12)", length=97, 

This crash is due to a wrong Item_singlerow_subselect::thd pointer (0x0). The new fix is then to realize that this pointer is redundant and a correct THD pointer is already available from other sources and can be used instead.
[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 10:24] Roy Lyseng
Bug#57986 and bug#58970 could be related to this problem.
[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.