bzr commit into mysql-trunk branch (ole.john.aske:3488) Bug#58818

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-trunk/ based on revid:martin.hansson@stripped

 3488 Ole John Aske	2011-01-12 [merge]
      Merge of fix for bug#58818 from '5.5' -> 'trunk'

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_none.result
      sql/item_subselect.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-01-12 09:52:10 +0000
+++ b/mysql-test/include/subquery.inc	2011-01-12 12:21:16 +0000
@@ -4950,6 +4950,55 @@ SELECT * FROM t2 UNION SELECT * FROM t2
 DROP TABLE t1,t2;
 --enable_result_log
 
+--echo #
+--echo # Bug #58818: Incorrect result for IN/ANY subquery
+--echo # with HAVING condition 
+--echo #
+
+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 * 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 = 999
+);
+
+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 = 999
+) 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;
+
 --echo End of 5.1 tests
  
 --echo #

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2010-12-16 17:38:26 +0000
+++ b/mysql-test/r/subquery_all.result	2011-01-12 12:21:16 +0000
@@ -6100,6 +6100,58 @@ ORDER BY (SELECT * FROM t1 WHERE MATCH(a
 SELECT * FROM t2 UNION SELECT * FROM t2
 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
 DROP TABLE t1,t2;
+#
+# Bug #58818: Incorrect result for IN/ANY subquery
+# with HAVING condition 
+#
+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 * 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 = 999
+);
+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 = 999
+) 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;
 End of 5.1 tests
 #
 # BUG#50257: Missing info in REF column of the EXPLAIN 

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-01-12 09:52:10 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-01-12 12:21:16 +0000
@@ -6100,6 +6100,58 @@ ORDER BY (SELECT * FROM t1 WHERE MATCH(a
 SELECT * FROM t2 UNION SELECT * FROM t2
 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
 DROP TABLE t1,t2;
+#
+# Bug #58818: Incorrect result for IN/ANY subquery
+# with HAVING condition 
+#
+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 * 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 = 999
+);
+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 = 999
+) 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;
 End of 5.1 tests
 #
 # BUG#50257: Missing info in REF column of the EXPLAIN 

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-01-12 09:52:10 +0000
+++ b/mysql-test/r/subquery_none.result	2011-01-12 12:21:16 +0000
@@ -6099,6 +6099,58 @@ ORDER BY (SELECT * FROM t1 WHERE MATCH(a
 SELECT * FROM t2 UNION SELECT * FROM t2
 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
 DROP TABLE t1,t2;
+#
+# Bug #58818: Incorrect result for IN/ANY subquery
+# with HAVING condition 
+#
+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 * 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 = 999
+);
+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 = 999
+) 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;
 End of 5.1 tests
 #
 # BUG#50257: Missing info in REF column of the EXPLAIN 

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-12-29 00:38:59 +0000
+++ b/sql/item_subselect.cc	2011-01-12 12:21:16 +0000
@@ -1294,6 +1294,7 @@ Item_in_subselect::single_value_in_to_ex
     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)

No bundle (reason: revision is a merge).