Merge spetrunia@bk-internal.mysql.com:/home/bk/mysql-5.0-opt

into  mysql.com:/home/psergey/mysql-5.0-bug16255-merge

4 files changed, 140 insertions, 30 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index b5d059be4c5..0c62d3f570f 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2328,9 +2328,9 @@ explain select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t4	const	id4	NULL	NULL	NULL	1	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t4	ALL	id4	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3
 left join t4 on id3 = id4 where id2 = 1 or id4 = 1;
 id1	id2	id3	id4	id44
@@ -3479,3 +3479,41 @@ Warning	1466	Leading spaces are removed from name ' a '
 execute stmt;
 a 
 1
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
+CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
+INSERT INTO t2 VALUES
+(1), (1), (1), (1), (1), (1), (1), (1),
+(2), (2), (2), (2),
+(3), (3),
+(4);
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	ref	idx	idx	4	const	7	Using index
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	ref	idx	idx	4	const	1	Using index
+DROP TABLE t1, t2;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
+CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
+INSERT INTO t2 VALUES (2,1), (3,2);
+CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
+INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+WHERE t1.id=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	idx1	NULL	NULL	NULL	1	
+1	SIMPLE	t3	ref	idx1	idx1	5	const	3	Using where
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+WHERE t1.id=2;
+id	a	b	c	d	e
+2	NULL	NULL	NULL	2	10
+2	NULL	NULL	NULL	2	20
+2	NULL	NULL	NULL	2	40
+2	NULL	NULL	NULL	2	50
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 197d89d02d5..36b3749b4d7 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2957,3 +2957,44 @@ SELECT 0.9888889889 * 1.011111411911;
 #
 prepare stmt from 'select 1 as " a "';
 execute stmt;
