MDEV-19778 Wrong Result on Left Outer Join with Subquery right on true

           and WHERE filter afterwards

This patch complements the patch fixing the bug MDEV-6892. The latter
properly handled queries that used mergeable views returning constant
columns as inner tables of outer joins and whose where clause contained
predicates referring to these columns if the predicates of happened not
to be equality predicates. Otherwise the server still could return wrong
result sets for such queries. Besides the fix for MDEV-6892 prevented
some possible conversions of outer joins to inner joins for such queries.

This patch corrected the function check_simple_equality() to handle
properly conjunctive equalities of the where clause that refer to the
constant columns of mergeable views used as inner tables of an outer join.
The patch also changed the code of Item_direct_view_ref::not_null_tables().
This change allowed to take into account predicates containing references
to constant columns of mergeable views when converting outer joins into
inner joins.

1 files changed, 34 insertions, 0 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 61c4278b43f..34aaa27ccf9 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -2194,3 +2194,37 @@ JOIN
  ) gp_20 ON gp_20.id=t2.id ;
 
 DROP TABLE  t1, t2;
+
+--echo #
+--echo # MDEV-19778: equality condition for mergeable view returning constants
+--echo #             in its columns and used as inner table of outer join
+--echo #
+
+create table t1 (pk int, a int);
+insert into t1 values (1,7), (2,3), (3,2), (4,3);
+create table t2 (b int);
+insert into t2 values (5), (1), (NULL), (3);
+create table t3 (c int);
+insert into t3 values (1), (8);
+
+create view v1 as
+select 3 as d, t2.b from t2;
+
+let $q=
+select * from t1 left join v1 on t1.pk <= 2 where t1.a=v1.d;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select * from t1 left join (select 3 as d, t2.b from t2) dt on t1.pk <= 2
+  where t1.a=dt.d;
+eval $q;
+eval explain extended $q;
+
+let $q=
+select * from t1 left join (v1,t3) on t1.pk <= 2 where t1.a=v1.d;
+eval $q;
+eval explain extended $q;
+
+drop view v1;
+drop table t1,t2,t3;