From: Date: April 5 2008 11:24pm
Subject: bk commit into 6.0 tree (sergefp:1.2623) BUG#35674
List-Archive: http://lists.mysql.com/commits/44954
X-Bug: 35674
Message-Id: <20080405212440.599C125F01C@pslp.localdomain>

Below is the list of changes that have just been committed into a local
6.0 repository of sergefp.  When sergefp does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-04-06 01:24:29+04:00, sergefp@stripped +3 -0
  BUG#35674: Range optimizer ignores conditions on inner tables in semi-join IN subqueries
  - Let range optimizer use such conditions.

  mysql-test/r/subselect_sj2.result@stripped, 2008-04-06 01:24:22+04:00, sergefp@stripped +3 -3
    BUG#35674: Range optimizer ignores conditions on inner tables in semi-join IN subqueries
    - Update test result, testcase

  mysql-test/t/subselect_sj2.test@stripped, 2008-04-06 01:24:22+04:00, sergefp@stripped +2 -1
    BUG#35674: Range optimizer ignores conditions on inner tables in semi-join IN subqueries
    - Update test result, testcase

  sql/sql_select.cc@stripped, 2008-04-06 01:24:22+04:00, sergefp@stripped +10 -3
    BUG#35674: Range optimizer ignores conditions on inner tables in semi-join IN subqueries
    - Let range optimizer use such conditions.

diff -Nrup a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
--- a/mysql-test/r/subselect_sj2.result	2008-03-26 15:48:40 +03:00
+++ b/mysql-test/r/subselect_sj2.result	2008-04-06 01:24:22 +04:00
@@ -303,9 +303,9 @@ t2.Code IN (SELECT Country FROM t3 
 WHERE Language='English' AND Percentage > 10 AND
 t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	Population,Country	NULL	NULL	NULL	30	Using where; Start temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where
-1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t3.Country	1	Using index condition; Using where; End temporary
+1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Start temporary
+1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using index condition; Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
diff -Nrup a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test
--- a/mysql-test/t/subselect_sj2.test	2008-03-26 15:48:40 +03:00
+++ b/mysql-test/t/subselect_sj2.test	2008-04-06 01:24:22 +04:00
@@ -146,7 +146,8 @@ drop table t0, t1,t2,t3;
 
 #
 # Bug #27348: Assertion abort for a query with two subqueries to be flattened  
-#  
+# Bug #35674: Range optimizer ignores conditions on inner tables in semi-join IN subqueries 
+#
 CREATE TABLE t1 (
   ID int(11) NOT NULL auto_increment,
   Name char(35) NOT NULL default '',
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-04-06 00:46:47 +04:00
+++ b/sql/sql_select.cc	2008-04-06 01:24:22 +04:00
@@ -4093,9 +4093,16 @@ make_join_statistics(JOIN *join, TABLE_L
       all select distinct fields participate in one index.
     */
     add_group_and_distinct_keys(join, s);
-
-    if (!s->const_keys.is_clear_all() &&
-        !s->table->pos_in_table_list->embedding)
+    
+    /*
+      Perform range analysis if there are keys it could use (1). 
+      Don't do range analysis if we're on the inner side of an outer join (2).
+      Do range analysis if we're on the inner side of a semi-join (3).
+    */
+    if (!s->const_keys.is_clear_all() &&                        // (1)
+        (!s->table->pos_in_table_list->embedding ||             // (2)
+         (s->table->pos_in_table_list->embedding &&             // (3)
+          s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
     {
       ha_rows records;
       SQL_SELECT *select;