From: Date: May 18 2006 5:48am
Subject: bk commit into 5.0 tree (igor:1.2146) BUG#19816
List-Archive: http://lists.mysql.com/commits/6546
X-Bug: 19816
Message-Id: <20060518034858.2191A2C714A@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.2146 06/05/17 20:48:48 igor@stripped +3 -0
  Fixed bug #19816.
  This bug was introduced when the patch resolving the
  performance problem 17164 was applied. As a result
  of that modification the not_null_tables attributes
  were calculated incorrectly for constant OR conditions.
  This triggered invalid conversion of outer joins into
  inner joins.

  sql/item_cmpfunc.cc
    1.201 06/05/17 20:48:38 igor@stripped +3 -1
    Fixed bug #19816.
    This bug was introduced when the patch resolving the
    performance problem 17164 was applied. As a result
    of that modification the not_null_tables attributes
    were calculated incorrectly for constant OR conditions.
    This triggered invalid conversion of outer joins into
    inner joins.

  mysql-test/t/join_outer.test
    1.37 06/05/17 20:48:37 igor@stripped +18 -0
    Added a test case for bug #19816.

  mysql-test/r/join_outer.result
    1.48 06/05/17 20:48:37 igor@stripped +37 -2
    Added a test case for bug #19816.

# 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-2

--- 1.200/sql/item_cmpfunc.cc	2006-04-24 14:50:25 -07:00
+++ 1.201/sql/item_cmpfunc.cc	2006-05-17 20:48:38 -07:00
@@ -2570,7 +2570,9 @@
 	(item= *li.ref())->check_cols(1))
       return TRUE; /* purecov: inspected */
     used_tables_cache|=     item->used_tables();
-    if (!item->const_item())
+    if (item->const_item())
+      and_tables_cache= (table_map) 0;
+    else
     {
       tmp_table_map= item->not_null_tables();
       not_null_tables_cache|= tmp_table_map;

--- 1.47/mysql-test/r/join_outer.result	2006-05-13 11:55:58 -07:00
+++ 1.48/mysql-test/r/join_outer.result	2006-05-17 20:48:37 -07:00
@@ -1151,8 +1151,8 @@
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
 WHERE t1.name LIKE 'A%' OR FALSE;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
-1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
 DROP VIEW IF EXISTS v1,v2;
 DROP TABLE IF EXISTS t1,t2;
@@ -1175,4 +1175,39 @@
 2	NULL
 3	3
 DROP VIEW v1,v2;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (2), (3);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
+a	b
+1	NULL
+2	2
+3	3
+4	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
+a	b
+1	NULL
+2	2
+3	3
+4	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
+a	b
+1	NULL
+2	2
+3	3
+4	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
+a	b
+1	NULL
+2	2
+3	3
+4	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
+a	b
+1	NULL
+2	2
+3	3
+4	NULL
 DROP TABLE t1,t2;

--- 1.36/mysql-test/t/join_outer.test	2006-05-13 11:55:59 -07:00
+++ 1.37/mysql-test/t/join_outer.test	2006-05-17 20:48:37 -07:00
@@ -805,3 +805,21 @@
 
 DROP VIEW v1,v2;
 DROP TABLE t1,t2;
+
+#
+# Bug 19816: LEFT OUTER JOIN with constant ORed predicates in WHERE clause
+# 
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (2), (3);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1 OR 1);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (0 OR 1);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0);
+
+DROP TABLE t1,t2;