+
+#
+# Bug #21390: wrong estimate of rows after elimination of const tables
+#
+
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int NOT NULL);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4);
+
+CREATE TABLE t2 (c int NOT NULL, INDEX idx(c));
+INSERT INTO t2 VALUES
+  (1), (1), (1), (1), (1), (1), (1), (1),
+  (2), (2), (2), (2),
+  (3), (3),
+  (4);
+
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;
+EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;
+
+DROP TABLE t1, t2;
+
+#
+# No matches for a join after substitution of a const table
+#
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a int);
+INSERT INTO t1 VALUES (1,2), (2,NULL), (3,2);
+
+CREATE TABLE t2 (b int, c INT, INDEX idx1(b));
+INSERT INTO t2 VALUES (2,1), (3,2);
+
+CREATE TABLE t3 (d int,  e int, INDEX idx1(d));
+INSERT INTO t3 VALUES (2,10), (2,20), (1,30), (2,40), (2,50);
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+  WHERE t1.id=2;
+SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
+  WHERE t1.id=2;
+
+
+DROP TABLE t1,t2,t3;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 9a836705bb9..71ba63dcf98 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3586,8 +3586,7 @@ static SEL_TREE *get_func_mm_tree(PARAM *param, Item_func *cond_func,
 
   case Item_func::BETWEEN:
   {
-    int i= (int ) value;
-    if (! i)
+    if (!value)
     {
       if (inv)
       {
@@ -3610,8 +3609,10 @@ static SEL_TREE *get_func_mm_tree(PARAM *param, Item_func *cond_func,
     else
       tree= get_mm_parts(param, cond_func, field,
                          (inv ?
-                          (i == 1 ? Item_func::GT_FUNC : Item_func::LT_FUNC) :
-                          (i == 1 ? Item_func::LE_FUNC : Item_func::GE_FUNC)),
+                          (value == (Item*)1 ? Item_func::GT_FUNC :
+                                               Item_func::LT_FUNC):
+                          (value == (Item*)1 ? Item_func::LE_FUNC :
+                                               Item_func::GE_FUNC)),
                          cond_func->arguments()[0], cmp_type);
     break;
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ff87bd1ca5b..47eb19364ee 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2205,6 +2205,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
   int ref_changed;
   do
   {
+  more_const_tables_found:
     ref_changed = 0;
     found_ref=0;
 
@@ -2216,6 +2217,30 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
     for (JOIN_TAB **pos=stat_vector+const_count ; (s= *pos) ; pos++)
     {
       table=s->table;
+
+      /* 
+        If equi-join condition by a key is null rejecting and after a
+        substitution of a const table the key value happens to be null
+        then we can state that there are no matches for this equi-join.
+      */  
+      if ((keyuse= s->keyuse) && *s->on_expr_ref)
+      {
+        while (keyuse->table == table)
+        {
+          if (!(keyuse->val->used_tables() & ~join->const_table_map) &&
+              keyuse->val->is_null() && keyuse->null_rejecting)
+          {
+            s->type= JT_CONST;
+            mark_as_null_row(table);
+            found_const_table_map|= table->map;
+	    join->const_table_map|= table->map;
+	    set_position(join,const_count++,s,(KEYUSE*) 0);
+            goto more_const_tables_found;
+           }
+	  keyuse++;
+        }
+      }
+
       if (s->dependent)				// If dependent on some table
       {
 	// All dep. must be constants
@@ -2266,34 +2291,38 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
 	  } while (keyuse->table == table && keyuse->key == key);
 
 	  if (eq_part.is_prefix(table->key_info[key].key_parts) &&
-	      ((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY)) ==
-	       HA_NOSAME) &&
               !table->fulltext_searched && 
               !table->pos_in_table_list->embedding)
 	  {
-	    if (const_ref == eq_part)
-	    {					// Found everything for ref.
-	      int tmp;
-	      ref_changed = 1;
-	      s->type= JT_CONST;
-	      join->const_table_map|=table->map;
-	      set_position(join,const_count++,s,start_keyuse);
-	      if (create_ref_for_key(join, s, start_keyuse,
-				     found_const_table_map))
-		DBUG_RETURN(1);
-	      if ((tmp=join_read_const_table(s,
-                                             join->positions+const_count-1)))
-	      {
-		if (tmp > 0)
-		  DBUG_RETURN(1);			// Fatal error
+            if ((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY))
+                 == HA_NOSAME)
+            {
+	      if (const_ref == eq_part)
+	      {					// Found everything for ref.
+	        int tmp;
+	        ref_changed = 1;
+	        s->type= JT_CONST;
+	        join->const_table_map|=table->map;
+	        set_position(join,const_count++,s,start_keyuse);
+	        if (create_ref_for_key(join, s, start_keyuse,
+				       found_const_table_map))
+		  DBUG_RETURN(1);
+	        if ((tmp=join_read_const_table(s,
+                                               join->positions+const_count-1)))
+	        {
+		  if (tmp > 0)
+		    DBUG_RETURN(1);			// Fatal error
+	        }
+	        else
+		  found_const_table_map|= table->map;
+	        break;
 	      }
 	      else
-		found_const_table_map|= table->map;
-	      break;
+	        found_ref|= refs;      // Table is const if all refs are const
 	    }
-	    else
-	      found_ref|= refs;		// Table is const if all refs are const
-	  }
+            else if (const_ref == eq_part)
+              s->const_keys.set_bit(key);
+          }
 	}
       }
     }
@@ -2696,7 +2725,8 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
     We use null_rejecting in add_not_null_conds() to add
     'othertbl.field IS NOT NULL' to tab->select_cond.
   */
-  (*key_fields)->null_rejecting= ((cond->functype() == Item_func::EQ_FUNC) &&
+  (*key_fields)->null_rejecting= ((cond->functype() == Item_func::EQ_FUNC ||
+                                   cond->functype() == Item_func::MULT_EQUAL_FUNC) &&
                                   ((*value)->type() == Item::FIELD_ITEM) &&
                                   ((Item_field*)*value)->field->maybe_null());
   (*key_fields)++;
@@ -3461,7 +3491,7 @@ best_access_path(JOIN      *join,
                                                  keyuse->used_tables));
             if (tmp < best_prev_record_reads)
             {
-              best_part_found_ref= keyuse->used_tables;
+              best_part_found_ref= keyuse->used_tables & ~join->const_table_map;
               best_prev_record_reads= tmp;
             }
             if (rec > keyuse->ref_table_rows)