bk commit into 5.0 tree (igor:1.1897) BUG#9938

[igor]

Below is the list of changes that have just been committed into a local
5.0 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.1897 05/05/21 08:44:50 igor@stripped +3 -0
  join_outer.test, join_outer.result:
    Added test cases for bug #9938.
  item.h:
    Fixed bug #9938: missing method not_null_tables for Item_ref.

  mysql-test/r/join_outer.result
    1.39 05/05/21 08:42:43 igor@stripped +18 -0

  mysql-test/t/join_outer.test
    1.28 05/05/21 08:42:10 igor@stripped +23 -0
    Added test cases for bug #9938.

  sql/item.h
    1.128 05/05/21 08:40:27 igor@stripped +1 -0
    Fixed bug #9938: missing method not_null_tables for Item_ref.

# 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-5.0-0

--- 1.127/sql/item.h	Wed May 18 17:10:46 2005
+++ 1.128/sql/item.h	Sat May 21 08:40:27 2005
@@ -1325,6 +1325,7 @@
   { 
     return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); 
   }
+  table_map not_null_tables() const { return (*ref)->not_null_tables(); }
   void set_result_field(Field *field)	{ result_field= field; }
   bool is_result_field() { return 1; }
   void save_in_result_field(bool no_conversions)

--- 1.38/mysql-test/r/join_outer.result	Tue Apr 26 16:32:34 2005
+++ 1.39/mysql-test/r/join_outer.result	Sat May 21 08:42:43 2005
@@ -957,3 +957,21 @@
 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;
+CREATE TABLE t1 (EMPNUM INT, GRP INT);
+INSERT INTO t1 VALUES (0, 10);
+INSERT INTO t1 VALUES (2, 30);
+CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
+INSERT INTO t2 VALUES (0, 'KERI');
+INSERT INTO t2 VALUES (9, 'BARRY');
+CREATE VIEW v1 AS
+SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
+FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
+SELECT * FROM v1;
+EMPNUM	NAME	GRP
+0	KERI	10
+9	BARRY	NULL
+SELECT * FROM v1 WHERE EMPNUM < 10;
+EMPNUM	NAME	GRP
+0	KERI	10
+9	BARRY	NULL
+DROP TABLE t1,t2;

--- 1.27/mysql-test/t/join_outer.test	Tue Apr 26 16:32:34 2005
+++ 1.28/mysql-test/t/join_outer.test	Sat May 21 08:42:10 2005
@@ -676,3 +676,26 @@
 and t1.gid =1 and t2.gid =1 and t3.set_id =1;
 drop tables t1,t2,t3;
 
+#
+# Test for bug #9938: invalid conversion from outer join to inner join 
+# for queries containing indirect reference in WHERE clause
+#
+
+CREATE TABLE t1 (EMPNUM INT, GRP INT);
+INSERT INTO t1 VALUES (0, 10);
+INSERT INTO t1 VALUES (2, 30);
+
+CREATE TABLE t2 (EMPNUM INT, NAME CHAR(5));
+INSERT INTO t2 VALUES (0, 'KERI');
+INSERT INTO t2 VALUES (9, 'BARRY');
+
+CREATE VIEW v1 AS
+SELECT COALESCE(t2.EMPNUM,t1.EMPNUM) AS EMPNUM, NAME, GRP
+  FROM t2 LEFT OUTER JOIN t1 ON t2.EMPNUM=t1.EMPNUM;
+
+SELECT * FROM v1;
+SELECT * FROM v1 WHERE EMPNUM < 10;
+
+DROP TABLE t1,t2;
+
+