From: Date: March 13 2005 8:32am
Subject: bk commit into 4.1 tree (igor:1.2110) BUG#9017
List-Archive: http://lists.mysql.com/internals/22969
X-Bug: 9017
Message-Id: <20050313073200.27382106CED@rurik.mysql.com>

Below is the list of changes that have just been committed into a local
4.1 repository of igor. When igor 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.2110 05/03/12 23:31:52 igor@stripped +3 -0
  join_outer.result, join_outer.test:
    Added a test case for bug #9017.
  item_cmpfunc.h:
    A wrong not_null_tables method for Item_cond_xor
    caused a conversion of a left join into an inner join
    that was not valid.

  mysql-test/r/join_outer.result
    1.34 05/03/12 23:31:12 igor@stripped +22 -0
    Added a test case for bug #9017.

  mysql-test/t/join_outer.test
    1.18 05/03/12 23:30:34 igor@stripped +19 -0
    Added a test case for bug #9017.

  sql/item_cmpfunc.h
    1.104 05/03/12 23:29:15 igor@stripped +1 -0
    A wrong not_null_tables method for Item_cond_xor
    caused a conversion of a left join into an inner join
    that was not valid.

# 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:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-4.1-0

--- 1.103/sql/item_cmpfunc.h	Thu Mar 10 15:22:24 2005
+++ 1.104/sql/item_cmpfunc.h	Sat Mar 12 23:29:15 2005
@@ -1013,6 +1013,7 @@
   enum Type type() const { return FUNC_ITEM; }
   longlong val_int();
   const char *func_name() const { return "xor"; }
+  table_map not_null_tables() const { return and_tables_cache; }
 };
 
 

--- 1.33/mysql-test/r/join_outer.result	Thu Mar 10 15:23:15 2005
+++ 1.34/mysql-test/r/join_outer.result	Sat Mar 12 23:31:12 2005
@@ -836,3 +836,25 @@
 1	0	0-SV
 2	10	10-SV
 DROP TABLE invoice, text_table;
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
+INSERT INTO t2 VALUES (1,2), (2,2);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a	b	a	b
+1	1	1	2
+2	1	2	2
+3	1	NULL	NULL
+4	2	NULL	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
+a	b	a	b
+1	1	1	2
+2	1	2	2
+3	1	NULL	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
+a	b	a	b
+1	1	1	2
+2	1	2	2
+3	1	NULL	NULL
+DROP TABLE t1,t2;

--- 1.17/mysql-test/t/join_outer.test	Thu Mar 10 15:23:15 2005
+++ 1.18/mysql-test/t/join_outer.test	Sat Mar 12 23:30:34 2005
@@ -595,3 +595,22 @@
   WHERE (invoice.id LIKE '%' OR text_table.text_data LIKE '%');
 
 DROP TABLE invoice, text_table;
+
+#
+# Test for bug #9017: left join mistakingly converted to inner join
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
+INSERT INTO t2 VALUES (1,2), (2,2);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+  WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
+
+DROP TABLE t1,t2;
+
+