From: Jorgen Loland
Date: April 15 2011 8:29am
Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3448) Bug#11765699
List-Archive: http://lists.mysql.com/commits/135489
X-Bug: 11765699
Message-Id: <20110415082923.6D2713A7@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============2901836007217530484=="

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

#At file:///export/home/jl208045/mysql/mysql-5.5-11765699/ based on revid:serge.kozlov@stripped

 3448 Jorgen Loland	2011-04-15
      Bug#11765699 - 58690: !TABLE || 
      (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
      
      JOIN::conds points to the same expression tree as SELECT_LEX::where, so
      modifying the expression tree of JOIN::conds effectively does the same
      modifications to SELECT_LEX::where.
      
      JOIN::conds is optimized in JOIN::optimize()
      
        conds= optimize_cond(conds);
      
      optimize_cond() makes modifications to the conds input parameter. One 
      of these modifications removes an item from the input conds parameter. 
      This was not a problem for JOIN::conds because removed items are 
      included in the condition returned from the function and therefore 
      assigned to conds as can be seen above. However, the removed parts of 
      the condition were lost from SELECT_LEX::where.
      
      This bug was about a query with three levels of subselects:
      
      SELECT 1 FROM t1 WHERE a = 
      (SELECT 1 FROM t2 WHERE b = 
      (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
      GROUP BY b
      );
      
      Prior to optimization, select#3 has 
      
      join->conds = select_lex->where = "c = 1 OR t1.a = 1 AND 1 = 2"
      
      As part of optimize_cond(), remove_eq_conds() sees that 
      "t1.a = 1 AND 1 = 2" is FALSE and correctly removes this condition. This
      leaves the OR item with only one argument: "c = 1". With only one
      argument left in this OR, internal_remove_eq_conds() empties the argument
      list of the OR and returns "c = 1". The effect is that after 
      optimize_cond(), select#3 has:
      
      join->conds = "c = 1"
      select_lex->where = ""   // <- !!
      
      Fixed by not emptying the argument list of an Item_cond even when there 
      is only one argument. The effect is that after optimize_cond(),
      select_lex->where is an OR-item with only one argument: "c = 1". This is
      slightly inefficient (evaluation would have to go through the OR), but 
      it works correctly. Note that condition evaluation is performed using the 
      correctly optimized JOIN::conds.
      
      Note: This fix has to be reconsidered when the optimizer has been split 
      into separate phases.
     @ mysql-test/r/subselect_innodb.result
        Added test for BUG#11765699
     @ mysql-test/t/subselect_innodb.test
        Added test for BUG#11765699
     @ sql/sql_select.cc
        In internal_remove_eq_conds(): Do not empty argument_list() of an Item_cond
        with only one argument. This will result in lost condition parts in 
        SELECT_LEX::where/having

    modified:
      mysql-test/r/subselect_innodb.result
      mysql-test/t/subselect_innodb.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2011-02-17 12:41:25 +0000
+++ b/mysql-test/r/subselect_innodb.result	2011-04-15 08:29:20 +0000
@@ -254,3 +254,21 @@ SELECT * FROM t1 WHERE b < (SELECT CAST(
 a	b
 2011-05-13	0
 DROP TABLE t1;
+#
+# Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+#                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+#
+CREATE TABLE t1( a INT );
+INSERT INTO t1 VALUES (0), (1);
+CREATE TABLE t2( b TEXT, c INT, PRIMARY KEY (b( 1 )) ) ENGINE = INNODB;
+INSERT INTO t2 VALUES ('a', ''), ('b', '');
+Warnings:
+Warning	1366	Incorrect integer value: '' for column 'c' at row 1
+Warning	1366	Incorrect integer value: '' for column 'c' at row 2
+SELECT 1 FROM t1 WHERE a = 
+(SELECT 1 FROM t2 WHERE b = 
+(SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+GROUP BY b
+);
+1
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/subselect_innodb.test'
--- a/mysql-test/t/subselect_innodb.test	2011-02-17 12:41:25 +0000
+++ b/mysql-test/t/subselect_innodb.test	2011-04-15 08:29:20 +0000
@@ -247,3 +247,22 @@ CREATE TABLE t1(a date, b int, unique(b)
 INSERT INTO t1 VALUES ('2011-05-13', 0);
 SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); 
 DROP TABLE t1;
+
+--echo #
+--echo # Bug 11765699 - 58690: !TABLE || (!TABLE->READ_SET || 
+--echo #                BITMAP_IS_SET(TABLE->READ_SET, FIELD_INDEX
+--echo #
+
+CREATE TABLE t1( a INT );
+INSERT INTO t1 VALUES (0), (1); 
+
+CREATE TABLE t2( b TEXT, c INT, PRIMARY KEY (b( 1 )) ) ENGINE = INNODB;
+INSERT INTO t2 VALUES ('a', ''), ('b', '');
+
+SELECT 1 FROM t1 WHERE a = 
+ (SELECT 1 FROM t2 WHERE b = 
+  (SELECT 1 FROM t1 t11 WHERE c = 1 OR t1.a = 1 AND 1 = 2)
+   GROUP BY b
+ );
+
+DROP TABLE t1, t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-04-14 09:10:11 +0000
+++ b/sql/sql_select.cc	2011-04-15 08:29:20 +0000
@@ -919,6 +919,13 @@ JOIN::optimize()
       thd->restore_active_arena(arena, &backup);
   }
 
+  /*
+    Note: optimize_cond makes changes to conds. Since
+    select_lex->where and conds points to the same condition, this
+    function call effectively changes select_lex->where as well. This
+    needs to be reconsidered after splitting the optimizer into
+    properly separated phases.
+  */
   conds= optimize_cond(this, conds, join_list, &cond_value);   
   if (thd->is_error())
   {
@@ -928,6 +935,7 @@ JOIN::optimize()
   }
 
   {
+    // Note above about optimize_cond also applies to SELEC_LEX->having
     having= optimize_cond(this, having, join_list, &having_value);
     if (thd->is_error())
     {
@@ -9441,7 +9449,8 @@ optimize_cond(JOIN *join, COND *conds, L
   SYNPOSIS
     remove_eq_conds()
     thd 			THD environment
-    cond                        the condition to handle
+    cond                        the condition to handle. Note that cond
+                                is changed by this function
     cond_value                  the resulting value of the condition
 
   RETURN
@@ -9503,9 +9512,34 @@ internal_remove_eq_conds(THD *thd, COND 
 	*cond_value != Item::COND_OK)
       return (COND*) 0;
     if (((Item_cond*) cond)->argument_list()->elements == 1)
-    {						// Remove list
+    {
+      /*
+        BUG#11765699:
+        We're dealing with an AND or OR item that has only one
+        argument. However, it is not an option to empty the list
+        because:
+
+         - this function is called for either JOIN::conds or
+           JOIN::having, but these point to the same condition as
+           SELECT_LEX::where and SELECT_LEX::having do.
+
+         - The return value of remove_eq_conds() is assigned to
+           JOIN::conds and JOIN::having, so emptying the list and
+           returning the only remaining item "replaces" the AND or OR
+           with item for the variables in JOIN. However, the return
+           value is not assigned to the SELECT_LEX counterparts. Thus,
+           if argument_list is emptied, SELECT_LEX forgets the item in
+           argument_list()->head().
+
+        item is therefore returned, but argument_list is not emptied.
+      */
       item= ((Item_cond*) cond)->argument_list()->head();
-      ((Item_cond*) cond)->argument_list()->empty();
+      /*
+        Consider reenabling the line below when the optimizer has been
+        split into properly separated phases.
+ 
+        ((Item_cond*) cond)->argument_list()->empty();
+      */
       return item;
     }
   }


--===============2901836007217530484==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   raetp2emw9woajsz
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5-\
#   11765699/
# testament_sha1: fb9a96368d2364c8b9670eb9fccbcc8460656987
# timestamp: 2011-04-15 10:29:23 +0200
# base_revision_id: serge.kozlov@stripped\
#   vcx60wwq2b1uj1oe
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWb7oSvcABfP/gFa0BAR49///
f//f4L////xgDg3Pn3s4fLu13bz3nSgK3Aaeve7Y6by06tu29DPaV7nTZrs7ueEkiFT9I9NNUZtU
/U1M9T1GNT1E9J+pDBBiMaIxMIGASUCDTQaBCnkaFP1PVPKNDanqNAZAA0AAAqm1GgzSYg0YmIMg
AGgGTTQAyADIBIhINIk2ibQ1MkwmTNE2U0GgGmQAAGg0EUiGinqeiZNDRMmyInininqaB6mnoGpt
TIeoHqHqPU9QSKCGgEGqeU9J+kozTDUmUwekEYE0NGgwQDMXKNysIWU0ygwz4c/ET3HTx8TPLjqd
OSwKr3U4/Xgd0G+MIyuWWOrU8AKPHhxPskkgAovqkYMARseFW5B1hUKNlEwZY12cFNoom01t4wob
v1Wks7OxIQLlxh7cLbasCbFn1HceMVujqpY05ZY490X3Wl493dlXIxAdacfKaesgD83ViptvXtuA
QUEl5TCGTCQ/h4rFZQP03hA3BsNp/EiMZdd+Bu46jJfeVdSZtX916kxjG0Yv4Tw04ekph7UzpiQg
D5iqvhvb7Elx1le6xVBOVTq3DiNlWxXB7tTX7WGNYxORQBmo+5A5EOhq2HUrDt9BT0q1B5+i6jMz
OYZLHo95HuH5LSe9vAHlVPIk6T8Ojq4HkGcSJ7jODXMxQ01Cpdh6S/IziZH5aGBQ8DLpy2WsLHH1
4VZuvOciutZcyxWT2reMVBalo+M1dnOBhV4m89mLdWHhvB6raO+j0b1aZIwsc3AWjX6teFrY5O50
azH1zlljDdm886k4N68104b+FiXaYL5cHLbHlNeUg6ibxfRMN/eirZETFCi7arC3P1t3ezzqVrfZ
KW12psLmsKRpztY4qjLF5S/c4iyNgSHInWXxusHkbbf1P0F0BoHWnj08qJkxWrA27Xyhtm2G/MYl
RsZYehWC2W1Vfr42rPu6r52BvDeelVdlEuAhkhINqJz4JSS5DZ6WYb6aoYVXRPV8bYde/dlMnwa5
7/mo6ePHbsXnFW4oYNoPGDbG3y/BZGJ6yYmHeYnEKPYk1m0o1MPPhvmzXEKBx2cA0eQ2c6gwZV/F
ngSP2zRAew4nSJRhO6rCUdkycPjVcvqLL6wYXooaza4lxhxZpRaQIfXavmMA+LgsBdoB5TmpdwsL
RtxttGkrQjVXoa/PMUFtIjUfYg9RdWCa2SdmjaGWD8ndKfsqu2RjKGOb4Imi1AUK7YqiR3CPSuw6
+wf2w0pbJv5nJhMx2q88OtxnIJPoze5qWdrQJgEIWwzHCQtlM9pKjAnTJ9kOlkMrXOb+rO5lXixM
H4IvhFNDaSNiMk1AM2DJaOa6y3rASmCWZP26lC4TACr04r0XDDmyNmpMVJHHTSrZsyzZ9RHwQxYh
/eDhFCtyVlOBBLt1O2n5Imh6OMJbl5ZGKxWhA1rJIP9rkrTHmiAxrmIwFmwCO6RqBvKoYGAwTbmk
R2DzvUYxEbZGcpCvNpwwP8Wegi7KuhyizqH6ubAU1wc0UDGZmWFhwYnldFZQUWTocgsuoRqPc6ly
8cKsyt4o4MMC8SZhP3SchMjWQTmqA9S3nCqkwurMgsLm/6qtzl/ypuj10mfTQXCNFSOqpG4lraU4
7FlRXtA0gWDKCa5arMtVY7ReF1pr2Jz/aWq5LpVfcpHRa91z89ni06r1HOzvr03QSoHkXvOfQQKV
aogM31OYEJuGDrsKn9g+SxvYpJppEnv5zHki2fTapJXG1YmPHpiVklVZ5OT0RlvGFJjjM2YzpwIl
ZouCanKaoxNzq3sWq7MRwjdX0tuK6zKhUyzIElvOptrzdhQ8iZ0XwqYqZKlag268RUroFA4yIRiR
KFIlzLVznJUKyxFrJjyg4mJUZWcj6RGXWRZcuoeeDB0tGrwS91iYi8hGBa3ikyCxyemxeNggLOrW
siVGPGqmgw4yrizu7F0UbyDaQbXQECuvGvr5CCNh2Vm7f0u6hxQBYcgUU9KvSPd4BiX70qDYMuOI
ggIOws/U7lZHXDbBbQJiFXCvwIaj/W0bajGHue2BsVgTFIQ72oQod6Eg8AhDI2IXHgURwo6pOZ/y
EnZcRrKeL2lZQIAm+Z2feVHZHfBSIiUFcG20VgQtQzpVehldpGpYSwgyEA2N/xoFZ4qZFLFIYzN5
IvbpqXof4PiH9myhuG02BcMDxRWwUlsYnMmGiDROcXIMQigceYgL9hXuATLmwLgJpfBEGAZ3MzRq
dEsADEWOVzOcvgZvlYePTEDHwCS8fOyYEgYU6jo7JI9KZcKGDuIQNtcJG45N4+QzH1GHxcbjkmkC
K90fggYZesj1ejssEx3toTHP8RIMRqY7iot9Q3BRoeWYVsi82JpHAIYbEQaB9j7Gl8H7MfaqwnR/
XBEQQwebjY0QRrWCj7Tsb6+xmW1o8HDoA+Htm0cQjKQYlXzHNedjM144/Kb8ZhKFLLWxSix0JGVR
CuWoLuimDOXbxILpFaLaYS26UXetT1BQdx4bYXcxWWazCLNoCqMkB14+PzsCGwOgqGNjHIdqJLmR
tlfmqEKgaNH0KUQxtjTO0whpsbR14CzUn3J+S/Lj2w4e480buMkvrd0bjHWdFFmppE1jqM27ulK4
PO5Q3jlDUC8Di0stlwLkguFgRjPFR4vH4mSyEc/VJJQYzGFJuWQ9yZPFGDU881QZVzjlYMJovPT5
MLstAg6FMLhfI1NYHcSywJdS4X8TzG6wsM1vVVzkyEkrAzFeLI6HaHrhoER4kMGptbfTW/buojIu
GkFfHe5kDAet5u40wlDk5mWrK2/jE4hGBjqrh8PHnbxZv9ZtySO/Pir8WIyQYIoyPoEW8maqkUi8
ly5MuaGmjBQiyfiC+fAo5noZKCaiyWxhQzvXXaZBgnGzlud2cs/NCEtdiuV6ngbQmTSg4KBkuAik
haerOG5SFFESiVVPdMwwaOXNBhDdkd9nLeTNTWw7VYWiXYBaEywl5N9ra/O9zzcrDtBhxMXE1vIa
9yC8qlos6png8govONA22ed0XoV6uBsUoApEiRZTj80kzX8VtqOfmco6Y3obnIz+QC+TA+RsFHg2
r8aBeYaE2iq9pSMQ0UNtsayC9LhmYVdS1SJLYuNa3G1N79OgjRakbTY+9+M0qJykUqJYqELGABsA
7T3sdRWQdfMsGW7yljrHQTuOQi5rYtPFJ7PYrRI1jBtoa4fc9tXGxv/ohg1W0QMhZpEZMPa9zPIf
Cw83ID36WrI6vumbUwUViyTFZugy9dF7REbxhJHmTYV2oW5sqrZITP2E9Xy+DeYTFHlFezIqRj2A
qldpcEdyAgg43JIv6/gHvRfnGWUCY7IKpSKUnHDckWGiqmNzWwwiYsPZtOfOhDvE2+I6pVMTGMA8
FWoRDEmVqiTwQuAbZD6mJW21HYeu7RvAC24L4oKmIiqzPOtMXqHmmVDwIZ6GRTgqWnAYOXiyhkpD
nEhSN2NSwxeRkE4ppsYxpUKEiAIgU9sBOmNjenVlZCFGMLCUFayE1xNUZFJkxdaxCbEmGDUE00c1
zUV6/JkS6BAg0RB1QBhbZscgmPIJMJdLZlA3P3OB69CmguYEMwDCCWjwQs32VFYVaIL4IhftYysE
BbmP/IBiiffmkQ0YXo2VLM0F9yOvu2206GtFA5AWE+132rfBxCbDoduTCQ3SbAnjccnBpl1Gply7
K/jIHOUIKSSYjU0TOCtCdC8YsMAXKb1fip1RabQw4GQMlrxsk6oVSgPAIYsDc1DsL0Mlhc5EQbNj
qR6mNhkg8vSthUDD5Hu78BRGCE6CTt6H75yGHLUiMrG/rhjn6VdFbcClyLw1Hh9kimSUx6+sSSO0
9ty6IG6fdqA3qmXFpVZzJY9MQZ4kjMXDl1hE2LU6sjfgUMdm802jrg6Xb6gYBjmrFIeGaOkRtdzY
12g5eJE11NCxaLFXM0FJdm43tY2K/i+teh4QhCEIR+d5fGpTiqpGgbUzM1gOAQaYrBVqyyChCk1P
ReURvyvu0mdcwRaYkmZUKd/k5jKzcGRHdeZkOsMrYjXJYIojm80LY0l81Rc1WkmirtEzKbPAZKJK
+owA+pjI7BuVvE4yM5cSmMsTcuq1K+O7JQqgF6bbO4uXccxIlv7S7bygpkDGpMu/C4Vw60hjRc2i
lUItYnWVBFOEXIoXFqRS5vNxGiR24U2D/J9J1sy8yovHXWLuQoE08IQ1tFq00toZk1jbL1cu43Ks
ND12h1qV7xYquA7U1Gb2+OxNSRMV6wCjyiYBJh95uwwHoZmTRJ6hIOFvOd7HNjTX2eBq5Gs8rC7d
DfjehScpyidUtp32ukUc6VIbAtCQSewUqTArulorGrfAWAXmLuSKcKEhfdCV7g==


--===============2901836007217530484==--