MDEV-11958: LEFT JOIN with stored routine produce incorrect resultbb-5.5-MDEV-11958

Added forgoten method of Item_func_sp to make it correctly work with LEFT/RIGHT JOIN.

3 files changed, 70 insertions, 0 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 823c6f78cee..29673bdce3f 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -8055,4 +8055,44 @@ SET S.CLOSE_YN = ''
 where 1=1;
 drop function if exists f1;
 drop table t1,t2;
+#
+# MDEV-11958: LEFT JOIN with stored routine produce incorrect result
+#
+CREATE TABLE t (x INT);
+INSERT INTO t VALUES(1),(NULL);
+CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE IFNULL(t2.x,0)=0;
+x	x	IFNULL(t2.x,0)	f(t2.x,0)
+NULL	NULL	0	0
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE IFNULL(t2.x,0)=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (ifnull(`test`.`t2`.`x`,0) = 0)
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE f(t2.x,0)=0;
+x	x	IFNULL(t2.x,0)	f(t2.x,0)
+NULL	NULL	0	0
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+FROM t t1 LEFT JOIN t t2
+ON t1.x = t2.x
+WHERE f(t2.x,0)=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (`f`(`test`.`t2`.`x`,0) = 0)
+drop function f;
+drop table t;
 # End of 5.5 test
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 0e42bf3c831..dfe9d752739 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -9353,4 +9353,33 @@ where 1=1;
 drop function if exists f1;
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-11958: LEFT JOIN with stored routine produce incorrect result
+--echo #
+
+CREATE TABLE t (x INT);
+INSERT INTO t VALUES(1),(NULL);
+CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
+
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+                            FROM t t1 LEFT JOIN t t2
+                            ON t1.x = t2.x
+                            WHERE IFNULL(t2.x,0)=0;
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+                            FROM t t1 LEFT JOIN t t2
+                            ON t1.x = t2.x
+                            WHERE IFNULL(t2.x,0)=0;
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+                            FROM t t1 LEFT JOIN t t2
+                            ON t1.x = t2.x
+                            WHERE f(t2.x,0)=0;
+explain extended
+SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
+                            FROM t t1 LEFT JOIN t t2
+                            ON t1.x = t2.x
+                            WHERE f(t2.x,0)=0;
+
+drop function f;
+drop table t;
 --echo # End of 5.5 test
diff --git a/sql/item_func.h b/sql/item_func.h
index d60801745fe..3cff391126f 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -2091,6 +2091,7 @@ public:
   {
     return TRUE;
   }
+  table_map not_null_tables() const { return 0; }
 };