bk commit into 5.0 tree (serg:1.1885) BUG#10162

[Sergei Golubchik]

Below is the list of changes that have just been committed into a local
5.0 repository of serg. When serg 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
  1.1885 05/04/27 01:32:44 serg@stripped +3 -0
  be sure to call top_level_item() on the new Item_cond_and,
  that merges ON and WHERE (BUG#10162)

  sql/sql_select.cc
    1.321 05/04/27 01:32:34 serg@stripped +1 -0
    be sure to call top_level_item() on the new Item_cond_and,
    that merges ON and WHERE (BUG#10162)

  mysql-test/t/join_outer.test
    1.27 05/04/27 01:32:34 serg@stripped +15 -0
    BUG#10162 - ON is merged with WHERE, left join is convered to a regular join

  mysql-test/r/join_outer.result
    1.38 05/04/27 01:32:34 serg@stripped +14 -0
    BUG#10162 - ON is merged with WHERE, left join is convered to a regular join

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	serg
# Host:	serg.mylan
# Root:	/usr/home/serg/Abk/mysql-5.0

--- 1.320/sql/sql_select.cc	Thu Apr 21 10:32:55 2005
+++ 1.321/sql/sql_select.cc	Wed Apr 27 01:32:34 2005
@@ -7396,6 +7396,7 @@
         if (conds)
         {
           conds= and_conds(conds, table->on_expr);
+          conds->top_level_item();
           /* conds is always a new item as both cond and on_expr existed */
           DBUG_ASSERT(!conds->fixed);
           conds->fix_fields(join->thd, 0, &conds);

--- 1.37/mysql-test/r/join_outer.result	Tue Apr  5 08:00:48 2005
+++ 1.38/mysql-test/r/join_outer.result	Wed Apr 27 01:32:34 2005
@@ -943,3 +943,17 @@
 Warning	1260	2 line(s) were cut by GROUP_CONCAT()
 drop table t1, t2;
 set group_concat_max_len=default;
+create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not
null, art int(11) not null, primary key  (gid,x,y));
+insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
+create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not
null, id int(11) not null, primary key  (gid,id,x,y), key id (id));
+insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
+create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null,
name char(12) not null, primary key  (id,set_id));
+insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4,
'f'), (1, 5, 'g'), (1, 6, 'h');
+explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
+left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
+and t1.gid =1 and t2.gid =1 and t3.set_id =1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	10	const,const,const	1	
+1	SIMPLE	t2	const	PRIMARY,id	PRIMARY	14	const,const,const,const	1	Using index
+1	SIMPLE	t3	const	PRIMARY	PRIMARY	3	const,const	1	
+drop tables t1,t2,t3;

--- 1.26/mysql-test/t/join_outer.test	Tue Apr  5 08:00:48 2005
+++ 1.27/mysql-test/t/join_outer.test	Wed Apr 27 01:32:34 2005
@@ -661,3 +661,18 @@
 select group_concat(t1.b,t2.c) from t1 inner join t2 using(a) group by t1.a;
 drop table t1, t2;
 set group_concat_max_len=default;
+
+#
+# BUG#10162 - ON is merged with WHERE, left join is convered to a regular join
+#
+create table t1 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not
null, art int(11) not null, primary key  (gid,x,y));
+insert t1 values (1, -5, -8, 2), (1, 2, 2, 1), (1, 1, 1, 1);
+create table t2 (gid smallint(5) unsigned not null, x int(11) not null, y int(11) not
null, id int(11) not null, primary key  (gid,id,x,y), key id (id));
+insert t2 values (1, -5, -8, 1), (1, 1, 1, 1), (1, 2, 2, 1);
+create table t3 ( set_id smallint(5) unsigned not null, id tinyint(4) unsigned not null,
name char(12) not null, primary key  (id,set_id));
+insert t3 values (0, 1, 'a'), (1, 1, 'b'), (0, 2, 'c'), (1, 2, 'd'), (1, 3, 'e'), (1, 4,
'f'), (1, 5, 'g'), (1, 6, 'h');
+explain select name from t1 left join t2 on t1.x = t2.x and t1.y = t2.y
+left join t3 on t1.art = t3.id where t2.id =1 and t2.x = -5 and t2.y =-8
+and t1.gid =1 and t2.gid =1 and t3.set_id =1;
+drop tables t1,t2,t3;
+