MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 11 2010 10:29am
Subject:bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3125) Bug#50257
View as plain text  
#At file:///localhome/jl208045/mysql/mysql-next-mr-bugfixing/ based on revid:zhenxing.he@stripped

 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.

    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-11 10:29:10 +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-11 10:29:10 +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-11 10:29:10 +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-11 10:29:10 +0000
@@ -1,4 +1,4 @@
-/* Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
+/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -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-11 10:29:10 +0000
@@ -1,4 +1,4 @@
-/* Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
+/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -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-11 10:29:10 +0000
@@ -1,4 +1,4 @@
-/* Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
+/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -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-20100311102910-tsbh5nal1qz73fjd.bundle
Thread
bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3125) Bug#50257Jorgen Loland11 Mar