From: Ole John Aske
Date: January 14 2011 1:36pm
Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4112)
List-Archive: http://lists.mysql.com/commits/128752
Message-Id: <20110114133653.8B97C223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7009482225022285907=="

--===============7009482225022285907==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0/ based on revid:ole.john.aske@stripped

 4112 Ole John Aske	2011-01-14
      Backport to mysql-5.1-telco-7.0:
      
      Fix for bug#58818: Incorrect result for IN/ANY subquery
      
      If the ::single_value_transformer() find an existing HAVING condition it used
      to do the transformation:
                        
        1) HAVING cond -> (HAVING Cond) AND (cond_guard (Item_ref_null_helper(...))
                  
      As the AND condition in 1) is Mc'Carty evaluated, the
      right side of the AND cond should be executed only if the 
      original 'HAVING evaluated' to true.
                  
      However, as we failed to set 'top_level' for the tranformed HAVING condition,
      'abort_on_null' was FALSE after transformation. An
      UNKNOWN having condition will then not terminate evaluation of the
      transformed having condition, and we incorrectly continued
      into the Item_ref_null_helper() part.

    modified:
      mysql-test/r/subselect.result
      mysql-test/t/subselect.test
      sql/item_subselect.cc
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2010-04-15 14:04:24 +0000
+++ b/mysql-test/r/subselect.result	2011-01-14 13:36:47 +0000
@@ -4733,4 +4733,56 @@ 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

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2010-04-15 14:04:24 +0000
+++ b/mysql-test/t/subselect.test	2011-01-14 13:36:47 +0000
@@ -3725,4 +3725,53 @@ 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

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-10-06 10:06:47 +0000
+++ b/sql/item_subselect.cc	2011-01-14 13:36:47 +0000
@@ -1131,6 +1131,9 @@ Item_in_subselect::single_value_transfor
     select_lex->having= join->having= and_items(join->having, item);
     if (join->having == item)
       item->name= (char*)in_having_cond;
