Bug #50257 Missing info in REF column of the EXPLAIN lines for subselects
Submitted: 11 Jan 2010 22:39 Modified: 13 Aug 2010 2:42
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1, 5.0, 5.5.99 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[11 Jan 2010 22:39] Igor Babaev
Description:
In the following output of ab EXPLAIN command the field of the REF column in the second line is blank:

mysql> explain select * from (select * from t1 where a=7) t;
+----+-------------+------------+--------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |             |
|  2 | DERIVED     | t1         | ref    | a             | a    | 5       |      |    1 | Using where |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------------+

I would expect in that field the same contents as for this command:

mysql> explain select * from t1 where a=7;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | a             | a    | 5       | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

How to repeat:
Populate the table t1 with the following commands:

create table t1 (a int, b int, index (a));
insert into t1 values (3, 10), (2, 20), (7, 10), (5, 20);

Run:
explain select * from (select * from t1 where a=7) t;
[11 Jan 2010 23:09] Sveta Smirnova
Thank you for the report.

Verified as described.
[23 Jan 2010 17:54] Sinisa Milivojevic
This is a patch, against 5.1, that effectively solves the problem and fixes this bug:

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc   2009-06-15 15:57:06 +0000
+++ sql/sql_select.cc   2010-01-22 17:30:27 +0000
@@ -5596,7 +5596,7 @@
         j->ref.null_rejecting |= 1 << i;
       keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
       if (!keyuse->used_tables &&
-         !(join->select_options & SELECT_DESCRIBE))
+         (!(join->select_options & SELECT_DESCRIBE) && !(join->select_lex->uncacheable & UNCACHEABLE_EXPLAIN)))
       {                                        // Compare against constant
        store_key_item tmp(thd, keyinfo->key_part[i].field,
                            key_buff + maybe_null,

All available tests pass.
There is a pair of braces in the patch that are superfluous, but are there to underline the logic.
[23 Jan 2010 17:57] Sinisa Milivojevic
Igor, my dear friend,

it takes about 30 minutes to fix this bug, once I re-learned the piece of code that I previously knew by heart.

And, as it takes more time to report then to fix this bug, I wonder why haven't you done it, as this is an easy bug to fix.

Best wishes ... ;-)
[23 Jan 2010 21:34] Igor Babaev
Sinisa,

I'm impressed with your one-liner patch. After having applied it the query I submitted works fine:

mysql> explain select * from (select * from t1 where a=7) t;
+----+-------------+------------+--------+---------------+------+---------+-------+------+-------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+------------+--------+---------------+------+---------+-------+------+-------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL  |    1 |             |
|  2 | DERIVED     | t1         | ref    | a             | a    | 5       | const |    1 | Using where |
+----+-------------+------------+--------+---------------+------+---------+-------+------+-------------+

Now I have only one problem: EXPLAIN for this query with a subselect in the where clause doesn't work as expected:

mysql> explain select * from t1 where exists (select * from t1 where a=7);
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------+
|  1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |    4 |                          |
|  2 | SUBQUERY    | t1    | ref  | a             | a    | 5       |      |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------+

Formally speaking it's a little bit different problem. Nevertheless IMHO it has the same roots as the first one.
So, rather than submitting a new bug I would prefer correcting the synopsis of this bug for: "Missing info in REF column of the EXPLAIN lines for subselects".

Sinisa, would you, please, find another 30 mins to fix the problem in its entirety?

Regards,
Igor.
[23 Jan 2010 21:36] Igor Babaev
Changed the synopsis of the bug
[23 Jan 2010 23:04] Sinisa Milivojevic
Igor, 

Definitely, only not today.
[27 Jan 2010 19:31] Sinisa Milivojevic
Igor,

I finally found free 30 minutes to make a patch. It is small and nifty, but I got a mismatch in the test suite. This one:

CURRENT_TEST: main.subselect
--- /Users/sinisa/razno/work/mysql-5.1/mysql-test/r/subselect.result    2009-06-23 01:43:15.000000000 +0300
+++ /Users/sinisa/razno/work/mysql-5.1/mysql-test/r/subselect.reject    2010-01-27 17:27:43.000000000 +0300
@@ -363,9 +363,9 @@
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id     select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
 1      PRIMARY t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
-4      SUBQUERY        t8      const   PRIMARY PRIMARY 37              1       100.00  Using index
+4      SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 2      SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00
-3      SUBQUERY        t8      const   PRIMARY PRIMARY 37              1       100.00  Using index
+3      SUBQUERY        t8      const   PRIMARY PRIMARY 37      const   1       100.00  Using index
 Warnings:
 Note   1003    select 'joce' AS `pseudo`,(select 'test' AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM

Do you think that previous result is correct or my current result ???

To me, it looks like that my result is correct one, but wanted to check.

Thanks in advance ... ;o)
[28 Jan 2010 2:18] Igor Babaev
Sinisa,

Without the patch itself I can't say whether it's right or not.
The new results are ok, but they are not enough to make a judgment.

Regards,
Igor.
[28 Jan 2010 16:30] Sinisa Milivojevic
Igor,

Here is a new patch. It is not one liner, but actually it is just a small re-arrangements of the existing code:

=== modified file 'sql/item_subselect.cc'
--- sql/item_subselect.cc       2009-06-09 16:44:26 +0000
+++ sql/item_subselect.cc       2010-01-27 13:55:22 +0000
@@ -1877,6 +1877,12 @@
 
     optimized= 1;
     unit->set_limit(unit->global_parameters);
+    if (!select_lex->uncacheable && thd->lex->describe && 
+        !(join->select_options & SELECT_DESCRIBE) && item->const_item())
+    {
+      select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
+      select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
+    }
     if (join->optimize())
     {
       thd->where= save_where;
@@ -1884,9 +1890,8 @@
       thd->lex->current_select= save_select;
       DBUG_RETURN(join->error ? join->error : 1);
     }
-    if (!select_lex->uncacheable && thd->lex->describe && 
-        !(join->select_options & SELECT_DESCRIBE) && 
-        join->need_tmp && item->const_item())
+    if ((select_lex->uncacheable & UNCACHEABLE_EXPLAIN)  && 
+        join->need_tmp)
     {
       /*
         Force join->join_tmp creation, because this subquery will be replaced
@@ -1894,8 +1899,6 @@
         called by EXPLAIN and we need to preserve the initial query structure
         so we can display it.
        */
-      select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
-      select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
       if (join->init_save_join_tab())
         DBUG_RETURN(1);                        /* purecov: inspected */
     }
[28 Jan 2010 18:20] Igor Babaev
Sinisa,

It's pretty hard to talk with you through the bug service.
Definitely I would not approve your patch.

Regards,
Igor.
[4 Mar 2010 13:48] 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/102320

3127 Jorgen Loland	2010-03-04
      Bug#50257 "Missing info in REF column of the EXPLAIN 
                 lines for subselects"
      
      When executing an EXPLAIN query, we need to store the ref_key
      chosen for a table so we can print it's information. This is 
      done in in create_ref_for_key() if called from 
      select_describe(). 
      
      The bug is that in the case of subselects, create_ref_from_key() 
      is called from JOIN::optimize() of the parent select. This 
      happens before select_describe() (part of JOIN::exec()). The 
      ref_key is therefore not saved. The fix is to make 
      create_ref_for_key() store the key if executing an EXPLAIN 
      query instead of checking if called from select_describe() 
      (which is only a part of EXPLAIN execution).
     @ mysql-test/r/subselect.result
        Updated result file with missing info in ref column of EXPLAIN after fixing BUG#50257
     @ mysql-test/r/subselect4.result
        Added test for BUG#50257
     @ mysql-test/t/subselect4.test
        Added test for BUG#50257
     @ sql/sql_lex.cc
        Initialize lex->describe to DESCRIBE_NONE instead of 0.
     @ sql/sql_lex.h
        Added #define DESCRIBE_NONE, a lex->describe type indicating that the query is not an EXPLAIN.
     @ sql/sql_select.cc
        create_ref_for_key() must save ref_key in all cases when 
        executing an EXPLAIN, not only if called from select_describe()
[5 Mar 2010 13:30] Jørgen Løland
Sinisa, 

AFAICT, the submitted patch takes care of both bugs. join->select_lex->uncacheable
will never have the UNCACHEABLE_EXPLAIN bit set unless thd->lex->describe != 0.
[5 Mar 2010 15:45] Sinisa Milivojevic
Jorgen,

That is fine, just include both test cases in the patch.
[9 Mar 2010 12:13] 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/102692

3119 Jorgen Loland	2010-03-09
      Bug#50257 "Missing info in REF column of the EXPLAIN 
                 lines for subselects"
      
      In create_ref_for_key(): When executing a query where a key
      lookup is compared to a constant value, the value can be stored
      in ref.key_buff of the join_tab. The store_key object created for
      this key will not be used after this, so the object will not be
      pointed to by the join_tab.
      
      If the store_key object needs to be referenced later, the object
      is pointed to by ref.key_copy of the join_tab instead of just
      storing the value in ref.key_buff. Key lookups of non-constant
      values and EXPLAIN queries both use this object.
      
      The bug was that for EXPLAIN queries with constant key lookup in
      subqueries, it was not detected that the query was an EXPLAIN.
      The reason was that it was checked whether the call stack came
      from select_describe() ("join->select_options &
      SELECT_DESCRIBE"). This check is correct for queries that are not
      nested, but for subselects create_ref_for_key() may be called as
      part of JOIN::optimize() of the parent select. The fix is to
      check for "thd->lex->describe" instead of "join->select_options".
      This variable will have a non-zero value if and only if we're
      executing an EXPLAIN query.
     @ mysql-test/r/subselect.result
        Updated result file with missing info in ref column of EXPLAIN after fixing BUG#50257
     @ mysql-test/r/subselect4.result
        Added test for BUG#50257
     @ mysql-test/t/subselect4.test
        Added test for BUG#50257
     @ sql/sql_lex.cc
        Initialize lex->describe to DESCRIBE_NONE instead of 0.
     @ sql/sql_lex.h
        Added #define DESCRIBE_NONE, a lex->describe type indicating that the query is not an EXPLAIN.
     @ sql/sql_select.cc
        create_ref_for_key() must detect that the query is an EXPLAIN and point ref->key_copy to the store_key object also in the case of constant lookups in subselects.
[11 Mar 2010 10:29] 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/102973

3125 Jorgen Loland	2010-03-11
      Bug#50257 "Missing info in REF column of the EXPLAIN 
                 lines for subselects"
      
      In create_ref_for_key(): When executing a query where a key
      lookup is compared to a constant value, the value can be stored
      in ref.key_buff of the join_tab. The store_key object created for
      this key will not be used after this, so the object will not be
      pointed to by the join_tab.
      
      If the store_key object needs to be referenced later, the object
      is pointed to by ref.key_copy of the join_tab instead of just
      storing the value in ref.key_buff. Key lookups of non-constant
      values and EXPLAIN queries both use this object.
      
      The bug was that for EXPLAIN queries with constant key lookup in
      subqueries, it was not detected that the query was an EXPLAIN.
      The reason was that it was checked whether the call stack came
      from select_describe() ("join->select_options &
      SELECT_DESCRIBE"). This check is correct for queries that are not
      nested, but for subselects create_ref_for_key() may be called as
      part of JOIN::optimize() of the parent select. The fix is to
      check for "thd->lex->describe" instead of "join->select_options".
      This variable will have a non-zero value if and only if we're
      executing an EXPLAIN query.
     @ mysql-test/r/subselect.result
        Updated result file with missing info in ref column of EXPLAIN after fixing BUG#50257
     @ mysql-test/r/subselect4.result
        Added test for BUG#50257
     @ mysql-test/t/subselect4.test
        Added test for BUG#50257
     @ sql/sql_lex.cc
        Initialize lex->describe to DESCRIBE_NONE instead of 0.
     @ sql/sql_lex.h
        Added #define DESCRIBE_NONE, a lex->describe type indicating that the query is not an EXPLAIN.
     @ sql/sql_select.cc
        create_ref_for_key() must detect that the query is an EXPLAIN and point ref->key_copy to the store_key object also in the case of constant lookups in subselects.
[11 Mar 2010 10:44] 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/102976

3813 Jorgen Loland	2010-03-11 [merge]
      * Merge BUG#50257 from next-mr-bugfixing to 
        6.0-codebase-bugfixing.
      * Updated subselect_no_{mat,opts,semijoin} and myisam_mrr tests
        to reflect new explain output.
[12 Mar 2010 18:10] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100312180926-0emfjrj8e9xnvl8h) (version source revid:alik@sun.com-20100312180447-2r0ak22y13s05134) (merge vers: 6.0.14-alpha) (pib:16)
[15 Mar 2010 13:13] Jørgen Løland
Pushed to next-mr-bugfixing
[24 Mar 2010 8:18] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100324081159-5b8juv8ldiqwce8v) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (pib:16)
[7 Apr 2010 14:21] Tor Didriksen
Seems to have introduced a regression
Bug #52317  	Assertion failing in Field_varstring::store () at field.cc:6833
[4 Aug 2010 8:11] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:26] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100324081105-y72rautcea375zxm) (merge vers: 5.6.99-m4) (pib:18)
[13 Aug 2010 2:42] Paul Dubois
Noted in 5.6.0, 6.0.14 changelogs.

The ref column of EXPLAIN output for subquery lines could be missing
information.