From: Ole John Aske Date: December 3 2010 2:55pm Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3386) Bug#57030 List-Archive: http://lists.mysql.com/commits/125946 X-Bug: 57030 Message-Id: <20101203145519.957EF222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5619611276780057608==" --===============5619611276780057608== 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:jan.wedvik@stripped 3386 Ole John Aske 2010-12-03 SPJ-scan-scan: Cherry picked fix for bug#57030 as this was a RQG test showstopper NOTE: There are two alternative fixes available for this bug. I have picked the smalles, but most hacky of of these fixes. Might replace - and hopefully will - with the other fix if reviwers find that one better . modified: mysql-test/r/range.result mysql-test/t/range.test sql/sql_select.cc === modified file 'mysql-test/r/range.result' --- a/mysql-test/r/range.result 2010-08-24 15:51:32 +0000 +++ b/mysql-test/r/range.result 2010-12-03 14:55:15 +0000 @@ -1666,4 +1666,91 @@ c_key c_notkey 1 1 3 3 DROP TABLE t1; +# +# Bug #57030: 'BETWEEN' evaluation is incorrect +# +CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); +CREATE UNIQUE INDEX i4_uq ON t1(i4); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30); +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const i4_uq i4_uq 5 const 1 +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using where +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +pk i4 +1 10 +2 20 +DROP TABLE t1; End of 5.1 tests === modified file 'mysql-test/t/range.test' --- a/mysql-test/t/range.test 2010-08-24 15:51:32 +0000 +++ b/mysql-test/t/range.test 2010-12-03 14:55:15 +0000 @@ -1325,4 +1325,63 @@ SELECT * FROM t1 WHERE 2 NOT BETWEEN c_n DROP TABLE t1; +--echo # +--echo # Bug #57030: 'BETWEEN' evaluation is incorrect +--echo # + +# Test some BETWEEN predicates which does *not* follow the +# 'normal' pattern of BETWEEN AND + +CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); +CREATE UNIQUE INDEX i4_uq ON t1(i4); + +INSERT INTO t1 VALUES (1,10), (2,20), (3,30); + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; + + +DROP TABLE t1; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-12-01 12:31:27 +0000 +++ b/sql/sql_select.cc 2010-12-03 14:55:15 +0000 @@ -3439,6 +3439,7 @@ add_key_field(KEY_FIELD **key_fields,uin */ if ((cond->functype() != Item_func::BETWEEN) || ((Item_func_between*) cond)->negated || + num_values < 2 || !value[0]->eq(value[1], field->binary())) return; eq_func= TRUE; --===============5619611276780057608== 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\ # vlprbatxthsq9vnh # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: 41f4522443d16307b2fd4894028245652c48f884 # timestamp: 2010-12-03 15:55:19 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: jan.wedvik@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVLx4DkABd1fgAB6WPf//3// /+C////0YAoeXl9my1tXYA8LnAb3mCQ6BusO9snhJKalPepqT/UmFPap41T0ym9TFPU8mmUbTap6 jJkYNBkIwSUSegGkzKnmoTJDR6jT1Gg0AeoA0aAADjRkyMIxAMJoMAmg0DJk0ZMhhAYSmSIakZJ6 ntUH6p6jQ9QNNGjQAAAAANBFQiZPSm0aJpihoaPUaD1AADIBppmpobKBJIJkEwmk2iegmhppqmnq melHpojam1Hqeo9QAZKcmPo1MVhlCBQ6xo6FKA0BCEFHvBQpeg1Ux5VKx+qTmpsUkpeTWpyKcimB aRTumb6yOBXttebC97P9Yjb3kJNdvcsY7T6+67I7GlrSK6UaX0sWvxHckksE0kk1KaSlShec6TZn bMmxtktjbG2UbJbN1sdwBmLbM/R1Y34ztRBwOkpD6VgBoXYijKu80hnLP9MwQ6QSIWdl/iXgrqZm GZmXW0D/CM9vhK3iCOQCYCYJgYIG/uoe9G74XcCK1252kJ2C4NXYc43hGxQlCSWYOEGG/RiELUga SSSgQJCSFAglgXay5oBIyyGYjN4peBlHHC86yEHFYjGBIYkxGGehpi7wdz6H2/+b3klXNxs/N2z3 dPlPuA1Ai45Af5K6NJyKvrVI+zxjdhJaX0XRKKAk6hvofQEiRWED5zFbllXCrTa0hqkuXAZH4NVY zk+WasuI09AnMJTzsyZrERcNLCwKA3xI8EwY8I3UIg0ryhzO6WMjEuNyOQRh5gFQgZoy4viPxNaU xb3w1+bya0+iSTfkRfgS3oxKawVyY8WTJHawK0YT09M96wWm0NQMZiWARRQlIZgEBBRVBCGeKtAK BnLgk0mESAwlAZhJwNT3BeoqaiMOEVwPecx0bGfXa0WNsRQjCKkToGkJoTITM1jiOK+b9GR8u0ue CkWI2LkthFjWFAg/cx1LeBTV6iIGgQfgx9OZdEF6MEcIRgGs10CC99WRAbFt/5rIqLmooduRA6tW Igp2xKRGiWw0JV0km1+PA0MdCVZmzMr1emycJ49pm4M3bcFduIEQ9wbi4EVCWpMjCs2GW148rKO4 NrrpOMmFjV07xxaazwL5rVkmwnxyIKrWWuDAJmLxE4lNYft3BSm7YjDjN0TEknleyarLNpsORkV5 9Nl6RawkrnuLR4ZsRLxw6nSPoGZdg4RQXSIQV15qfiNANAkOHUyP0He20vNora6kjGpazn4rGQKF 2wsqYvxKR8DUIkdvpAKKaDTQfBXXZYEcD8J3jFxMh6HuJGeVBVqiplYPHOdnlWOK2l5ttpJdiMS+ itXFMpIeMGEmLqEN2Yq1nE8xgRzjDJtDSgesJGRN3o3d0c8xy5CBQUyRC3aN7ZPiM1SbWx8g11Sp Lw011zxmLKHTV324MLRw2c5VBwEmj5xtmh1uRgOuIl1BmRwxveWQLwDEB7sTUUURKjtEMbffC6AV M4m989bRetTw0ImYFeqhgRfguC665to7yPk6IJopTq6qKKClKOU5mVRRpO1CSNmUVjTGUiIVOV84 dN6oR1zS+sOUU1bC1FOI6wRv3MGECERU3TWFMigIgpH4KpImp0iOwwBEUkEYhOQKbQB5wUmAPSAM Ss8AA8hYKbDIphBTbYAMbSalwpIrIDcVhzycQoRQDAGIFMKDfgDA5QU5kbIClIqSIFCKSKSmIE0Q 3S0FNwgYPHYYeYUzqVFUy0AdgXG0FMxVSYyBcCmEuUxTBTcKArCYXvKQBQNZ2EEIHlSGMQBMAKgB RjHBvr5AjtSIuXVoThBjEsH8SIZh4GLh5KE5D7HNaeE2MPJ0NpwSIMGb74An4rIpIGG83jTIpqF4 MDRE9zaHJ6hafFMrna1I0zRQexvQCsxZSznLacRGqmeJ7aDYSP33fF2k2Fkfj8xjkcKjIQ44neY9 9SRvBmSGKi04UklefqEZ5McbzvzGIhbwVt3eZETg1LUqhyZCzpQGnzNITOZSAT0FSQ8Is35L8qtW HyN5TNGkoQ6sglhroLbeQODPoY0/KS5ImJFaRTz70aPj0oRDBy463Te0aYAKsTLR9T4KyDcYNcYn Ga2kig2qCiClUxbm3iTxQgQzmtTE4lW8YybcuAhx8GPE3TSkV6nma6RhHaw+7DLZsEski4NiYLcX SrY84OeLypeP2yqNcvnKDtE+MfsZDrV18ByVJFLakVDCRLFI0FjbxbhjuIurN7u8YceJyMLSkkKw InZ/sPJakzBI5OR0kNf0O0oSY1wHpFGLC0HG6w3Hj4UnTpveDmW4uH8gNVDMtUnVo7vue/Y4sRvs 2cbxwmOoS3QsXN4VL4rzEAIgwNFil0keUMeBWkbP0SbOddQVfDBHCSDVMmn5isqL0admcdqqKBew vsGuFb5uJNcE117PW1YHiegL61vkhdw42HNWIUC/iJG9IppHC61nBU01GFmBaFAoXvCCosgQOqR9 7BFNUCzBPE9IiJXJDiqxR3Ol1Op6LunmIipYEHLduf5VgHRp9MmT6VxwOFcfq8JSD4BTHnDgdqRZ zrJtUPBXIcn+o5weBapah1QxiU7WNAakSEW8/5qMiVm0sFglaxH90kyisluJY79yD1hqq+bNIK2t 7uJwZRYKu6gK7UwIXXCz2rQRm62JYf4FMLmmVRy5kxJSc/hack5kaB6RdWTVWKBxvKyzWyZDaSQe IkSpa6gq9iR6mg7p1B9qRAq84YiK7oRGBerDYdlhcIdW5LTbEpbqw77HKaQ8QZYI5dRmKREhU4G0 hJIggh6rHJ6ORSXWhi81YR0aEiRQmLWPJIzSIEGL2sTmc75Z1ODPmLd4KzE4lMoUQk8Dlvq/hMI0 v0uB4LjeUWyFzY6ySS3OeQLKWM1a4iMBMYoQo14kOXNDtRmYO5O0PDvatw47LJralpBC9KpFCb3i aSlSlRKjHl4SBuTq0siIw6sXTiizE1V0OnKSZWrc6DTkmpRfgmi2eCAm6pvKUGfGb/ljxHcNBkbW FZWJR84nf5rIlpJ78ITVvswTExaRpiPxEedhUa+K0QHi9oxyeCgW1OKxwyURHwSHJGdjSZ4zJDik URIckXVkJSooK8NIoSkbpCY9fh7C7kinChIKXjwHIA== --===============5619611276780057608==--