Merge lp:~linuxjedi/drizzle/optimizer-bugs into lp:drizzle/7.0

[Andrew Hutchings]

Backport fix from http://bugs.mysql.com/bug.php?id=58818

=== modified file 'drizzled/item/subselect.cc'
--- drizzled/item/subselect.cc	2011-03-03 08:29:06 +0000
+++ drizzled/item/subselect.cc	2011-03-07 09:37:26 +0000
@@ -1208,6 +1208,7 @@
     select_lex->having= join->having= and_items(join->having, item);
     if (join->having == item)
       item->name= (char*)in_having_cond;
+    select_lex->having->top_level_item();
     select_lex->having_fix_field= 1;
     /*
       we do not check join->having->fixed, because Item_and (from and_items)

=== added file 'tests/suite/regression/r/709500.result'
--- tests/suite/regression/r/709500.result	1970-01-01 00:00:00 +0000
+++ tests/suite/regression/r/709500.result	2011-03-07 09:37:26 +0000
@@ -0,0 +1,61 @@
+drop table if exists t1,t1s,t2s;
+create table t1(i int);
+insert into t1 values (1), (2), (3);
+create table t1s(i int);
+insert into t1s values (10), (20), (30);
+create table t2s(i int);
+insert into t2s values (100), (200), (300);
+select straight_join t2s.i
+from
+t1s left outer join t2s on t2s.i = t1s.i;
+i
+NULL
+NULL
+NULL
+select straight_join t2s.i
+from
+t1s left outer join t2s on t2s.i = t1s.i
+having t2s.i = 999;
+i
+select * from t1
+where t1.i NOT IN
+(
+select straight_join t2s.i 
+from
+t1s left outer join t2s on t2s.i = t1s.i
+having t2s.i = 100
+);
+i
+1
+2
+3
+select * from t1
+where t1.i IN
+(
+select straight_join t2s.i 
+from
+t1s left outer join t2s on t2s.i = t1s.i
+having t2s.i = 100
+) IS UNKNOWN;
+i
+select * from t1
+where not t1.i = ANY
+(
+select straight_join t2s.i 
+from
+t1s left outer join t2s on t2s.i = t1s.i
+having t2s.i = 999
+);
+i
+1
+2
+3
+select * from t1
+where t1.i = ANY (
+select straight_join t2s.i 
+from
+t1s left outer join t2s on t2s.i = t1s.i
+having t2s.i = 999
+) is unknown;
+i
+drop table t1,t1s,t2s;

=== added file 'tests/suite/regression/t/709500.test'
--- tests/suite/regression/t/709500.test	1970-01-01 00:00:00 +0000
+++ tests/suite/regression/t/709500.test	2011-03-07 09:37:26 +0000
@@ -0,0 +1,57 @@
+--disable_warnings
+drop table if exists t1,t1s,t2s;
+--enable_warnings
+
+create table t1(i int);
+insert into t1 values (1), (2), (3);
+
+create table t1s(i int);
+insert into t1s values (10), (20), (30);
+create table t2s(i int);
+insert into t2s values (100), (200), (300);
+
+select straight_join t2s.i
+   from
+   t1s left outer join t2s on t2s.i = t1s.i;
+
+select straight_join t2s.i
+   from
+   t1s left outer join t2s on t2s.i = t1s.i
+   having t2s.i = 999;
+
+select * from t1
+where t1.i NOT IN
+(
+  select straight_join t2s.i 
+  from
+  t1s left outer join t2s on t2s.i = t1s.i
+  having t2s.i = 100
+);
+
+select * from t1
+where t1.i IN
+(
+  select straight_join t2s.i 
+  from
+  t1s left outer join t2s on t2s.i = t1s.i
+  having t2s.i = 100
+) IS UNKNOWN;
+
+select * from t1
+where not t1.i = ANY
+(
+  select straight_join t2s.i 
+  from
+  t1s left outer join t2s on t2s.i = t1s.i
+  having t2s.i = 999
+);
+
+select * from t1
+ where t1.i = ANY (
+  select straight_join t2s.i 
+  from
+  t1s left outer join t2s on t2s.i = t1s.i
+  having t2s.i = 999
+ ) is unknown;
+
+drop table t1,t1s,t2s;