From: Ole John Aske
Date: December 8 2010 3:40pm
Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
 (ole.john.aske:3394) Bug#58818
List-Archive: http://lists.mysql.com/commits/126334
X-Bug: 58818
Message-Id: <20101208154033.6B98A223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7692681504429297815=="

--===============7692681504429297815==
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-spj-scan-scan/ based on revid:ole.john.aske@stripped

 3394 Ole John Aske	2010-12-08
      SPJ-sca-scan: Cherry picked fix for bug#58818: Incorrect result for IN/ANY subquery with HAVING condition
      
      The fix is to ensure that the HAVING condition 'IS TRUE' when doing a
      Item_in_subselect::single_value_transformer
      
      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 above is Mc'Carty evaluated, the idea was that the
      right side of the AND cond would be executed only if the HAVING evaluated to true.
      
      This logic failed to considder the 3-value logic required by NULL/UNKNOWN evaluation.
      Evaluating 'UNKNOWN AND <right cond>' requires the right AND-argument to be evaluated and
      'false' returned if <right cond> evaluated to false, else unknown.
      
      To get the required behaviour this fix change the transformation in 1) to be:
      
         2) HAVING cond -> ((HAVING Cond) IS TRUE) AND (cond_guard (Item_ref_null_helper(...))

    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	2010-12-08 15:40:27 +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	2010-12-08 15:40:27 +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-11-23 07:28:24 +0000
+++ b/sql/item_subselect.cc	2010-12-08 15:40:27 +0000
@@ -1097,6 +1097,16 @@ Item_in_subselect::single_value_transfor
       select_lex->group_list.elements)
   {
     bool tmp;
+    /*
+      If 'having' condition may evaluate to 'unknown', we must ensure
+      it 'IS TRUE' before we are allowed to continue into the AND'ed
+      Item_ref_null_helper object.
+    */
+    Item *having= join->having;
+    if (!abort_on_null && having && having->maybe_null)
+    {
+      having= new Item_func_istrue(having);
+    }
     Item *item= func->create(expr,
                              new Item_ref_null_helper(&select_lex->context,
                                                       this,
@@ -1118,7 +1128,7 @@ Item_in_subselect::single_value_transfor
       we can assign select_lex->having here, and pass 0 as last
       argument (reference) to fix_fields()
     */
-    select_lex->having= join->having= and_items(join->having, item);
+    select_lex->having= join->having= and_items(having, item);
     if (join->having == item)
       item->name= (char*)in_having_cond;
     select_lex->having_fix_field= 1;


--===============7692681504429297815==
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\
#   eq0r7p8qmgpt7ddl
# target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\
#   /mysql-5.1-telco-7.0-spj-scan-scan/
# testament_sha1: f92d19b421c67b1ca2c7ac811a6a797b563c29ad
# timestamp: 2010-12-08 16:40:33 +0100
# source_branch: bzr+ssh://oaske@stripped/bzrroot/server\
#   /mysql-5.1-telco-7.0-spj/
# base_revision_id: ole.john.aske@stripped\
#   pcchhrs9dovzsu9l
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWZ3ghq0ABPDfgFAQeff//3//
36C////6YAorvtHu3tilL2KLAZXW5OzuSuzSdaBQMkJppTaaQbJNiTZQ0yaNMmjEDQAaADQSSg0a
CYQ001Q/U1Pap6h6R6gMgGjEDQABEEnij1GQNBoyABoAGgAAGQAEiJAJNpNqZNNU/UzTUaGKYmJo
xMmTIaAaAcNNMEMhppkZMIBpoAwmjTJgAQNBJITTQCaMmhpNGUyYT0pplP1T2qemQjR5QD0ynqLJ
E0whgX6oB564siWjS1USlDRAg9BRNUSZ0+qqXt099YSB8gxDlDt0ZOw/tHn52TSpOZzpx48YKN8B
bdFLnCLdeYICgqfBkvrHTIIWCTQGHZnKwq4qOvRjv6ozQyyDLSkXhxGBwkToTDAHAPmdLfSd395t
5Blzi4AoKTlNVPQB9ozwLqW9MzJmZlXoRq16HUXSTwM2mTTxht3TFLGdEmcPmbyAnMsJwPzGEcTu
ir5Bf2n4fE1BzApP8qnk0wW72b1rQJwpIqX2+QFH7oJz9T2PAoArBgIHzGDtS6iIdl+uvrNju97Z
yLj+sA0yBAAgzHFAbMVdLaCz1EVzlEzGWKkS3lKNIGOoPCSyMcjCqj7VTkm2KK6mOWKdQUnxLyAf
obg5xi6AeOLQMgHjxwGwBgKhaa69HcQyg/F0IzRcyzGGoGBqPZVOryb4dk5OHDCK1UTXcqn+U4sc
k4LEBNq8jG2vD1EHRPg2z1Z614boTTaZkmkHKwt5+sYT1aNzs7fdJ89aqh5jGwJxgmG92G4edwmm
PfUB8GuBaqLuu0wKQ6jYMaADWcbDceNmfE/QOQiqduRkaj3alRA+f09VPhNRSFHDrmM5WtxDkX/l
O6kIpKdMSSXcAVBgMhJUF6+navIv1uytXOvZhagPHCxPCNYjZhixPEiRGE4ncAOSXs+FAsSYoEPG
Ob0Kv6FUFBCpZiImXacJFEPqIiXbe8cJyVLhq4kGdwglZQBTTUGABpzVHIb/NPydjrqVeRlvLEKL
hNS+flsw3bjbRcaWrLEOMfrfmVmqoQypnKbx1eWcl6L5g0JlY4uVD8pEBYbRItzJF+4XaJFhuFxZ
ClC2WQlndDWTpJMI5iRFMnjTNaXcjFezczrRILSu0rYJ5KVFSPujVnrmjNWYeoZLK2J8SGosNUDL
LwqpWkcy2CrEWIyY5kdoVGeZGTUnQVkuECI1JAWKlugPLiBQYhw6sS+JE1lcpoV1lnH0cy8zJzdm
WCuWBwPs9Cul1mgwMNoQ2GgIrzTiCItvnM1EUlcz5BmzsZmFYOMN1ugtsRSijXtHVSmnQatJ4k2e
NDmYRtbAfQYdXDImxwKzQWGBSnOhZIrmJiFNOKIGsRrMC0ePO05CJz+Z0miuxq33Xq5hF6uJpG16
0m8vLq5yiwkuOJUVqhrcUchFwFLFdmLiqyRzEczVlTDEwDjACkfKcFTabVdO1Cnxxg254uZmx5Ln
atcBnaLJQ7HUHSZhW3PR2+ZtR4IEpO0NCSoDAzceRDbyW1KDf513Sh5oCpJd4+sD3gfMXEkXL7Rf
yIOOlxDY2x/uBVQkUAHqlciVC8wF6AgTD5qBXyAviAVAZgIFWKy4GDAwAvEH5AekoIqGM/sD4Aek
OMCoPiANcggsFALIaP2mF7XE4HyoB/gDxFq8oGkAoBliLEAnIMhgEgNQEEnCCYUwfYTxA6CWQv2A
NwBABiwHAMLwqNtgEgGA+ovsAfIeKCYViWQFQMMBYSBcBgZhYASBJWKoDOLQBIs4EF0AVKlYCju6
ZwqAYVQgkDmTxRSvA5bgynevHdL3Q1YLHlZisNODrWCstU0CJIHZHADAMacZXN/A/Hp+/5HvNR9b
7/j+qSsezhaf2i4+4jJZQyJ3Dsnhifee/3lmKD7uz8PWkujMO1Dbb5ZSPaSZz0cR3D3O344wm3Q4
6FgdJAbFOF697GHSoi5ZmER8RkQd9IhM+38QG8oNI80EJTpMpgfisdqYsdVZItwTxDJLjvQHJYj3
DQXLpLkla+wdH0kKL2IhPjWzOaZoY85YixEG6aJNGw752xTeIZXo28wXJUQbnqFJdbjuHQhFhd2g
LLBbvo9CwP2r6xOxH0SAEATSCxtTFWqRoY2cV5LepUl2J64FBOnilpISzitxDOupyR8+S6pxXjlF
LrgW0vqy45GfNgUKQ03DO6779jR/WArzpF65PSHOOi0OpMi85m8Q9TdnBzh3GrqspLJ4KjrpkhyL
W8RMWvVJWlsLPZcPqjm2iZy9S1du40FR0UhW491yVGk74cQa/9XBWpWFm2QuZ27g0jI+O4tBsJMi
YkOcafUl0lWgNg+c5asxHG4qv4LurGuAZ5bs5juw/23zxnA9zI23Ln5WKaOwAeGvvXgeAewOKYNg
MBiDITL1juObsLSJ727JLac03/KTrCgOJxQcpyJkVTegWGU5FyC0ljN6eiTwIszMD9wulCLBxRUM
GaQMpMhN2oK5qniloOGkkxCMU7YghRJRHOcDhvyFwPp1F+oyYPSQs4UFSZ3tSnr9XYtKSrSULxjW
gsSIm7HqvODUPbsM94VFQtBSRHLfj0GigvQDAbUV99Ksgb8C4Rv8rq/GmQUSLJtRYZ7NBIcR1Rbh
C3VETMVJLQRq3EVtFmCrp5U6ippiYQ0tERX3yoSozume4s6zZfaFNCTuhNoZjehyn18o6Q3gepul
oTcazotMGTZH9v4oCq3MPid4Mnc7BBxMQDW9esZI03OPIvLA+6S1icBYOWOUavqyrdpWruHJhuQb
DgI04MmR2k4Hqqpz3MX9cqHdYisRbqoOJ6NhWHmI7Coqt95krQ7cAdY7aQuDXpEVoRZxhrLi5ure
txeqkGxS15U9njgNBdO0oekjAqS3WeLzeX04wUs05zWRMyBDU5x/IDCsBww4l+cX2RIjCGAUkxTD
s4W9IU9S05iPPdrDE9JYQCnJR3QMBQWOtJeWCPT2nivFmZms7xuKy1sK24Tc1er1TQMw6cXqJajA
p1a1nuWxPgUynjOmKoSEWiFyBg/cYEhju4COZGySdyr8xMTObJlj7b2QwcO9ai8TQTgD1y7rNo+3
RJD2GI2o1MqdYoXHJrJpN8ou5Zg01rxGKUIXC2bBnn8V8Tvcol7I8aRVMO+Gh+FBJ5sm4407DoNa
Pk3RtK71j2XpE4mLz2+0kptRpT2DaOHZm9R9BZo7iCCgst2PnYMSDyJsT1E2FYVF2RRcU3LH8D/4
u5IpwoSE7wQ1aA==


--===============7692681504429297815==--