Fix bug#11482 4.1.12 produces different resultset for a complex query

  than in previous 4.1.x

Wrongly applied optimization were adding NOT NULL constraint which results in
rejecting valid rows and reduced result set.

The problem was that add_notnull_conds() while checking subquery were adding
NOT NULL constraint to left joined table, to which, normally, optimization 
don't have to be applied.


sql/sql_select.cc:
  Fix bug #11482 Wrongly applied optimization was erroneously rejecting valid rows
  Constraint were added to optimization appliance test.
mysql-test/t/select.test:
  Test case for bug #11482  Wrongly applied optimization was erroneously rejecting valid rows
mysql-test/r/select.result:
  Test case for bug #11482  Wrongly applied optimization was erroneously rejecting valid rows

3 files changed, 41 insertions, 1 deletions

diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 8160c5a2f3d..5c0616f9e54 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2559,3 +2559,14 @@ WHERE
 COUNT(*)
 4
 drop table t1,t2,t3;
+create table t1 (f1 int);
+insert into t1 values (1),(NULL);
+create table t2 (f2 int, f3 int, f4 int);
+create index idx1 on t2 (f4);
+insert into t2 values (1,2,3),(2,4,6);
+select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
+from  t2 C where A.f4 = C.f4) or A.f3 IS NULL;
+f2
+1
+NULL
+drop table t1,t2;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 37e4324152b..2e261d0611f 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2116,3 +2116,14 @@ WHERE
 
 drop table t1,t2,t3;
 
+#
+# Bug #11482 4.1.12 produces different resultset for a complex query 
+#             than in previous 4.1.x
+create table t1 (f1 int);
+insert into t1 values (1),(NULL);
+create table t2 (f2 int, f3 int, f4 int);
+create index idx1 on t2 (f4);
+insert into t2 values (1,2,3),(2,4,6);
+select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3)
+from  t2 C where A.f4 = C.f4) or A.f3 IS NULL; 
+drop table t1,t2;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 75fc189b21f..43cf649685e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3508,7 +3508,23 @@ inline void add_cond_and_fix(Item **e1, Item *e2)
       (where othertbl is a non-const table and othertbl.field may be NULL)
       and add them to conditions on correspoding tables (othertbl in this
       example).
-      
+
+      Exception from that is the case when referred_tab->join != join.
+      I.e. don't add NOT NULL constraints from any embedded subquery.
+      Consider this query:
+      SELECT A.f2 FROM t1 LEFT JOIN t2 A ON A.f2 = f1
+      WHERE A.f3=(SELECT MIN(f3) FROM  t2 C WHERE A.f4 = C.f4) OR A.f3 IS NULL;
+      Here condition A.f3 IS NOT NULL is going to be added to the WHERE
+      condition of the embedding query.
+      Another example:
+      SELECT * FROM t10, t11 WHERE (t10.a < 10 OR t10.a IS NULL)
+      AND t11.b <=> t10.b AND (t11.a = (SELECT MAX(a) FROM t12
+      WHERE t12.b = t10.a ));
+      Here condition t10.a IS NOT NULL is going to be added.
+      In both cases addition of NOT NULL condition will erroneously reject
+      some rows of the result set.
+      referred_tab->join != join constraint would disallow such additions.
+
       This optimization doesn't affect the choices that ref, range, or join
       optimizer make. This was intentional because this was added after 4.1
       was GA.
@@ -3539,6 +3555,8 @@ static void add_not_null_conds(JOIN *join)
           DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
           Item_field *not_null_item= (Item_field*)item;
           JOIN_TAB *referred_tab= not_null_item->field->table->reginfo.join_tab;
+          if (referred_tab->join != join)
+            continue;
           Item *notnull;
           if (!(notnull= new Item_func_isnotnull(not_null_item)))
             DBUG_VOID_RETURN;