From: Ole John Aske Date: December 1 2010 12:31pm Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3381) Bug#58628 List-Archive: http://lists.mysql.com/commits/125649 X-Bug: 58628 Message-Id: <20101201123131.C1E1D222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6802703132553743258==" --===============6802703132553743258== 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 3381 Ole John Aske 2010-12-01 SPJ-scan-scan: Cherry picked fix for bug#58628 Incorrect result for 'WHERE NULL NOT IN () create_ref_for_key() allowed any constant part of a REF key to be evaluated and stored into the 'key_buff' during ::optimize(). These 'store_key*' was *not* kept in ref.key_copy[] as they where constant and we assumed we would not have to reevaluate them during JOIN::exec() However, during execute NULL values in REF key has to be detected as they may need special attention - as in 'Full scan on NULL key'. This is done by subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts evaluated to a NULL-value. As we didn't keep a store_key for a constant value, a NULL-constant was not detected by subselect_uniquesubquery_engine::copy_ref_key() ! This fixs modifies create_ref_for_key() to check if a NULL-value constant was produced - In these cases it keeps the store_key, which then will be reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys. modified: mysql-test/r/func_in.result mysql-test/t/func_in.test sql/sql_select.cc sql/sql_select.h === modified file 'mysql-test/r/func_in.result' --- a/mysql-test/r/func_in.result 2010-06-22 18:53:08 +0000 +++ b/mysql-test/r/func_in.result 2010-12-01 12:31:27 +0000 @@ -770,4 +770,40 @@ CASE a WHEN a THEN a END NULL DROP TABLE t1; # +# Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +# +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); +SELECT * FROM t1 +WHERE t1.i NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE t1.i IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +SELECT * FROM t1 +WHERE NULL IN +(SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; +pk i +0 NULL +1 NULL +2 NULL +3 NULL +SELECT * FROM t1 +WHERE 1+NULL NOT IN +(SELECT i FROM subq WHERE subq.pk = t1.pk); +pk i +DROP TABLE t1,subq; +# End of 5.1 tests === modified file 'mysql-test/t/func_in.test' --- a/mysql-test/t/func_in.test 2010-06-22 18:53:08 +0000 +++ b/mysql-test/t/func_in.test 2010-12-01 12:31:27 +0000 @@ -555,5 +555,50 @@ SELECT CASE a WHEN a THEN a END FROM t1 DROP TABLE t1; --echo # +--echo # Bug#58628: Incorrect result for 'WHERE NULL NOT IN () +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, i INT); +INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL); + +CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3); + +## Baseline queries: t1.i contains only NULL and should effectively +## be evaluated as 'WHERE NULL IN' +## .. These return correct resultset ! + +--sorted_result +SELECT * FROM t1 + WHERE t1.i NOT IN + (SELECT i FROM subq WHERE subq.pk = t1.pk); + +--sorted_result +SELECT * FROM t1 + WHERE t1.i IN + (SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; + +## Replaced 't1.i' with some constant expression which +## also evaluates to NULL. Expected to return same result as above: + +--sorted_result +SELECT * FROM t1 + WHERE NULL NOT IN + (SELECT i FROM subq WHERE subq.pk = t1.pk); + +--sorted_result +SELECT * FROM t1 + WHERE NULL IN + (SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN; + +--sorted_result +SELECT * FROM t1 + WHERE 1+NULL NOT IN + (SELECT i FROM subq WHERE subq.pk = t1.pk); + + +DROP TABLE t1,subq; + +--echo # --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-12-01 10:21:04 +0000 +++ b/sql/sql_select.cc 2010-12-01 12:31:27 +0000 @@ -5874,22 +5874,36 @@ static bool create_ref_for_key(JOIN *joi if (keyuse->null_rejecting) j->ref.null_rejecting |= 1 << i; keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables; - if (!keyuse->used_tables && - !(join->select_options & SELECT_DESCRIBE)) - { // Compare against constant - store_key_item tmp(thd, keyinfo->key_part[i].field, - key_buff + maybe_null, - maybe_null ? key_buff : 0, - keyinfo->key_part[i].length, keyuse->val); - if (thd->is_fatal_error) - DBUG_RETURN(TRUE); - tmp.copy(); + + store_key* key= get_store_key(thd, + keyuse,join->const_table_map, + &keyinfo->key_part[i], + key_buff, maybe_null); + if (unlikely(!key || thd->is_fatal_error)) + DBUG_RETURN(TRUE); + + if (keyuse->used_tables || join->select_options & SELECT_DESCRIBE) + { + *ref_key++= key; // Always evaluate/explain in JOIN::exec() } else - *ref_key++= get_store_key(thd, - keyuse,join->const_table_map, - &keyinfo->key_part[i], - key_buff, maybe_null); + { + /* key is constant, copy value now and possibly skip it while ::exec() */ + enum store_key::store_key_result result= key->copy(); + + /* Depending on 'result' it should be reevaluated in ::exec(), if either: + * 1) '::copy()' failed, in case we reevaluate - and refail in + * JOIN::exec() where the error can be handled. + * 2) Constant evaluated to NULL value which we might need to + * handle as a special case during JOIN::exec() + * (As in : 'Full scan on NULL key') + */ + if (result!=store_key::STORE_KEY_OK || // 1) + key->null_key) // 2) + { + *ref_key++= key; // Reevaluate in JOIN::exec() + } + } /* Remember if we are going to use REF_OR_NULL But only if field _really_ can be null i.e. we force JT_REF === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-10-15 14:42:00 +0000 +++ b/sql/sql_select.h 2010-12-01 12:31:27 +0000 @@ -761,9 +761,8 @@ public: store_key_const_item(THD *thd, Field *to_field_arg, uchar *ptr, uchar *null_ptr_arg, uint length, Item *item_arg) - :store_key_item(thd, to_field_arg,ptr, - null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? - &err : (uchar*) 0, length, item_arg), inited(0) + :store_key_item(thd, to_field_arg, ptr, + null_ptr_arg, length, item_arg), inited(0) { } const char *name() const { return "const"; } @@ -771,23 +770,13 @@ public: protected: enum store_key_result copy_inner() { - int res; if (!inited) { inited=1; - if ((res= item->save_in_field(to_field, 1))) - { - if (!err) - err= res < 0 ? 1 : res; /* 1=STORE_KEY_FATAL */ - } - /* - Item::save_in_field() may call Item::val_xxx(). And if this is a subquery - we need to check for errors executing it and react accordingly - */ - if (!err && to_field->table->in_use->is_error()) - err= 1; /* STORE_KEY_FATAL */ + int res= store_key_item::copy_inner(); + if (res && !err) + err= res; } - null_key= to_field->is_null() || item->null_value; return (err > 2 ? STORE_KEY_FATAL : (store_key_result) err); } }; --===============6802703132553743258== 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\ # vc14367mtq6m55v0 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: c58adb102dd834988f340f8a7beeca2c37e5bc2f # timestamp: 2010-12-01 13:31:31 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # grubtn0qgwtf1adv # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWffuasAABj9/gF5wQgB5//// f//fqr////5gDR0l2bH1u7Xq5gA3ta3KR693XKene3uxTQKtmqlRVwlEIjTRigxqeFGap+poaaTy T0mTQ00yDJtTQaAaPSCUpomD1J6CZU/RTynlG1PQ01DPUQDGo9QaDNABDJg1PUGmSRpo0T0p4mka eITQ0MgAAAAAAAkSInqDTU00ZMmSek0xpT1G2oyg9Q9NQaAek0NG1DJ6hwNBpkNNGhhAyGhgjQ0y aNAMgxAAGgkiEABNBkKekwIwpptKek9Iep6j0jyJo9QG1AaFavIBNBRG8ZjOb53IzL3nHpz7rldY uNze9ZZYtY6DwaMbY7uuTTa+Cm/6ULn6jTQKxbZf09rmfxurmuvzO4FVdlVJqGlNKEbc2OEgkuoP uzKh3HQJCS4WMt2G2c8ES1LDIN5GCTcqIqQsrhpY7DpM9tTnKxxSddd1E8aQK3i9vsmAha6I8HB1 QA2hNgFPgvzJTR6YydVxKx2CNvem21cXYFYJUlP37xgxO0QuSwWofFee5Q4Q8YBBJIBJICa3ahll g4W8g1rrSfBxScmylIRJCTAuudxxOIJs5WWNTw437Y6RFk91arePnxCIEP4dCcQKILwMkGgPRivF tjZb/BfLzG1eFmA2XDlo8J+Uqdtj7r17ehD5l9N0NwqJ0YIKW4ol3sfetEemmV29w0PKgkPvH9Cs VZkwIUjBOR7tvRh046NHAof1/3CdeKCETr3XtECaRdX/tj/TOUDqDhfAQHG1CbGfCSmDRF7PLsTH B7ydsWOhSpXm9e4wKPSNsTgErpgSZZMwyFpXliUNKlE2olPVvfeU6Vx7D5nzEOIpcQ5AYXhMIrAF 1lWI14YC+CTf3UjaY3WnM+IWizahpFYtqZXiS9mZOLjwdHTZJHjFzrAhx8j2WcB5RAjOYyCsYjEM ScUzt7OjTt8+LMUEaHGIG222PTHPqqSD04TENNW9ZICjvfy+9s+7Xd7ZdfGg9sbJnk3UDCocrPOS GJhialX7jKmjzczIzOPvp1Ic2sIYGag10zOlxoNU5OXavyAmJ5b4wCMm793Y0bd5abzaQIECBb1k TJdAE/UWmIeZEDAlmrLM6x1tPzN1NB4gk2QhYo18yHV3QDVQuRFgZm7BesF5Mp/qly+zw8lOdz4i DcmLBA6uE4gmoNAg8IPiWGUP2bemURYI2A0MZL3jsAiGFwacjqRs+gYXMtyoOKiLwVhdXXNSBgEk MAYBAM4gc2JYRllRXLMcNOE7PR2KQYUqBXdV87ycsZpsOo7jQMyXOjaDIYYYYmc0GNAiaMAYGu0w ZCNbUVg4YGoLTdiPl4U0AEX/qPNEAsknCsFuBWw3R4WcLr8B7/sE7kwJlx6h/HVQY1Dl6NpfC6aD m2bOOJSdznMETIMAmMtpkagiaOBqkeg5ELc9aYZzVvdmZoHI5BdPkkjJAcLmrCCxLAzIAFuUM4gu DwnBiOmOkEsDwqOBYbSiep5bDYGRWagvBOZDUn5bhzdZvhRmCkVGNm4sK7RsSBA4LQ8abE09YMjc ytYkwBaY+JAxNWJCTvgNijE5EiJWS3Ebz/xs8C4rKalDA2QVhrrMi0ntPJ0dBrOnXuBU3BMiW0uK vcqgoBmPhXPAqKyWs9RXOhYZvDbr1u01zu5EsippwnqLCPUzYtaRXAS3HWVVCNEggL0GIvgnjITA TBFUs9TFmB2iM0AWYUThIkx2DHiilgzVUduDkFkA6xq4PJ07l+1BxLsCs6C/zkbgUieV0rYaoxHC /VTPQX0cGPIxW7acVe11IPBobwxNbYM4wuv1MATJzLI6BkFQYM6bxI8E40lnUIZSImI5y2t2nLQe We3GxM1mopnBXBwSS7Mwx3QAkQAcWAple4KQ5EiHFSjhgc7iRqHjC7qZ1kxBhQGV54Ix71Dzew0q mEapbU7MMaBvQQerj6gwLmNLRWIPOfSL3B952htbnoiiyJtjb/z+hd9VGVB3SQiR2eFRL3CLQ+sg QwYbPfAXRQhytCPxATBILY/2TVXDAj0k3/QFlMpCX7C2ggEexo73NmPmDEIRjFhQiwQjIFARlALG K0KgvpLEEwD9AJLmbaD7gGkaB94saxIhikHpuNKDEBfiDlMobTH8ARwKAs2FRAMgbQRzCI4BgWiL kGIRE4oCHJCqBwxoqmmODU7w30iERC9kXM3F3dOkOyxBZIU28nh12nvr4Y/JltIerP1es4owyB0C WUY9aFZAowZJTSi8CBUhqvtmFMkSY2pREfEm+FR7/dA+J86iOSOUQF/ATZwUafcdH4ER4iW+OZRl s3RAhrB/KK9WuRmRTqAkZ9vGkbE131AxlKHK/WIJ3THoCUVEa64TWnEMN4v9ZEU7JQNOwZoOp4ca z58hg4nqZIkR6zb3BPyerxmkbzJJcWMgXAXdA/Fw+UAevp0nAgdhA6wWs2FxuOJ2DpywV9omWwkV nUvkOXlxt2E5I5jMLWsNaKGIDiDy2gjT8J0fcylLfhSvJ770G0WCDAwMAxO4obBtvkbMoPI3/fZy hv59dYuJMLyGkbTOdBheLymA5YcRz5HPLcXHSP3I8MOiwdXzNIcKQ5jHt4Z9+ztZ2lZLl4eFU0qo BQC0lZ8VVWXei2kvBqAcBIgZhtTyDfy2jOLN9r2pKuJSTc5LYag1htEHBXZOPkF4i5J8JjbI7z+Q G9ciRJFCCcjXPA2HBy6DlG+8gsGrGRw7zCTFVcpPNOxuoSZ1BgqJh2CYfbgs6+0pvqa9d0Qkiw2g cw5i9QhagRgn6XPUxcdMNfnWVMU7mlt2h+4+hRSyKOmMhp15YSQ85BAT43kDJWphvQ3TFK08AIuf J7UGVWtsA5BoChtYg522azyAbWIvhORrNQ/jGijhJdzeG6s7ayAvwEJbEoFkQhx5SMuPaQ3wwFWq DrIInGLshvT4DMdBOLFcFx1GRriyU9kjV6Rqj0HbOdJWfQXon4noDWczHi7D5DdojtyCYWch12n6 frgzEEOeZIJLzFRI2bPuR0CLRGAwMrYvOMKKPREbUhHwA3Y3J60C6zZH4pjKuslgR7gw7BXHCCfh YqhEG1F4uZKricO68I4U0VYw4d3PXfoA1+hQaz17UoKNw+zlnMVZkLA18OnvPUdAg+2636yhHJhL xqIBRfPEWJBKhhOeNxrqZCQ/fBC7f3xTM776yXPsxJkr/TUPcdWTO60XYZhMlHsgE8CYVZIl4pqf fFwzEHaIHA9cyB5a41Fh9Z10TxpKbF52ItOXwgAVEHgIOr4ddlFz9DUNakED6gXYaEpNv0Wwjeir v1kup8Cf6iVE6K6i8m9QKXiVlQy+hk41TUs4z/GNfCRCYnEGBEUcwk6Lw8pMKNSYU6VTqeC8aIx0 kBMrqSDfmJoMUHzbWYkZZjoLEQZVok5NNi3ub/pbZUsxQ0HTLJlTFca8k+E4CbxGM7vMIN4xyR7j 2oYH9nsLgRe1WthgOj0Q9hGS9rC2i/okllOIyorGeAgyiCFWi1GyazkZVq+KZu1mR+nu3SUqv1YZ ASYxFE6goiz8thCwcMN514sv8KGcR6wYPzjC3lVfCnZYRhhNJriODoNAFobwTjfsIn0hdkg7xjdi WVI3mJUK/e3RC+GLKYaUZJCpM8shHCVoi+tjvBsIwJG/BbUiAcs/WyYZ9MIwKVgUtjwDzGC4kDiY TUPkUlCGVQ6Hd8waLJo0lVL85LND1OXyTk9+dbwMm64uZyFS7e6NO7fZuBQrGbWsheraRza11QxM FJWXJQDGgQS5Ui+jabprrsRsDY2225ElvSgbINyjwNF1sDElS3x6pf66m2gXKeZXSVyEBk6C5wGD GivDKVsEKqWi3Nr56CdUNKA0C1qYuQMoTCwQiphHfPbXOE9PQBfBIvlVyaDKRqaoyg6NsHbeRcha FVyKwtAfoCbr85IUGXcUbLMzPW2EuoKUEm2CB1zHmBT0E0qjFxbbb9p7xzM9JQkXoAqDrKSIvZ9I nYyXxNi+RirLEA7JxcxvoI2WhyNDw08hYd4K8zSOHfTGwW4WWKt5rqpk86hLOM2zqu2GukIkNLrG ORxCssnwrSOk+9y84E8EFurSorlzMOzwjDoYJMIGgmxtDUQoIhMde2XCW6Ob4lWQYybCEkEwCvZB ZiwuGis7UvIx3wF3JFOFCQ9+5qwA --===============6802703132553743258==--