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.

1 files changed, 3 insertions, 1 deletions

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);