MySQL Lists are EOL. Please join:

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

 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.

    modified:
      mysql-test/r/myisam_mrr.result
      mysql-test/r/subselect.result
      mysql-test/r/subselect4.result
      mysql-test/r/subselect_no_mat.result
      mysql-test/r/subselect_no_opts.result
      mysql-test/r/subselect_no_semijoin.result
      mysql-test/t/subselect4.test
      sql/sql_lex.cc
      sql/sql_lex.h
      sql/sql_select.cc
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2009-02-02 11:19:12 +0000
+++ b/mysql-test/r/myisam_mrr.result	2010-03-11 10:43:36 +0000
@@ -348,7 +348,7 @@ GROUP BY t2.pk
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t2	ALL	int_key	int_key	5		3	33.33	Using index condition; Using filesort
+2	SUBQUERY	t2	ALL	int_key	int_key	5	const	3	33.33	Using index condition; Using filesort
 Warnings:
 Note	1003	select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
 DROP TABLE t1, t2;

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2010-03-01 19:02:51 +0000
+++ b/mysql-test/r/subselect.result	2010-03-11 10:43:36 +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	2010-02-26 14:04:18 +0000
+++ b/mysql-test/r/subselect4.result	2010-03-11 10:43:36 +0000
@@ -60,6 +60,25 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
 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	
+
+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 index
+
+DROP TABLE t1;
+End of 5.5 tests.
+#
 # BUG#46743 "Azalea processing correlated, aggregate SELECT
 # subqueries incorrectly"
 #

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2010-02-26 14:04:18 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2010-03-11 10:43:36 +0000
@@ -367,9 +367,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/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2010-02-26 14:04:18 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2010-03-11 10:43:36 +0000
@@ -367,9 +367,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/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2010-02-26 14:04:18 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2010-03-11 10:43:36 +0000
@@ -367,9 +367,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/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	2010-01-20 10:11:29 +0000
+++ b/mysql-test/t/subselect4.test	2010-03-11 10:43:36 +0000
@@ -64,6 +64,24 @@ 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.
+
+--echo #
 --echo # BUG#46743 "Azalea processing correlated, aggregate SELECT
 --echo # subqueries incorrectly"
 --echo #

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2010-03-10 11:14:25 +0000
+++ b/sql/sql_lex.cc	2010-03-11 10:43:36 +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
@@ -339,7 +339,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-03-10 11:14:25 +0000
+++ b/sql/sql_lex.h	2010-03-11 10:43:36 +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
@@ -66,6 +66,7 @@ struct sys_var_with_base
 #include "sql_cmd.h"
 
 // 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-10 11:14:25 +0000
+++ b/sql/sql_select.cc	2010-03-11 10:43:36 +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
@@ -8333,22 +8333,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-20100311104336-cqaucpkyhwm832r9.bundle
Thread
bzr commit into mysql-6.0-codebase-bugfixing branch (jorgen.loland:3813)Bug#50257Jorgen Loland11 Mar