join_outer.result, join_outer.test:

  Added a test case for bug #9017.
item_cmpfunc.h:
  A wrong not_null_tables method for Item_cond_xor
  caused a conversion of a left join into an inner join
  that was not valid.


sql/item_cmpfunc.h:
  A wrong not_null_tables method for Item_cond_xor
  caused a conversion of a left join into an inner join
  that was not valid.
mysql-test/t/join_outer.test:
  Added a test case for bug #9017.
mysql-test/r/join_outer.result:
  Added a test case for bug #9017.

3 files changed, 42 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 09a55ba2054..7981fc9c733 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -836,3 +836,25 @@ id	text_id	text_data
 1	0	0-SV
 2	10	10-SV
 DROP TABLE invoice, text_table;
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
+INSERT INTO t2 VALUES (1,2), (2,2);
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+a	b	a	b
+1	1	1	2
+2	1	2	2
+3	1	NULL	NULL
+4	2	NULL	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
+a	b	a	b
+1	1	1	2
+2	1	2	2
+3	1	NULL	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
+a	b	a	b
+1	1	1	2
+2	1	2	2
+3	1	NULL	NULL
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 8096176a744..2ed7086746f 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -595,3 +595,22 @@ SELECT invoice.id, invoice.text_id, text_table.text_data
   WHERE (invoice.id LIKE '%' OR text_table.text_data LIKE '%');
 
 DROP TABLE invoice, text_table;
+
+#
+# Test for bug #9017: left join mistakingly converted to inner join
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int);
+CREATE TABLE t2 (a int PRIMARY KEY, b int);
+
+INSERT INTO t1 VALUES (1,1), (2,1), (3,1), (4,2);
+INSERT INTO t2 VALUES (1,2), (2,2);
+
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t1.b=1;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+  WHERE t1.b=1 XOR (NOT ISNULL(t2.a) AND t2.b=1);
+
+DROP TABLE t1,t2;
+
+
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 8e8e4e922c0..7c48854850e 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1013,6 +1013,7 @@ public:
   enum Type type() const { return FUNC_ITEM; }
   longlong val_int();
   const char *func_name() const { return "xor"; }
+  table_map not_null_tables() const { return and_tables_cache; }
 };