From: Date: September 30 2005 9:39am
Subject: bk commit into 5.0 tree (timour:1.2029) BUG#13597
List-Archive: http://lists.mysql.com/internals/30541
X-Bug: 13597
Message-Id: <20050930073922.6311E1B4854@lamia.home>

Below is the list of changes that have just been committed into a local
5.0 repository of timka. When timka 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.2029 05/09/30 10:39:17 timour@stripped +3 -0
  Fix for BUG#13597 - columns in ON condition not resolved if references a table in a nested right join.
  
  The problem was in that when finding the last table reference in a nested join tree,
  the procedure doing the iteration over the right-most branches of a join tree
  was testing for RIGHT JOINs the table reference that represents the join, and not
  the second operand of the JOIN. Currently the information whether a join is LEFT/RIGHT
  is stored not on the join object itself, but on one of its operands.

  sql/table.cc
    1.193 05/09/30 10:39:14 timour@stripped +8 -6
    - test whether a table reference is a right join by testing the
      rigth join operand (first in the list of operands), and not
      the table reference that represents the join itself.
    - clearer comments

  mysql-test/t/select.test
    1.81 05/09/30 10:39:14 timour@stripped +34 -0
    Added test for BUG#13597

  mysql-test/r/select.result
    1.97 05/09/30 10:39:14 timour@stripped +23 -0
    Added test for BUG#13597

# 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:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/5.0-bug-13597

--- 1.192/sql/table.cc	2005-09-22 23:46:51 +03:00
+++ 1.193/sql/table.cc	2005-09-30 10:39:14 +03:00
@@ -2291,8 +2291,10 @@
     List_iterator_fast<TABLE_LIST> it(cur_nested_join->join_list);
     cur_table_ref= it++;
     /*
-      If 'this' is a RIGHT JOIN, the operands in 'join_list' are in reverse
-      order, thus the first operand is already at the front of the list.
+      If the current nested join is a RIGHT JOIN, the operands in
+      'join_list' are in reverse order, thus the first operand is
+      already at the front of the list. Otherwise the first operand
+      is in the end of the list of join operands.
     */
     if (!(cur_table_ref->outer_join & JOIN_TYPE_RIGHT))
     {
@@ -2343,9 +2345,11 @@
        cur_nested_join;
        cur_nested_join= cur_table_ref->nested_join)
   {
+    cur_table_ref= cur_nested_join->join_list.head();
     /*
-      If 'this' is a RIGHT JOIN, the operands in 'join_list' are in reverse
-      order, thus the last operand is in the end of the list.
+      If the current nested is a RIGHT JOIN, the operands in
+      'join_list' are in reverse order, thus the last operand is in the
+      end of the list.
     */
     if ((cur_table_ref->outer_join & JOIN_TYPE_RIGHT))
     {
@@ -2355,8 +2359,6 @@
       while ((next= it++))
         cur_table_ref= next;
     }
-    else
-      cur_table_ref= cur_nested_join->join_list.head();
     if (cur_table_ref->is_leaf_for_name_resolution())
       break;
   }

--- 1.96/mysql-test/r/select.result	2005-09-28 20:29:15 +03:00
+++ 1.97/mysql-test/r/select.result	2005-09-30 10:39:14 +03:00
@@ -3038,3 +3038,26 @@
 102
 drop table t1, t2;
 drop view v1, v2, v3;
+create table a (
+id int(11) not null default '0'
+) engine=myisam default charset=latin1;
+insert into a values (123),(191),(192);
+create table b (
+id char(16) character set utf8 not null default ''
+) engine=myisam default charset=latin1;
+insert into b values ('58013'),('58014'),('58015'),('58016');
+create table c (
+a_id int(11) not null default '0',
+b_id char(16) character set utf8 default null
+) engine=myisam default charset=latin1;
+insert into c values
+(123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
+select count(*)
+from a inner join (c left join b on b.id = c.b_id) on a.id = c.a_id;
+count(*)
+6
+select count(*)
+from a inner join (b right join c on b.id = c.b_id) on a.id = c.a_id;
+count(*)
+6
+drop table a, b, c;

--- 1.80/mysql-test/t/select.test	2005-09-28 16:32:01 +03:00
+++ 1.81/mysql-test/t/select.test	2005-09-30 10:39:14 +03:00
@@ -2575,3 +2575,37 @@
 
 drop table t1, t2;
 drop view v1, v2, v3;
+
+#
+# Bug #13597 Column in ON condition not resolved if references a table in
+# nested right join.
+#
+
+create table a (
+  id int(11) not null default '0'
+) engine=myisam default charset=latin1;
+
+insert into a values (123),(191),(192);
+
+create table b (
+  id char(16) character set utf8 not null default ''
+) engine=myisam default charset=latin1;
+
+insert into b values ('58013'),('58014'),('58015'),('58016');
+
+create table c (
+  a_id int(11) not null default '0',
+  b_id char(16) character set utf8 default null
+) engine=myisam default charset=latin1;
+
+insert into c values
+(123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');
+
+-- both queries are equivalent
+select count(*)
+from a inner join (c left join b on b.id = c.b_id) on a.id = c.a_id;
+
+select count(*)
+from a inner join (b right join c on b.id = c.b_id) on a.id = c.a_id;
+
+drop table a, b, c;