| 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: | |
| 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 |
[11 Jan 2010 22:39]
Igor Babaev
[11 Jan 2010 23:09]
Sveta Smirnova
Thank you for the report. Verified as described.
[23 Jan 2010 17:54]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
Igor, Definitely, only not today.
[27 Jan 2010 19:31]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
