Merge remote-tracking branch 'origin/10.2' into bb-10.2-ext

1 files changed, 95 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index d7b4e107a8d..46e542910a1 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2337,4 +2337,99 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`i1` AS `i1`,`test`.`t1`.`v1` AS `v1`,`test`.`t2`.`i2` AS `i2`,`test`.`t2`.`v2` AS `v2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`v3` AS `v3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`v3` = 4 and `test`.`t1`.`i1` = `test`.`t3`.`i3` and `test`.`t2`.`i2` = `test`.`t3`.`i3`
 drop table t1,t2,t3;
+#
+# MDEV-11958: LEFT JOIN with stored routine produces 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
+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
+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;
+CREATE TABLE t1 (
+col1 DECIMAL(33,5) NULL DEFAULT NULL,
+col2 DECIMAL(33,5) NULL DEFAULT NULL
+);
+CREATE TABLE t2 (
+col1 DECIMAL(33,5) NULL DEFAULT NULL,
+col2 DECIMAL(33,5) NULL DEFAULT NULL,
+col3 DECIMAL(33,5) NULL DEFAULT NULL
+);
+INSERT INTO t1 VALUES (2, 1.1), (2, 2.1);
+INSERT INTO t2 VALUES (3, 3.1, 4), (1, 1, NULL);
+CREATE FUNCTION f1 ( p_num DECIMAL(45,15), p_return DECIMAL(45,15))
+RETURNS decimal(33,5)
+LANGUAGE SQL
+DETERMINISTIC
+CONTAINS SQL
+SQL SECURITY INVOKER
+BEGIN
+IF p_num IS NULL THEN
+RETURN p_return;
+ELSE
+RETURN p_num;
+END IF;
+END |
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,0) = 0;
+col1	col1	col3
+2.00000	NULL	NULL
+2.00000	NULL	NULL
+EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE IFNULL(t2.col3,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
+Warnings:
+Note	1003	select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where ifnull(`test`.`t2`.`col3`,0) = 0
+SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,0) = 0;
+col1	col1	col3
+2.00000	NULL	NULL
+2.00000	NULL	NULL
+EXPLAIN EXTENDED SELECT t1.col1, t2.col1, t2.col3
+FROM t1 LEFT OUTER JOIN t2 ON t1.col1 = t2.col2
+WHERE f1(t2.col3,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
+Warnings:
+Note	1003	select `test`.`t1`.`col1` AS `col1`,`test`.`t2`.`col1` AS `col1`,`test`.`t2`.`col3` AS `col3` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`col2` = `test`.`t1`.`col1`) where `f1`(`test`.`t2`.`col3`,0) = 0
+DROP FUNCTION f1;
+DROP TABLE t1,t2;
+# end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;