From 0298bc347aad0ad0ed0083781d84d412a921459d Mon Sep 17 00:00:00 2001
From: "evgen@moonbone.local" <>
Date: Sat, 16 Jul 2005 03:29:12 +0400
Subject: 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.
---
 mysql-test/r/select.result | 11 +++++++++++
 mysql-test/t/select.test   | 11 +++++++++++
 2 files changed, 22 insertions(+)

(limited to 'mysql-test')

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;
-- 
cgit v1.2.1