From: Jorgen Loland Date: May 26 2011 11:09am Subject: bzr commit into mysql-trunk branch (jorgen.loland:3122) Bug#11765699 List-Archive: http://lists.mysql.com/commits/138184 X-Bug: 11765699 Message-Id: <20110526111000.DC44810E4@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0386533980700221836==" --===============0386533980700221836== 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-trunk-11765699/ based on revid:sergey.glukhov@stripped 3122 Jorgen Loland 2011-05-26 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. @ mysql-test/include/subquery.inc Added test for BUG#11765699 @ mysql-test/r/subquery_nomat_nosj.result Added test for BUG#11765699 @ mysql-test/r/subquery_none.result 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/include/subquery.inc mysql-test/r/subquery_nomat_nosj.result mysql-test/r/subquery_none.result sql/sql_select.cc === modified file 'mysql-test/include/subquery.inc' --- a/mysql-test/include/subquery.inc 2011-05-05 07:41:53 +0000 +++ b/mysql-test/include/subquery.inc 2011-05-26 11:09:57 +0000 @@ -5354,3 +5354,32 @@ CREATE TABLE t(a VARCHAR(245) DEFAULT INSERT INTO t VALUES (''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''); SELECT * FROM (SELECT default(a) FROM t GROUP BY a) d; DROP TABLE t; + +--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)) +); +INSERT INTO t2 VALUES ('a', 2), ('b', 3); + +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) + ORDER BY 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 'mysql-test/r/subquery_nomat_nosj.result' --- a/mysql-test/r/subquery_nomat_nosj.result 2011-05-05 07:41:53 +0000 +++ b/mysql-test/r/subquery_nomat_nosj.result 2011-05-26 11:09:57 +0000 @@ -6507,4 +6507,29 @@ SELECT * FROM (SELECT default(a) FROM t default(a) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DROP TABLE t; +# +# 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)) +); +INSERT INTO t2 VALUES ('a', 2), ('b', 3); +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) +ORDER BY b +); +1 +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; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_none.result' --- a/mysql-test/r/subquery_none.result 2011-05-05 07:41:53 +0000 +++ b/mysql-test/r/subquery_none.result 2011-05-26 11:09:57 +0000 @@ -6506,4 +6506,29 @@ SELECT * FROM (SELECT default(a) FROM t default(a) aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DROP TABLE t; +# +# 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)) +); +INSERT INTO t2 VALUES ('a', 2), ('b', 3); +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) +ORDER BY b +); +1 +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; set optimizer_switch=default; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-05-26 06:03:02 +0000 +++ b/sql/sql_select.cc 2011-05-26 11:09:57 +0000 @@ -1904,6 +1904,11 @@ 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. + */ conds= optimize_cond(this, conds, join_list, TRUE, &select_lex->cond_value); if (thd->is_error()) { @@ -1913,6 +1918,7 @@ JOIN::optimize() } { + // Note above about optimize_cond() also applies to selec_lex->having having= optimize_cond(this, having, join_list, FALSE, &select_lex->having_value); if (thd->is_error()) @@ -14711,7 +14717,8 @@ optimize_cond(JOIN *join, Item *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 @@ -14773,9 +14780,34 @@ internal_remove_eq_conds(THD *thd, Item *cond_value != Item::COND_OK) return (Item*) 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; } } --===============0386533980700221836== 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\ # hri6lm8atmw2oh46 # target_branch: file:///export/home/jl208045/mysql/mysql-trunk-\ # 11765699/ # testament_sha1: 2267a9a13e7eee26ffc234283c7df540be8595f7 # timestamp: 2011-05-26 13:10:00 +0200 # base_revision_id: sergey.glukhov@stripped\ # qu3hi0klo14vtnzk # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWd5VKUYAB2F/gHaUZAR49/// f//f4L////xgDsvvIu71evrfR83lfd7oW1B9cAfM3rdO9euJb267WPWu9najnblaoJJCBBkxMmgp +phoU0elG0j00QADEZBoABGkmnqniT1R6ZqjZTQGhoA0AZADQAANAA00JlGmip+qBtqg0NAabUAA AAAAAACREISek1T/VTZNA9CbRqn6QaT1GmjJ6mjaTRkaDQaGIIoiCaZFNpgUyaRmhMhk002oDEDQ NDQ0NGnqCRIQAmQJowjVBmgiP0moHqPUyGmmmjQAGRkD7WELQcLOFeFkjrJXSsOv6sDmfjtHDeqU f+WHub09Z14PtoOrMzpkpXqdFRn6+iQnuv5lcyhCVen3qvfz12QooT0RWzwDO4edDsJHUYFEXKdc 6cA7UTfBxXKhIoOg6EisTroSSFSpJLMSACDFkJc9lybe/qpbVDtoJeF9lDas+R/q112hBot1t47A zgkgB6cKmnpLpM76R99b4Sp/nrpogEZiS6Jk3sYEwYe3ptW1ZxfsDavrWaaa44LA+1bOUidEzMP6 B3g0mxjbN39heHXrGFeHzoN6D+KEMFKfq+RSlcy74nBDIfJnwQbqi5qE66VGGFAe5jfSr5uDuexj GDDUbnILgUqB7IIXcV7ebJjkuMtgTIR1HYsqyrYB4fKSrIOpMcdYzkIiN6VUNb3WkS8JwYgIisGC oDwgKIURaIm0MsRVht/TXAcGBM7juiRGuHoQX2EDAIJGSHAQJAESMNNr2qMVTU2Ji4ktxWmqK/HQ bpDgTbC4JpRGW33s1KVk3jctCxJgl1M3OuD62xdxJuknIs1xnuqr+AwtrmDKRcMnqwCAjGJ0rIlE Ogq5JDRTaz1bd02XWIXEAvkphRyLGcsN5Il9TCsYZ0lOLHNownCZM/7PpF5B2Acy1arVtdDoAvTL TN7U1Z6MqiWXMi7AQlN89mrpLUv41q3wIyODObmTJOmEyX3RLKp4WZ/GDX5fBHtD4uG9/J6on59s j04jND5r/lDVpx172WK4kr2SHPCIYKIY2DY+v+YfEYLWtwue5fRag98+eYy6IUDjnUrkwufveoGm HQNikrWVZLxW+A4T9zFYnNTXokWsLb325zmFpioVCXsVnuLbbBXakvpFIKyXd1jzOXDFUKfVgvoN AujZvKzsFo2C7lySDt8QFVA3+syPSvIHxqx4KPG/VtWldsi4v6CoDiuC0FPblN80tEPdkdNJF5Cq D4Q/lZqt6CyMARffmRmQmsKg3Zcq2GrYP72FrYYSEnhNVJyiOTfmAepvivuvlbNj2W0YXayyoQJj wcLHO5WpJcy+ZlkAyLMbhuLwvboSLIJKSCauVEr6KulliDWXGFrs3hWpajKrS5Q21FzCpfKqbRJW FmpcS0dYwDxdfJD573aNGScARZ7goiFJV5p3gNAImkQ42gxKbG2BSEZHTTPta88idngL2CxC/qfm tVmIWtWE2YyxQqTCuQaptB166Q8GF73lStxe0KjdjIrNwazlArFrX3LDdlacxEkMCW2OUINrR8GN uu7SYuk2HgrZoL6mrIgobqr4QFVRkYWKXK2RvolrVD0GHZ+cFFl2dMOplktSrpkHKFuK6jlVGMmR Q8IwuKokFm0kfHWWGziuOBOvHWjBwHY0VLA26GVJD6uZxJazuGgmnQx2wUGQMy+bBXokZ7it7VqK i4obUz7uOKy7aEsHoLeSwa8SrVgN5AMtpaX9lyz7sxr11vQrdYDC4xHHEtZGurVaczMaE/GPYcQN XYv+UZkqrayuKDPZA+HWuEgoTSwnt2UoWJ0FJBPUVHA0DFBKotjgyJ3bDKua0Ezyi98y5Ar47ltV CkWUFnWQHiw3XxFcZSqNCxTpUt1qDQwUUtHVcpHHjJVaw0y6zJi0W6W88Nnirl6VQ1UeehLrtvkE E8TcRLbSgzMrDM2WFQUGOmz5gVZgbC2s5HfLVjoDxTuxGqiEXGhWrK0leVBeXFhZ0vQXD1RlFhYU Kw6F4U8C3z5J+VNNtA8kv1tNGAWzWyOiTmGrumotbdAqCI3t0mAkgLFEdGdBLDBEYX3ml6CmIBBQ G/5UMAWj00WobOQbEpDY+PkHqSObA8W7pbkfkROgD1ewdKn2LUdjxnUiIISD0+l9SJe/VC3w33DR Fr2FPIwFX/QvAv0jav5wLYiQLlKL6wqi+h9aJI+wYBoFgF77HARxuCST/kSXaGT6VOkLRsBkPCGy h5Y9drMRMFYdwWokItwGxa5kTSGsWqkClX7qj0qaAGoBkSE8QZmamYIoZ2ACET/AOkf7eNE3P8Ba OZgd+C7RcXbQk5R2A7ApoRNQ4AWh0QQSFiH8gZwyGjmC4cwFHPCZDvDWFFHrxiIaGxdQaw3gQGkG HKcKT9IQBs5ggG0vHAj7hDS6D5umESBkl60HZUHN7E7hHkOg6R/Z0DKHkOnzn4bucN2v7fqBEvn9 sfUVnBFSXJXFV/Er94WBUFhxoeLrZN6mE3CFwCMc1DSGvM0vm8sqBXQ+yIiIY2NUJA42BPoN2l84 P4El+2EzPe1Yq8ZPYey+h65tL78xwiMN/gKj1z/YvUeutm3MSW6/vxmpMDwVTBIHKwcADZKwESc9 /aIdWRn5jW7pkrg9tlO8F47S/U4voSrsVgRRsETVgIzq+j9CCGzMafiVljYx0DhmWjV31nQG0DEx sbGNM0sUNNppYtQawfNY79fq5nA3K8g4EjAXwLBZYCgkT6kiupVoKIWB6/cuRBu5EvhVmd5oc/Qj qGQGBYnXTlCyQBDSV5oa+zAcjo95CAwRvHeOAzBiFOgBYnYKwUXZh6TAoCkEDZvq3j5kcHFRCoEY kPcELPbqtLCfqHqopi8RLQo1GYyM3m4mzyhaF/IVhmq6w1AYrXaZV5jrUKcZ+K5yisG5nfl7MsIb IsVBmcoim9zSRGkpK51pHmja+KWLU1l0zQs2hRQ29ilPjh7Hh6YwQHllwW7FiMkGCLWStq7o5kIV 8muHarmhg0VqBbVSPUF13KOVigiUTS0YunwlwXrtnWnRCupsemk9PiicuOEYmxGwyBRADK4F766W 0NWokroCgTIJVNQGRkW7RbMB/HcWxqI6yGA0hHyuPCRq9BkooRKjPe9R/oHZ0Bg0BjNLcyQrxqlI Usicj5XfX8KYDbabVa6hYjG4hKyRYIC27qveZbsLUd/WHrRugMwN7ztdAmaVhu3Cj5OovX3agvqG kNorXjJYmwYaE1VjC4MFReJS0E86+i4oU/FM2gu5m9N9vpL2kb975Slubd0SB8a8ax1LIgK5hLSB sPB52zbItKU7ra8Tb7NbrcomuthlbB65Hx99NYLAabYmt3EtbG/viQgSCzFchKj444oNz4oeblA7 3ccHSeepkECMrkeg6fBIuasLzvQwXaeV7cVRsXctp0VqwSpXlLu/tYdxuSI5AqtOQo+DItTLnFtd GzxOKV1gkAuuCAxjmPYLDOU9UFBjVJFlnGWXyAvQvShXFZVUBsWUjUmd69Z2c+tw4E5PzLFdi7a2 xNnGEgxBOQZlpJDxAeaF3I3THq6QCiv8FoXAE3IKsgEZgI4GaM9YZmlCFVVYMR5CFcqFmOAo1AyS E+SYQ3MuCiqayCKhobGJRZRkCIEZ80psugitIVvbXPSy+1eVW4VC3pWVBtV4VNJMtcJpoMVC9q0X E2wesI3JmG+RwE89y4LTNS4GiqQb13gvOYL6MaFKt4I4kPZ1UChAjaRrqispVXmuU8TLmNthkDGV bf/AhpSPrzQo8mGS0qWpGYxfSHXO2zcckzgBb9irUuYjZtfhvgGJqqAqR+meR64xVmy+ajuc+HmL DGplY4owmxzhiYi4/M+291y2A9GJhDDTMnBMLMu4QU8JCrKQrFz3Fz2RXzVlSw6i39UaKkZQRAXK dHGU8SwHkjhyym/B0Kw4I5AnGO955VmMLuySNd16MuNZpMdCv4tpbctRg5bGxE7GPPoIJdJ4HLpA 3TwUlTfJJNYVOOZKnTEGF0tjLnDljkmzpVGURV4qLTHCjdDIuV+DQC2tF6c/vYfTwT7Ao2rLwgjG FeZTsUJfVEFyOCziso8KxrS83BxahznfLTYD4PUUFUtSLUxXSzk6EoEWpW2Dhw24i1X0AnattzjK V8oInKJEQpCdvgz0b7Rdep43JNbixUUKgSBKxAWhiWphAVsSUolDuARiE0vMwHxsZGk24OL23U5m gEXrgFA9qEOmZCIRrAPds08zs2kg7lXe24glkMak+lPWyQW+toxaMmLNvJSpI4sjXQnCJHKo6El4 Ptss6zhiAdeifKXI/a9p+PDJVqOdwH5EKA0nPgdBd0HZC863V6ocPesMlzVjU97Apnha7yLmGlY8 aSlF4Od5Hut20G3OFE+n0ne0w96mZtFMFYK9dwzxWuaRNcGdz0YFJpMUqyaWOq7WRODdyReOtdrL 5JysY4MK7GXwaZOq6Hv+wDeB/4u5IpwoSG8qlKMA --===============0386533980700221836==--