From: Ole John Aske
Date: January 12 2011 12:15pm
Subject: bzr commit into mysql-5.5 branch (ole.john.aske:3238) Bug#58818
List-Archive: http://lists.mysql.com/commits/128502
X-Bug: 58818
Message-Id: <20110112121528.00508223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7047946635933403007=="

--===============7047946635933403007==
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.5/ based on revid:martin.hansson@stripped

 3238 Ole John Aske	2011-01-12
      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-11-18 12:01:50 +0000
+++ b/mysql-test/r/subselect.result	2011-01-12 12:15:22 +0000
@@ -5004,6 +5004,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 #57704: Cleanup code dies with void TABLE::set_keyread(bool): 

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2010-11-18 12:01:50 +0000
+++ b/mysql-test/t/subselect.test	2011-01-12 12:15:22 +0000
@@ -3945,6 +3945,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 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-12-29 00:26:31 +0000
+++ b/sql/item_subselect.cc	2011-01-12 12:15:22 +0000
@@ -1116,6 +1116,7 @@ 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;
+    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)


--===============7047946635933403007==
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\
#   oqfyus25bhvz9lk0
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.5/
# testament_sha1: 7fa71b49ee0217fe7d81c3c832c8474fca08ff94
# timestamp: 2011-01-12 13:15:27 +0100
# base_revision_id: martin.hansson@stripped\
#   ay76i8bfo44j3fy5
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSkLAXoABIZfgFBQWPf//3//
36C////wYAkd82yvb3HTSmzNer2F3d73d0KDuzpT0CSU01EPSek8k9Q0yeUYg0BoNGmahpkAACSU
U9PKMm00ymmqfqj1PJG1NHpDQADQBoAAlECYVE/1U9G1MgmAEbJoA0TAAmmACRIRNTU9NTejVPMq
MamT1DIzUyaA0AA0DQcZMmhiMTRgEYCYQBgJpo0yNAMJJCYiYmmmhoJgJpMp6NKbKep6TTT9SA0N
MepMMK31sMNN+9N44g+x1PQbrvz6d7NuB8g0BsC2zdqu0bfj9zJppRaMchSUjBsdxlmvxqxB1VYM
FUBQT2pGfmD8rEoAGizoSDVtqhJ+49YQyy/HWEXQC4MowOcBVQi2GY9Pm0ZBTgrmng2IqQId5Z1Q
mZhm2s2ZG3Oy2ki47dJaNM5E76bUyqKSqfKBeUA9AxHN5AOFnM+n55g94FP+zjjQFutm81cJwph5
rN6gNYgifUcBICoGAeeowdAxIhxhl6DZcObG8zfJ4JRuF7C7gXXz9VfNtw7LLI8KZKeWapjUBkpL
yXmB/YwDYMLaDxx1AuAePHAYAMBTWJe8wzq3FxXL116vm6yFuwbXDgkUmYjX3og3zrfEcoiZvC2N
vgZntdkqMabWQ/LkhGsuCO7zbVJ4l0SeyCAaxgqGqkUHYE3EPAysBNbZWWjg1kRiIBeVlfhhUvqE
7W3ky0j6aBED3URC+BspqxMao77AikSC8dSJMtICoGAYekUCzroo3UKpeIxEA3UwBiBYMtw1p/MU
5KXyYmTGF8plDwB6AKkZIiljRP+RGtEjZYUIPTj0ZiWI4iTJOrsv4DgckykOBxpHFSyuA8YFLFQ5
Cb5a9Zu7esUJrBwzIUxZyMtJBCwEZhSrbDHwyOwLLVe8ciaH1fm6sz2iLlQrlJis01Lba7MF7OUG
JFqbCDxaUkS1GW7UOSGDmJFZCAmE5VjAiQJktHJ5q5CM0+6qnBgNBAnkGqJn5w33iQU3j4XijPbp
RKL7sSw1wKJheVDQK9FhfsNCt/hjvi8UEsWC8ZYmwzfYoabyJqGLxaO7lE4kx2u3E5j4ECwrCsGl
+liqMHMR0zMiyF5QK2FCRNbDafrDEeW01ZpSZiSAw/W3IuiG0xVMko3ZplEyTBtaspat4iKZy0dq
CeRHuJxRblBxyFhTkrjVI4EJvJ7tG6MKTFUW64l6dJFhnHMbqTEeVjj+nAyL20tC6ykZh6gV0kOJ
bDuaKYYkRUmzYxH7E+ZpwygSlsZxu4mOq42KBkzFRO8sKrKHOAd3AVX82qLiy4aPsI1wSsHhPIZJ
WVsWD6g0HrBqCqOY8L8hIxXBhxKqcyE494WIRx0ljMNctyTjf/hcPRjEkopGr4gUge34KulEsQvh
JOlbSUAMm9wHwjhQAGJ0ygZCsXr+gPEwMKpwCeAQAyAOF+oqUTBgIgJ4klnAyPRGxVGGP0DwAgwh
KA8PgANXJKgoAGwsYH+AfYRyAZQCQFKVIA60LGAHgMnBBOSVyKA9kPEEEsBewBiARAYrBwDIwyFY
EgGA9hbQD8h4KsNmAGQGDIJwEwKisVQDwHlAqALRXAPFrA2AZBZJBMBhTSTwcyeitLKBw6jVrW+p
MXvJT6CDWcXTIUk6zyHKc9SIGkTeF4ibzHUw4Mg/PYIkW5NMd98BHZfN3AxHF2xiqhYUpjy1xGur
lOjzpEJWqkDMSRLMYVGYJioUzuMebuOR+AQWp3I/p0WnIV8SZEpVoj8THdivmyCvidTyEYcmnppJ
VnE49tkjUPvkYHbJLXOBxzxC+hhJyAhgdLArDUYhKt2jN+kPjhQ229Cww4ieXPWipFCwOek0pQOj
3wek+R894MrKS/PwOTW3kNt4JQXb1xsOBnKDY27UM7X0XuWdIzy3MmuVIszqi+6/3BwURDfF+6Bv
g5jPLjt1MzRlaXm3Hme57aPxQVREYEBNXYyoTIceRRn6Ed+2jVkpHyguVMENkFJ5zWJyoE55BLqS
6U/V2gWG5tvibyBadom0j33SOBQdQyhq/Zc1MPAnSnpeOwgxWMeEIQXIR3D0zb5F5hSlwOF5Plh4
euXkbZjWgfYsDtFBopHcQUwnZpOGIisMkwMwDJMIOYTGhYzO4upvGpwilCh8+UHGR2NiDdumTGWE
Xx7hYR1K1+iHHbB4FDMzBTIXk7P54FxgldyPuyLy3rqt6Rv40CKR0gTsNxvO68XbMT35K2yg4ICf
e8Oo8dn+cFmEuqQ4XKIh1qr+Ah9O9lEYVpXIN/Num4sptQDIxWKKUydaPH6/Arm6iQRtIUEPYrPp
AyHpU8sYT0i+hP1jIHc01exRNuTgTQayJY4RxkUrz21pZEUxQm0NmCgV+bcqSBZVDOherNZRnf6p
HkXwZWuK6IKCVJWk2ED6MBdY74G27AbaIwRxEoC6vAmEvjjFdXX7fv26EG8iX4smQ1AdiFNfWiTq
7M5MRyMt5lFyKTnp6Sa5KwradLBsZUgiJmuRIfkDyhBgu8n5UhYVkoVvGAbT88S2WaQWNNyczUaS
CeofxA5VAOGPGDx4whgFqEMU19tllctSD20aM4bjiQW9sFmKJipU5pHPJHDnJLguDMzNp2I1XphW
wFcrlOYzERdo6x+bvWC0d1kMyYsy8mmguELiDB/4YD5sCBx3/aZZqdNdImKHcHFvoMu/ut7K88x4
nRGCGGVv4zRUrciEFRSFy2cF58chHJB6QvubRiFGCmHncqgXSL5fb71955QL2WuoDCg7xF2fL8MT
ec0aWLE1+2xPExYenQRcZVfi5ahxrqr7aB5pZKSo4ucxBZVgQIneigRQdRE3MaysKzUVWmSZp/cX
ckU4UJApCwF6


--===============7047946635933403007==--