From d1c2e8508fcc795eb5aeafe1af492929ec13ee97 Mon Sep 17 00:00:00 2001
From: Sergey Glukhov <Sergey.Glukhov@sun.com>
Date: Fri, 19 Mar 2010 10:21:37 +0400
Subject: Bug#51598 Inconsistent behaviour with a COALESCE statement inside an
 IN comparison Optimizer erroneously translated LEFT JOIN into INNER JOIN. It
 leads to cutting rows with NULL right side. It happens because Item_row uses
 not_null_tables() method form the base(Item) class and does not calculate
 'null tables' properly. The fix is adding calculation of 'not null tables' to
 Item_row.

mysql-test/r/join_outer.result:
  test result
mysql-test/t/join_outer.test:
  test case
sql/item_row.cc:
  adding calculation of 'not null tables' to Item_row.
sql/item_row.h:
  adding calculation of 'not null tables' to Item_row.
---
 sql/item_row.cc | 4 +++-
 sql/item_row.h  | 4 +++-
 2 files changed, 6 insertions(+), 2 deletions(-)

(limited to 'sql')

diff --git a/sql/item_row.cc b/sql/item_row.cc
index 29b37eb2bc0..7535c1fa80b 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -30,7 +30,8 @@
 */
 
 Item_row::Item_row(List<Item> &arg):
-  Item(), used_tables_cache(0), const_item_cache(1), with_null(0)
+  Item(), used_tables_cache(0), not_null_tables_cache(0),
+  const_item_cache(1), with_null(0)
 {
 
   //TODO: think placing 2-3 component items in item (as it done for function)
@@ -71,6 +72,7 @@ bool Item_row::fix_fields(THD *thd, Item **ref)
     Item *item= *arg;
     used_tables_cache |= item->used_tables();
     const_item_cache&= item->const_item() && !with_null;
+    not_null_tables_cache|= item->not_null_tables();
     /*
       Some subqueries transformations aren't done in the view_prepare_mode thus
       is_null() will fail. So we skip is_null() calculation for CREATE VIEW as
diff --git a/sql/item_row.h b/sql/item_row.h
index 67441f49603..76d1c875e7d 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -16,7 +16,7 @@
 class Item_row: public Item
 {
   Item **items;
-  table_map used_tables_cache;
+  table_map used_tables_cache, not_null_tables_cache;
   uint arg_count;
   bool const_item_cache;
   bool with_null;
@@ -26,6 +26,7 @@ public:
     Item(),
     items(item->items),
     used_tables_cache(item->used_tables_cache),
+    not_null_tables_cache(0),
     arg_count(item->arg_count),
     const_item_cache(item->const_item_cache),
     with_null(0)
@@ -65,6 +66,7 @@ public:
   bool const_item() const { return const_item_cache; };
   enum Item_result result_type() const { return ROW_RESULT; }
   void update_used_tables();
+  table_map not_null_tables() const { return not_null_tables_cache; }
   virtual void print(String *str, enum_query_type query_type);
 
   bool walk(Item_processor processor, bool walk_subquery, uchar *arg);
-- 
cgit v1.2.1