+#ifndef MCP_BUG58818
+    select_lex->having->top_level_item();
+#endif
     select_lex->having_fix_field= 1;
     /*
       we do not check join->having->fixed, because Item_and (from and_items)


--===============7009482225022285907==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/ole.john.aske@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: ole.john.aske@stripped\
#   wmiy33cz0ej61xl7
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.1-telco-7.0/
# testament_sha1: 5501bb7d760b8e4fbc867feb7740ea1293bd4bcb
# timestamp: 2011-01-14 14:36:53 +0100
# base_revision_id: ole.john.aske@stripped\
#   6ulnj879s5ru437t
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWY9+nVIABKPfgEBRWPf//3//
36C////wYAk3zeU9d6ZAaDelMZ2d272AevRoUCSUyFDT0npNqNB6EyaPUNDTQZNNNGJoAAJSKept
RH6aU2Ke0KabU0AyBoAAAyAANJJ6NCaDRo0aANNANAAAAAACREQJTwJ6JikYntQJj0RHqYRk0YmN
IbQcZMmhiMTRgEYCYQBgJpo0yNAMJJEyaAAQ0AKaYptJqPaUND0mmgNBhoxK2qr/VhVTVg7VOiWZ
vdRcIYNNVFp814PIIBkDDZqRbduV3L4/zZNSlJpSsKioYOPBxlnrReDqKwc4KJhPT2vGfiH4VJTI
CxQDj4QhLW+P3zxtj/bcEXTBcFMYHOQRoROGc9PncO8VcFbkv7FLIA+haFzJmYbbXJyBr5dFmTG2
TdZWYu3SXsiaPtlKPoA3FQPSMRzSUDw5eB6P90h0AX/E7m2oZtzfFaBQK4OKqZQGkAvNGYiADSAp
AwDjKMFwJyILT5bg9GvgxvFvn8MxqN7DZwXX09Vv65od998ZPzUFK56KzsKAtrPjXED3GIbxi7IS
Qd4DQBJJAGIDACzAJueA5EZNcQy20oZRM6FJDwsVzKhdYPuvpIfK6Y8oKGJbuDY2+ZkGOl4Uoxpt
ZT6OUI7xiEflyYQmkvk9vg4xD5DBAaGI47iaYkBrWCa2uotJwngSiiUAmTIlQ/bhUdAXbyrCclMi
zFaYE5KIaRJdqeJwnMid1AREpJjOgS3AKAwDDhKCgyzXgn3wVCgAeslUcxlzG2CjAgQGE4IuHMSa
FndopLiC4kF2/PQ6ipbZh+nUbLBWK8vin2EVJ0Y8xqFCWmFAM2kF4wPFIZFi8ELP/W85efWdChYn
ITpaE6l7TbgBLNWOlbvhpYY2kwhqTchyFA/G6ETAKZ6MHcGNrl19ZgtTE+Ty1TbZDxaiRkIv3mGh
vFvEio1FqJu70pwmJ3re85KrcXyJ9lzhfjCefASCO/EtOmGQuEaJrEl1EZTG52JDdWIxKVBGwYx1
iIoMx2B0Ja+2+obE2CwMxg3N5DKLCOA1Y4rLD81tORpJ/YuL4DqRXpG1TaFw+KaBSSJ9gdNzr6Sn
8/qJU74MwbzsXmAorAbo2kXM2FZIUPiRjigYHDwJnOaSuIxxSXlW8aOZO/CiSaG3oIhbEUIiNBx5
e3GfQgW7M0OF3HZ4WHDeth6FVRsutc9ObJIkResiD6jt0kT4l5WbFuqKZiYtoMIqnJFE5AqIabDS
HY8CkuX0PQoWBY4lcFpXznIDjAcSxHnJSDboUlavbN5a1WRRQXAV8oDlaz/dJ1ITG0kBvAsta695
GlNbnUnW1uVT3Eq8LXaGEBRhZKHY6h3bhXXPueTnbIYlB4wyoRXlJBmaVa6UBYzq1eYhhErZ0AYw
PX7FTS1FhF1JKdLwCAY/mB177bD12CkA/enKqGYzi9v/hImDFlgBSAUAzAQL6ivRcDAyARgkn+QG
OJEjEQwx+gYQILoTAQhmAGVZJVFAA2F7A2APKjyAZwCQL0rwCDMGVgEgNQFFCSzoqHWiRBRLkF1g
HKARAYrBwDI1qP0sAkAwDhcQD/Y8FWG/MCoGCoTgJAUlYqQHgPICgBaK8B4tQN4FQs65BQAwqEk8
HMnokl+GAHW+cFC3k5hZRz8BAUF3G4Tk0Z3DwOUvOMWl2Hb1l+5g0YjVIzomsZxKTX09Fl8Iq21l
/ggW3HEzIlKxk8pRF/F4XeYRGDmi1KKLEyjyEZliFmHQqWS0F88TXG3Cb7z7h+S7MghxOh6CPHr/
RxgQHGGZn4GnJTGLE93K6gMXMkvMoQGWR82AkHIoEpNtEfUOOcXNU5Q229S26DbrrvGapLkY3Ikm
aJYT7S0pOfZHdBpZpoq9sOJlq0WeFyarCWHpJPiuzbU5VhnHVGnuE8dtULISrKt7hr1ULB2NtvAO
igIb/M/W0wtnY5q406+MFFc8CQaTljRCbG2D1L+FjIyYQpFiKzPC2ikJBZHRDbwQ1OTchLiAg5VF
jkDMAkPFMDpwSUAKETQQrHed83cRYeJH2lhTvJgsYh4rmR7BMWhp+i7qkKCFD0vdtHsUDHnNqQTN
vgXDGE6XAqJ+OHl/e3ieUhqwMDwggxkO4pKcJ6+55e8NyYGYBkmEHvExQtmRs4jOzuSsdP0yYPGR
uNyDX2yJDY+D48RZx2K5+k3TfM8CDMzBROL3Oz9dC80Sv6n2uEVLz5LiI4LkJZD+ZXJJUcTmcsRf
CMpDud1TXA4JhP2IMpnMnjLLr9nsVYk8ScLtEQ65WOlyYUBhWlUQ5dm14FdFiAZGu5FCZOtHa7BF
cvCUgnuJvEidI/TOeqpmOfsSaGKRLWVn8BijInbxUUblwJ6b4A8CaDI95Zqo07dJBSEKHJmQzHBO
XnZ3jiHEOzOfZKRXkRrGb2EpN1epxgyg5KQgtFUAmcdrAS2O+RdkNsR2Eni8XgYk4ezm0V464cPu
3+xBzImO5kyGgHUmonONnhPQ6y3udS/MqFUSO9f7jNZeGIVs97BzZPBELkZE/AJxyDYvrWnB4WFZ
PNW8YB7PPJtM1umiMruiHxVnKVVVX9gOGQCBnipJIxDAL7ExRZ24218NUH027dA5ncrOsQ3KcdoJ
600EuGuaPT4HovRmZm/gW9UbUrbhXq9SnGYiLzI4j8PJZrTr7LYckxbrCSC8QvYDB/8YEhjx+RbJ
Tyq+QmIRH9nj9C74jLz53dVkOE0Bwj66dLtRr8ZBY0yzVrwLH6/TeKisqrNtJyQjJDGtfHQsAXCs
29PwX2numMWW7YqkGkS/K6JwOqzYqTX+tSeJio+PzLS1OvcsczoI2nx1s7l8fMmPUQyVCh3c5iQj
aXTETyRmbxDe+keUBYbyjAp1/UXckU4UJCPfp1SA


--===============7009482225022285907==--