MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 4 2010 1:36pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3127) Bug#50257
View as plain text  
#At file:///localhome/jl208045/mysql/mysql-next-mr-bugfixing/ based on revid:alik@stripped

 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()

    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-04 13:36:19 +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-04 13:36:19 +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-04 13:36:19 +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-04 13:36:19 +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-04 13:36:19 +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-04 13:36:19 +0000
@@ -5814,8 +5814,7 @@ 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))
+      if (!keyuse->used_tables && !thd->lex->describe)
       {					// Compare against constant
 	store_key_item tmp(thd, keyinfo->key_part[i].field,
                            key_buff + maybe_null,


Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20100304133619-kyzdq4qbku1b5lh7.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3127) Bug#50257Jorgen Loland4 Mar
  • Re: bzr commit into mysql-next-mr-bugfixing branch(jorgen.loland:3127) Bug#50257Øystein Grøvlen8 Mar
    • Re: bzr commit into mysql-next-mr-bugfixing branch(jorgen.loland:3127) Bug#50257Jørgen Løland8 Mar
      • Re: bzr commit into mysql-next-mr-bugfixing branch(jorgen.loland:3127) Bug#50257Jørgen Løland9 Mar