#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
Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20100309121325-650k4s9x18687kf7.bundle
