Looks good. Approved.
--
Øystein
Jorgen Loland wrote:
> #At file:///localhome/jl208045/mysql/mysql-next-mr-bugfixing/ based on
> revid:vvaintroub@stripped
>
> 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.
>
> modified:
> mysql-test/r/subselect.result
> mysql-test/r/subselect4.result
> mysql-test/t/subselect4.test
> sql/sql_lex.cc
> sql/sql_lex.h
> sql/sql_select.cc
> === modified file 'mysql-test/r/subselect.result'
> --- a/mysql-test/r/subselect.result 2010-03-01 09:45:36 +0000
> +++ b/mysql-test/r/subselect.result 2010-03-09 12:13:25 +0000
> @@ -363,9 +363,9 @@ INSERT INTO t8 (pseudo,email) VALUES ('2
> 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
>
> === modified file 'mysql-test/r/subselect4.result'
> --- a/mysql-test/r/subselect4.result 2009-09-28 13:48:40 +0000
> +++ b/mysql-test/r/subselect4.result 2010-03-09 12:13:25 +0000
> @@ -59,3 +59,22 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
> (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
> DROP TABLE t1,t2,t3;
> End of 5.0 tests.
> +#
> +# BUG#50257: Missing info in REF column of the EXPLAIN
> +# lines for subselects
> +#
> +CREATE TABLE t1 (a INT, b INT, INDEX (a));
> +INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
> +
> +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
> +
> +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 const 1 Using where; Using index
> +
> +DROP TABLE t1;
> +End of 5.5 tests.
>
> === modified file 'mysql-test/t/subselect4.test'
> --- a/mysql-test/t/subselect4.test 2009-09-18 09:34:08 +0000
> +++ b/mysql-test/t/subselect4.test 2010-03-09 12:13:25 +0000
> @@ -62,3 +62,21 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
> DROP TABLE t1,t2,t3;
>
> --echo End of 5.0 tests.
> +
> +--echo #
> +--echo # BUG#50257: Missing info in REF column of the EXPLAIN
> +--echo # lines for subselects
> +--echo #
> +
> +CREATE TABLE t1 (a INT, b INT, INDEX (a));
> +INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
> +
> +--echo
> +EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
> +--echo
> +EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
> +
> +--echo
> +DROP TABLE t1;
> +
> +--echo End of 5.5 tests.
>
> === modified file 'sql/sql_lex.cc'
> --- a/sql/sql_lex.cc 2010-02-24 13:52:27 +0000
> +++ b/sql/sql_lex.cc 2010-03-09 12:13:25 +0000
> @@ -340,7 +340,7 @@ void lex_start(THD *thd)
> lex->select_lex.sql_cache= SELECT_LEX::SQL_CACHE_UNSPECIFIED;
> lex->select_lex.init_order();
> lex->select_lex.group_list.empty();
> - lex->describe= 0;
> + lex->describe= DESCRIBE_NONE;
> lex->subqueries= FALSE;
> lex->view_prepare_mode= FALSE;
> lex->derived_tables= 0;
>
> === modified file 'sql/sql_lex.h'
> --- a/sql/sql_lex.h 2010-02-24 13:52:27 +0000
> +++ b/sql/sql_lex.h 2010-03-09 12:13:25 +0000
> @@ -140,6 +140,7 @@ enum enum_sql_command {
> };
>
> // describe/explain types
> +#define DESCRIBE_NONE 0 // Not explain query
> #define DESCRIBE_NORMAL 1
> #define DESCRIBE_EXTENDED 2
> /*
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-03-01 09:45:36 +0000
> +++ b/sql/sql_select.cc 2010-03-09 12:13:25 +0000
> @@ -5814,22 +5814,31 @@ static bool create_ref_for_key(JOIN *joi
> if (keyuse->null_rejecting)
> 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))
> - { // Compare against constant
> - store_key_item tmp(thd, keyinfo->key_part[i].field,
> +
> + if (keyuse->used_tables || thd->lex->describe)
> + /*
> + Comparing against a non-constant or executing an EXPLAIN
> + query (which refers to this info when printing the 'ref'
> + column of the query plan)
> + */
> + *ref_key++= get_store_key(thd,
> + keyuse,join->const_table_map,
> + &keyinfo->key_part[i],
> + key_buff, maybe_null);
> + else
> + { // Compare against constant
> + store_key_item tmp(thd, keyinfo->key_part[i].field,
> key_buff + maybe_null,
> maybe_null ? key_buff : 0,
> keyinfo->key_part[i].length, keyuse->val);
> - if (thd->is_fatal_error)
> - DBUG_RETURN(TRUE);
> - tmp.copy();
> + if (thd->is_fatal_error)
> + DBUG_RETURN(TRUE);
> + /*
> + The constant is the value to look for with this key. Copy
> + the value to ref->key_buff
> + */
> + tmp.copy();
> }
> - else
> - *ref_key++= get_store_key(thd,
> - keyuse,join->const_table_map,
> - &keyinfo->key_part[i],
> - key_buff, maybe_null);
> /*
> Remember if we are going to use REF_OR_NULL
> But only if field _really_ can be null i.e. we force JT_REF
>
>
>
> ------------------------------------------------------------------------
>
>
