From: Tor Didriksen Date: April 7 2011 11:56am Subject: bzr commit into mysql-5.1 branch (tor.didriksen:3648) Bug#11765713 List-Archive: http://lists.mysql.com/commits/134924 X-Bug: 11765713 Message-Id: <20110407115700.6802D3784@atum07.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0539138879064139757==" --===============0539138879064139757== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/didrik/repo/5.1-foo/ based on revid:georgi.kodinov@stripped 3648 Tor Didriksen 2011-04-07 Bug#11765713 58705: OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES CREATED BY OPT_SUM_QU @ mysql-test/r/subselect.result New test case. @ mysql-test/t/subselect.test New test case. @ sql/opt_sum.cc Add thd to opt_sum_query/matching_cond/find_key_for_maxmin, enabling them to test for errors. Return with error code if thd->is_error() rather than continuing to read the index. @ sql/sql_select.cc Add thd to opt_sum_query, enabling it to test for errors. @ sql/sql_select.h Add thd to opt_sum_query, enabling it to test for errors. modified: mysql-test/r/subselect.result mysql-test/t/subselect.test sql/opt_sum.cc sql/sql_select.cc sql/sql_select.h === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2010-11-08 10:55:43 +0000 +++ b/mysql-test/r/subselect.result 2011-04-07 11:56:55 +0000 @@ -4734,3 +4734,16 @@ SELECT * FROM t2 UNION SELECT * FROM t2 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); DROP TABLE t1,t2; End of 5.1 tests +# +# Bug #11765713 58705: +# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES +# CREATED BY OPT_SUM_QUERY +# +CREATE TABLE t1(a INT NOT NULL, KEY (a)); +INSERT INTO t1 VALUES (0), (1); +SELECT 1 FROM t1 WHERE a < SOME +(SELECT a FROM t1 WHERE a <=> +(SELECT a FROM t1) +); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; === modified file 'mysql-test/t/subselect.test' --- a/mysql-test/t/subselect.test 2010-11-08 10:55:43 +0000 +++ b/mysql-test/t/subselect.test 2011-04-07 11:56:55 +0000 @@ -3726,3 +3726,20 @@ DROP TABLE t1,t2; --enable_result_log --echo End of 5.1 tests + +--echo # +--echo # Bug #11765713 58705: +--echo # OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES +--echo # CREATED BY OPT_SUM_QUERY +--echo # + +CREATE TABLE t1(a INT NOT NULL, KEY (a)); +INSERT INTO t1 VALUES (0), (1); + +--error ER_SUBQUERY_NO_1_ROW +SELECT 1 FROM t1 WHERE a < SOME + (SELECT a FROM t1 WHERE a <=> + (SELECT a FROM t1) + ); + +DROP TABLE t1; === modified file 'sql/opt_sum.cc' --- a/sql/opt_sum.cc 2010-06-11 07:38:29 +0000 +++ b/sql/opt_sum.cc 2011-04-07 11:56:55 +0000 @@ -50,7 +50,8 @@ #include "mysql_priv.h" #include "sql_select.h" -static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, Field* field, +static bool find_key_for_maxmin(THD *thd, + bool max_fl, TABLE_REF *ref, Field* field, COND *cond, uint *range_fl, uint *key_prefix_length); static int reckey_in_range(bool max_fl, TABLE_REF *ref, Field* field, @@ -211,6 +212,7 @@ static int get_index_max_value(TABLE *ta /** Substitutes constants for some COUNT(), MIN() and MAX() functions. + @param thd thread handler @param tables list of leaves of join table tree @param all_fields All fields to be returned @param conds WHERE clause @@ -230,7 +232,8 @@ static int get_index_max_value(TABLE *ta HA_ERR_... if a deadlock or a lock wait timeout happens, for example */ -int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds) +int opt_sum_query(THD *thd, + TABLE_LIST *tables, List &all_fields, COND *conds) { List_iterator_fast it(all_fields); int const_result= 1; @@ -242,6 +245,8 @@ int opt_sum_query(TABLE_LIST *tables, Li Item *item; int error; + DBUG_ENTER("opt_sum_query"); + if (conds) where_tables= conds->used_tables(); @@ -269,7 +274,7 @@ int opt_sum_query(TABLE_LIST *tables, Li WHERE t2.field IS NULL; */ if (tl->table->map & where_tables) - return 0; + DBUG_RETURN(0); } else used_tables|= tl->table->map; @@ -297,7 +302,7 @@ int opt_sum_query(TABLE_LIST *tables, Li { tl->table->file->print_error(error, MYF(0)); tl->table->in_use->fatal_error(); - return error; + DBUG_RETURN(error); } count*= tl->table->file->stats.records; } @@ -368,7 +373,7 @@ int opt_sum_query(TABLE_LIST *tables, Li returned in range_fl. */ if (table->file->inited || (outer_tables & table->map) || - !find_key_for_maxmin(is_max, &ref, item_field->field, conds, + !find_key_for_maxmin(thd, is_max, &ref, item_field->field, conds, &range_fl, &prefix_len)) { const_result= 0; @@ -390,10 +395,10 @@ int opt_sum_query(TABLE_LIST *tables, Li if (error) { if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE) - return HA_ERR_KEY_NOT_FOUND; // No rows matching WHERE + DBUG_RETURN(HA_ERR_KEY_NOT_FOUND); // No rows matching WHERE /* HA_ERR_LOCK_DEADLOCK or some other error */ table->file->print_error(error, MYF(0)); - return(error); + DBUG_RETURN(error); } removed_tables|= table->map; } @@ -437,6 +442,10 @@ int opt_sum_query(TABLE_LIST *tables, Li const_result= 0; } } + + if (thd->is_error()) + DBUG_RETURN(thd->main_da.sql_errno()); + /* If we have a where clause, we can only ignore searching in the tables if MIN/MAX optimisation replaced all used tables @@ -446,7 +455,7 @@ int opt_sum_query(TABLE_LIST *tables, Li */ if (removed_tables && used_tables != removed_tables) const_result= 0; // We didn't remove all tables - return const_result; + DBUG_RETURN(const_result); } @@ -565,6 +574,7 @@ bool simple_pred(Item_func *func_item, I field identifiers), which will obviously fail, leading to 5 being stored in the Field object. + @param[in] thd thread handler @param[in] max_fl Set to true if we are optimizing MAX(), false means we are optimizing %MIN() @param[in, out] ref Reference to the structure where the function @@ -589,7 +599,7 @@ bool simple_pred(Item_func *func_item, I true We can use the index to get MIN/MAX value */ -static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, +static bool matching_cond(THD *thd, bool max_fl, TABLE_REF *ref, KEY *keyinfo, KEY_PART_INFO *field_part, COND *cond, key_part_map *key_part_used, uint *range_fl, uint *prefix_len) @@ -613,7 +623,7 @@ static bool matching_cond(bool max_fl, T Item *item; while ((item= li++)) { - if (!matching_cond(max_fl, ref, keyinfo, field_part, item, + if (!matching_cond(thd, max_fl, ref, keyinfo, field_part, item, key_part_used, range_fl, prefix_len)) DBUG_RETURN(FALSE); } @@ -732,6 +742,9 @@ static bool matching_cond(bool max_fl, T if (is_null || (is_null_safe_eq && args[1]->is_null())) { + if (thd->is_error()) + DBUG_RETURN(false); + part->field->set_null(); *key_ptr= (uchar) 1; } @@ -794,6 +807,7 @@ static bool matching_cond(bool max_fl, T (if we have a condition field = const, prefix_len contains the length of the whole search key) + @param[in] thd thread handler @param[in] max_fl 0 for MIN(field) / 1 for MAX(field) @param[in,out] ref Reference to the structure we store the key value @param[in] field Field used inside MIN() / MAX() @@ -802,8 +816,9 @@ static bool matching_cond(bool max_fl, T @param[out] prefix_len Length of prefix for the search range @note - This function may set table->key_read to 1, which must be reset after - index is used! (This can only happen when function returns 1) + This function may set field->table->key_read to true, + which must be reset after index is used! + (This can only happen when function returns 1) @retval 0 Index can not be used to optimize MIN(field)/MAX(field) @@ -813,12 +828,14 @@ static bool matching_cond(bool max_fl, T */ -static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, +static bool find_key_for_maxmin(THD* thd, bool max_fl, TABLE_REF *ref, Field* field, COND *cond, uint *range_fl, uint *prefix_len) { if (!(field->flags & PART_KEY_FLAG)) - return 0; // Not key field + return false; // Not key field + + DBUG_ENTER("find_key_for_maxmin"); TABLE *table= field->table; uint idx= 0; @@ -843,7 +860,7 @@ static bool find_key_for_maxmin(bool max part++, jdx++, key_part_to_use= (key_part_to_use << 1) | 1) { if (!(table->file->index_flags(idx, jdx, 0) & HA_READ_ORDER)) - return 0; + DBUG_RETURN(false); /* Check whether the index component is partial */ Field *part_field= table->field[part->fieldnr-1]; @@ -858,9 +875,13 @@ static bool find_key_for_maxmin(bool max ref->key_parts= 0; key_part_map key_part_used= 0; *range_fl= NO_MIN_RANGE | NO_MAX_RANGE; - if (matching_cond(max_fl, ref, keyinfo, part, cond, - &key_part_used, range_fl, prefix_len) && - !(key_part_to_use & ~key_part_used)) + const bool cond_matches_key= + matching_cond(thd, max_fl, ref, keyinfo, part, cond, + &key_part_used, range_fl, prefix_len); + if (thd->is_error()) + DBUG_RETURN(false); + + if (cond_matches_key && !(key_part_to_use & ~key_part_used)) { if (!max_fl && key_part_used == key_part_to_use && part->null_bit) { @@ -892,12 +913,12 @@ static bool find_key_for_maxmin(bool max */ if (field->part_of_key.is_set(idx)) table->set_keyread(TRUE); - return 1; + DBUG_RETURN(true); } } } } - return 0; + DBUG_RETURN(false); } === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-02-22 21:03:32 +0000 +++ b/sql/sql_select.cc 2011-04-07 11:56:55 +0000 @@ -961,7 +961,7 @@ JOIN::optimize() If all items were resolved by opt_sum_query, there is no need to open any tables. */ - if ((res=opt_sum_query(select_lex->leaf_tables, all_fields, conds))) + if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds))) { if (res == HA_ERR_KEY_NOT_FOUND) { === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2011-01-07 14:06:24 +0000 +++ b/sql/sql_select.h 2011-04-07 11:56:55 +0000 @@ -612,7 +612,8 @@ Field* create_tmp_field_from_field(THD * /* functions from opt_sum.cc */ bool simple_pred(Item_func *func_item, Item **args, bool *inv_order); -int opt_sum_query(TABLE_LIST *tables, List &all_fields,COND *conds); +int opt_sum_query(THD* thd, + TABLE_LIST *tables, List &all_fields, COND *conds); /* from sql_delete.cc, used by opt_range.cc */ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b); --===============0539138879064139757== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/tor.didriksen@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: tor.didriksen@stripped\ # pnt9rexutxu1ljkc # target_branch: file:///export/home/didrik/repo/5.1-foo/ # testament_sha1: 3cc53327a62a446eece17022e91e72ec7d32fe34 # timestamp: 2011-04-07 13:57:00 +0200 # base_revision_id: georgi.kodinov@stripped\ # m4weaz3f2vbojglc # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWavZ1EwABnT/gHdxhAx5d/// f+//ur////FgDQ7fPvvNd9afe1GgopKhnRkh7aI9nS6jL2AWrSegyk9FDEamJ6jDRPRHpPUekG0I AAAyAAAAZTUxJ4Rk0mTU1D1D1ANNDTQxBoAaAAANAUEanlAAADTRoAAAAAAAADQBhRNNNIT1Nqnl B6R6gDE2oDRkA0aAAaPUNA0CKQgBIxNTyU9hE1PBFPMqekPSep6E0Gg08kH6oYnpMEkQTQBBoaU0 xNNpIPRqYp6NR6MpkabUaaPUeoAbKPWFHEoAh2zngYrcSGqrNimsSQesLs4W5WkUjHkSaWMmrqDQ Wn2ri94deptdCepqrA2hBauLFGNyFYFzMsrDx11v4c/k264Zmurrrq2oRchyZRiQYj+JLi/0JUSC OG9+4A+8zMYS82TP55aQpdKUqqEY1RdqnnEspOFDgJK70OhATF+J8zoOpgZkza2UmGZHl6UfzOXw 75f7jpHK7+gqzw0+cp9D9ZN4/Wncm9IAiAghiBYj/v+gCBVBgcCKqUERkkzHTCiUowLBBJvKaGMZ VCHUIRApwkAUQLUJxpFKNiUmJqtWMuxC1RslCKapBzRqQQo2rFXFyTUlpAxck5DsjMNZiIGg9fKV gFAJoRDORgkH0mDed+9/cSxPsLn3tpkR2SCsMQ32QcGRwgMcOFDKh94mGv6K5bJExA8MqT6xhmnC 18VuMIWoUwAOWww+REJnkVsUVL6ouXVYzFnOcfee8N4wx+kmqBDk/5fNQX2spRUumEbfpjCurgx6 Ml29LP399p+iM60oS4DQQl+nmvh07v1IKmNjMwIzzZhmZmXHg4e1jzDbQzcbSCU0sOHM3vdQSNZK 3rpTE6jIZ57BCGWycWal3BocpcSu2Ws57KVLHXYWd2DFyRXDr5WQCzd47ShV/YZzyJqGTvvK3jNO Qm7DZC6F1plULESJqaMcHOrX2/YQoqxfnU7YOyXiVC8rBPQGdpYAZjjVlxuxfUgxJ6cwQxHFRvgQ vVyKwqjsCE3Qnym364TRbbPRDlsC2VyO31WIDWyQcGSdjjDqTihpwlRBtGVv/52xNCCgkKEOzCg3 UoVSEtIQLGpdZIVCDikTUqTqgwlL0tbHCBXYgkQK6AOolpQhKmY8MK2QFLGi0EXvOSiaxMwzGR+2 7snUIsYyd0HyugJkHXA4GT82E0gzY92sS+xSKVrGINeixs9C1SetxuaV0tEqm6iVhQUBxQE+bF0A HlrxKHFspGFH1l9hWmunA31ced6jkgnJuY2I/7m5mi8c7Cw70klWcx3vgJTxN2j8NW+CplpOU9op MJGu0VBcLLi17GGJTHGxFQeMkei9nJXUkrySOPHKq8wjTTECO4tMRhvzstJVmBA6lwU1NNbkmpsu rhGGVsFHZMpEGwQULIRxayydh0FFdAstGKiWzNwgt2QqtDVSEsRmttaUsLQ8RG6aOCjke1V0zDCi Ko7WX4Y4xIUsrGgKvarGEmRoxOcRNSJAz6qXkh6MOHOPS+sWstkdWVLHvvpdkyw7fCHAUdB21mo+ eMTXYeBZfBVKvUE7RrWwgmEsnywGJi5jPIckQeFVmRR4wXkCXjGoE416mnGqIcDIWNC2G4gKiY7i RxwIGkPGtVuhvsZ9lbYM171NheVyghhlDWVYGZEpKRWK8StUCPRF+x9lLiWIOPtZLKVVhw69w+cV zFBdlp1Wlx2C0xMwhnZdQLEpZMoQNAyLmAl4wKCGtbIdUozMIxU68SympTbbBMRQucbi2yTYGpSV RLPXUYZocLdR17teFSVRbfsMyJSes0JkTj4zmVefI4nqGJ7heXG6M0yTTRVGXhWS2tFckzCqYajV BbULi13YotprGKo0a2vKOdxDcXG01FJWFdlrasUEq4mqEWsKt8Ne+0vIVoKoTWBEmg2HRU5E2W5X wyNI6sefdxEtquKqnNjDFIKC1R3jIwm4POTQFAUnmyUUPKA8Bap11Tk0awK5NDzIsGSW5twXnYMt s6grIhjlEgOZJfSdPT6g9hHa+dCz8w3K5mP2ofc/BPoT7+dz6oYCAg+1D3e1HFtgsgrIYA+IWFll FMQHBwN6VpUPHBmwLf9QobxADfARGiQzfwaPuH+zc/vORrbDQmVVpqbYTBwU+ASTU2VrxNiP1Yp4 pA/BqciwLybDpeso4UPYGQ0ZJQ2uYhKHvA4NGToUgtZ0ZMmTUWEgiMQOcYyKC2kF/4gOJsl73e4s TCtWDwEURESvSKC0K4zSdtjIY3BaXGRUD4FxRVlRkUCsPuIdCVGCYPMzL1LptW/k/DND5E1I3lG7 J8ikJKEzmTYxLMPadRZzGi+y08Taya1PTKa/ELPZ9AXuvauxjzodHnQyKqjuRACcoEfYh6lWlJ2l +FgiCSPtb7mNAOkLKkvUMj7Sh6z13VHsXsHP1kJn3DH31mIb1+W5CPXfoj7fnfAs2wq3UFWA3H9/ 9SVaJrBGPA2RdMx+GtCLZDIHRJ5rLoUERiKmGkbsIcHZUqYxXzA2K4xlxjQyCoQoJgfChHWcED1F a/U1J8I9+BnkJFr5XkN3uPaoFTsOmgyGE7DhsMw7DxG4/Ic5iA0jatD+QFTHfwavxG+JHLI0dkda 4punCJhmyVPi+dnHnl4xEf0nLvbupxRMxOjn3s2rOymxOtiooThlDOJQkcyUg379thybiEsXnaBd avDa1799yllFjXQuMx0H6nw0mBF54bkl3CmG3JTWQAwZJ5bljukjfHr7ZzXJmqHGELPjLl31tHgR uIMavDrjTQ+YcsqOI0MOdHjRYYdjg89QhgNiM4eRYlxkajmRNCIYruMIDOX5m4hrXbF1yTB6RwCT N52To1ShZNQ+6uSCMCCLsAETlaKlew8mgfQwup0hY338N8+fFtevZmkHcSeOwDFzZGeXQOSMRIci PkgSx/YZ5RpbFKhc5pIR7V2eTlg2yTAFBlwyMNgW4pwWIoxGV0TGHaAuIIgkIOFp1PGyyHRpXnbD q8siH+Kd/YkJRPVB4/Zy2jxaoeV0J3XNaQYWRI53uLEjokHa4ja7oaQB5m8BYYZmFAPE3hoSG+Yh CX6aFySJwBTRD1ovldEAsG5VNZAEck2yT9jUHZHpGRYQWztmuHgAB1cDC4aVa+UKzeXZqwgAc9Ak pB0KADfdzbMCNNStgxF4AG5crUItKedD/A07wE0TOPJbN/YrnDIJnlv8nhjA5AVF3cDbAPvHDw54 KS11oy9PUnEvDyO0SsXeXJdeLC3/H2bfsmeD0LMrA9PjDuANzLNLeLeg3BIDX6joo+aupgSj6Wof LhIIS2NqJCuqiJx2tbcz/Oze35ReqTqhIGw4uI6u15Dw7mq7jNYpgm2aiRkPJLZn1G0759Pu+qUp IZDWIqFxNEdmxY/SqTmDQ3mqRZlUP9eMFl6o7IJwiA1C9ugzlkVEmKMScEtsKJUjBBNknmZizvpf DQL2aeVrqKi/pFhJwzhYhxfWzc74Rf196cA9sBSnCt2M6Tyr4XhVa9TWtQD0uSHFE1MNmtm5XLge zY8JWnjDR9lZawOhWSbkSQHJsCjU8tTr1athrCw1OGsE48zvkkLHQEkqHNji4ugWm3hY33TuBh4n PcUb8wJ7WCx58nrebOut3YDLRE5jSFdtGlZ0ss1YgWtWk1NKmd/ofTMzXI+E++yA75nL6JD8wvI6 XiCLvAAkzdrsOfuZGx33aa1Mi8BobyGnWMziSks5w64227gZFWnYMIgIKk9vnBTxq1+UMhnWsR0i NnaAcVSFi2RDkxRGoBkaMZHGQrOQ49BHTVG222227TrFnmxiamy6hcDTYzpGIzsdCawCutVu6eIs gXyT4i4SxBmXNcZHLSNpJk4VawSmUbAez9DWLQyklkznE3gvcvN4i+IVpVPhj7RvJz6kd8NCoeAh 52IgfmMF7MzfKDsDEfj/f4/Qo95lnzjaaErAN/MdImRT1aAXN0cd2E108A6RZO0WGT7rxOy09fyA fzfRiG46LlCCNl7W9AbNEHZoF73F2vY+ebvHwZPM6P0hzADSbyhoLFbcFjAuPlOk4dmhTNu2nA0M ZqG4bNrEsJ0aSrn5WpN7eLuFgWbBVXE4WZBWyE1ve4s3Y2sncmN7yEMXksXFzDg+Rw5HHQ6eZxdf 5C7kinChIVezqJg= --===============0539138879064139757==--