From: Date: April 16 2005 7:08am
Subject: bk commit into 5.0 tree (igor:1.1829) BUG#9516
List-Archive: http://lists.mysql.com/internals/24075
X-Bug: 9516
Message-Id: <20050416050847.7C09EC3A43@rurik.mysql.com>

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.1829 05/04/15 22:08:34 igor@stripped +3 -0
  subselect.result, subselect.test:
    Added a test case for bug #9516.
  item_subselect.h:
    Fixed bug #9516.
    The bug was due to that fact that the class Item_subselect
    inherited the generic implementation of the function
    not_null_tables that was not valid for the objects
    of this class. As a result evaluation of the
    not_null_tables attribute was not correct for subqueries.
    This caused invalid transformations of outer joins into
    inner joins.

  mysql-test/r/subselect.result
    1.111 05/04/15 22:08:08 igor@stripped +19 -0
    Added a test case for bug #9516.

  mysql-test/t/subselect.test
    1.97 05/04/15 22:07:39 igor@stripped +26 -0
    Added a test case for bug #9516.

  sql/item_subselect.h
    1.68 05/04/15 22:05:34 igor@stripped +1 -0
    Fixed bug #9516.
    The bug was due to that fact that the class Item_subselect
    inherited the generic implementation of the function
    not_null_tables that was not valid for the objects
    of this class. As a result evaluation of the
    not_null_tables attribute was not correct for subqueries.
    This caused invalid transformations of outer joins into
    inner joins.

# 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.110/mysql-test/r/subselect.result	Fri Apr 15 20:41:18 2005
+++ 1.111/mysql-test/r/subselect.result	Fri Apr 15 22:08:08 2005
@@ -2762,3 +2762,22 @@
 c1	c2
 1	1
 DROP TABLE t1,t2;
+CREATE TABLE t1 ( c1 integer );
+INSERT INTO t1 VALUES ( 1 );
+INSERT INTO t1 VALUES ( 2 );
+INSERT INTO t1 VALUES ( 3 );
+INSERT INTO t1 VALUES ( 6 );
+CREATE TABLE t2 ( c2 integer );
+INSERT INTO t2 VALUES ( 1 );
+INSERT INTO t2 VALUES ( 4 );
+INSERT INTO t2 VALUES ( 5 );
+INSERT INTO t2 VALUES ( 6 );
+CREATE TABLE t3 ( c3 integer );
+INSERT INTO t3 VALUES ( 7 );
+INSERT INTO t3 VALUES ( 8 );
+SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 
+WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
+c1	c2
+2	NULL
+3	NULL
+DROP TABLE t1,t2,t3;

--- 1.96/mysql-test/t/subselect.test	Fri Apr 15 20:40:33 2005
+++ 1.97/mysql-test/t/subselect.test	Fri Apr 15 22:07:39 2005
@@ -1773,3 +1773,29 @@
   WHERE c2 IN ( SELECT c2 FROM t2 WHERE c2 IN ( 1 ) );
 
 DROP TABLE t1,t2;
+
+#
+# Test for bug #9516: wrong evaluation of not_null_tables attribute in SQ 
+#
+
+CREATE TABLE t1 ( c1 integer );
+INSERT INTO t1 VALUES ( 1 );
+INSERT INTO t1 VALUES ( 2 );
+INSERT INTO t1 VALUES ( 3 );
+INSERT INTO t1 VALUES ( 6 ); 
+ 
+CREATE TABLE t2 ( c2 integer );
+INSERT INTO t2 VALUES ( 1 );
+INSERT INTO t2 VALUES ( 4 );
+INSERT INTO t2 VALUES ( 5 );
+INSERT INTO t2 VALUES ( 6 );
+
+CREATE TABLE t3 ( c3 integer );
+INSERT INTO t3 VALUES ( 7 );
+INSERT INTO t3 VALUES ( 8 );
+
+SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2 
+  WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
+
+DROP TABLE t1,t2,t3;
+

--- 1.67/sql/item_subselect.h	Fri Apr  1 00:00:35 2005
+++ 1.68/sql/item_subselect.h	Fri Apr 15 22:05:34 2005
@@ -96,6 +96,7 @@
   virtual bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;
+  table_map not_null_tables() const { return 0; }
   bool const_item() const;
   inline table_map get_used_tables_cache() { return used_tables_cache; }
   inline bool get_const_item_cache() { return const_item_cache; }