From: Ole John Aske Date: December 3 2010 2:23pm Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#57030 List-Archive: http://lists.mysql.com/commits/125940 X-Bug: 57030 Message-Id: <20101203142317.CBC97222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0201345201362454360==" --===============0201345201362454360== 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/ based on revid:georgi.kodinov@stripped 3477 Ole John Aske 2010-12-03 Fix for bug#57030: 'BETWEEN' evaluation is incorrect Root cause for this bug is that the optimizer try to detect & optimize the special case: ' BETWEEN c1 AND c1' and handle this as the condition ' = c1' This was implemented inside add_key_field(.. *field, *value[]...) which assumed field to refer key Field, and value[] to refer a [low...high] constant pair. value[0] and value[1] was then compared for equality. In a 'normal' BETWEEN condition of the form ' BETWEEN val1 and val2' the BETWEEN operation is represented with an argementlist containing the values [, val1, val2] - add_key_field() is then called with parameters field= , *value=val1. However, if the BETWEEN predicate specified: 1) ' BETWEEN AND the 'field' and 'value' arguments to add_key_field() had to be swapped. This was implemented by trying the cheat add_key_field() to handle it like: 2) ' GE AND LE ' As we didn't really replace the BETWEEN operation with 'ge' and 'le', add_key_field() still handled it as a 'BETWEEN' and compared the (swapped) arguments and for equality. If they was equal, the condition 1) was incorrectly 'optimized' to: 3) ' EQ ' This fix uses the 'num_values' argument to detect the cases where the BETWEEN should not be interpreted as a BETWEEN anymore. if 'num_values < 2' it has logicaly been transformed to a GE / LE. 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:23:13 +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:23:13 +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-10-29 08:23:06 +0000 +++ b/sql/sql_select.cc 2010-12-03 14:23:13 +0000 @@ -3363,6 +3363,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; --===============0201345201362454360== 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\ # ro4mihhpvk03en4d # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: 5e809e9b2c212bf9892585eeb4c98e938e2323b6 # timestamp: 2010-12-03 15:23:17 +0100 # base_revision_id: georgi.kodinov@stripped\ # l819wohslm0k87fn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWd9NAcoABorfgAB6Wff//3// /+q////0YAweXn27deS93b2cgB6MOAai23t0W3TZW61U1qloZJpJtKejKenpCn6J6NR6p+Q0o2o0 0GagAGgYIyDU0I0Yp+ppMpoNPU8o0DagAAAAAAAOMmTRoDRpiMjQxDAmjTEGI0GEABgkJJpDSaZU 9D0FPU/Un6SemoxPKAAHqAaGg0NPUEUhCYjRPTUyTKPSbSeaCCZGQyNA0PUaZPUZpAkkCGgBNTBo jU2oak/Uj9AjTUbU0PUaMnqAGSrLX2eFksbAgo+4adilA4QiIUegKKVw2VS/lTMP6pdcp2qTUrT3 KdanWpoWoU3G3Sct2SAkcKJLAH16XOR3ewdWDFvjokojzY8Nz455l2LcT0FGHfBtPQDfA/Htywz7 SZm23ocbbcdONumUx/QdEhjIc0hJCpCSEkLSFSG6QlziO3c8Q9knWnQZsYaba0j9Mo1QSI2Q33bQ KOXjtj84hzU0H9Cn0AXr1jOpauE379DarYOCD5DCBXB8wCRkqgH5r2vteZiIIiIdm0D3J3+/xWar O8CXgMAWgwEBE61UP8GOvvH0BAsfa8cw6YRDIZvvthujwh8Hx39OQLS02m29YfAECyq1DGPvXgU2 220MDMMzDIYIna3qgBFP7hveCR0ofeewmdDpPzDOHaEYlHcDlULoaCAQgEMgyoLU+S481lQtxxQQ fo4ajiDZTaq4o5K62/EJSU5LNcP/ISGGRGYmQlaCs/blMQs6eqyWd2QHAEbTsA/yavxKmBrtwbro bI6XU+SlcTLz7qqqqVORAhkZ2UBCktb5nzBImXBIoFTrbjku68WyxxKUtQUmeNZQjhUt1MjL85sI gtwa1JoBKKJpbtZl0+yYco2+vj3jypu1EsQg8+5EKI9h1DZqmdlbjapxXne2YNQMQeB2QxZxsfyV WOz7grqzo5PfJNAJG5g6ahnae482cLVgYE9q1LQ+0AqLzWo9a5FG5PUcI5NIG+CLGqJ74fBc3MsO daUt1szyyTEiYoIiTHZJTlZkh4gqnN0OW8GwILyIzK+iImGAEIIiDDESLwHhEiEoGhpJRGQoADgb zAwUFDhyiIq9gijgvcXR2TkVTiikThOuHMw4xwTh6bncPDVlxbl270uRpEUFkIzBvoSRvhAvhb2B 4pUCR2dZANQg+zlUX55jnuC3MHRV7owDsNkhKSnMfzdwn2VCOJWosxJtUXkhEU4o/9ftIalwYWVG Lh3LDpV1wo9JbNVmrMyknI7oYrZuteKkuPy5hy3R7FobZcyRQH0hzNARMS0ysEcE4zMDjxpVp1DL WcVQauFwWlMSw/LtKjefoL9u9NTLZ7Y8DARKhbzXCaNAkPEPEdKQiugPGO3evSbCglicRimDQWRq big79xekRr7fZvxSL72ZwkmezjEzT0c3DiTCJXkiBBaDEhnlej8xqQrEhx1r9IjGVhYdRvdaUn2C OQsOFDsH2LTwgYzON1B3kImJm4KtbT4qkOMt3YWXnv7C4zOBD6zyKI2NlOlbVYQuzvKpkRi5qhx9 DqTP6LZErisbPRFe5wkOwCGC2YNyROHXxTeDxeFYmRivFdB0wsBh+EThLA9VdiHecjkqzMpk2TSz C1qa03BcCmi4x3qx82MJVznEuN+95R2ldwDnF8S2DYj68SktneXEyBddI4tSBcMBkBjk28yC0nnN X47mwVO9yNGcUXww2J2EgWd6Q7yYBiLHF+TG95Iz4WeeWYVZ05hMGrNFrWlOVVOzXeLDSbaZ7vmU bNb9nWsy3LChiZbl7VY2FsaBqpGdncGZIZ8oecSPL6jUAL6j5g2Vng2024IE2HEHoDCiwQEj7xBk XKfA7IAYl2mAUFJpAXyBT4qL/QKXKLf7wBmWnUovWaBT4mSmZFOiC8AasC5TEUqKEDiRmCcnSKHs QCWgI1AppQbYBg6wU7VAoKMiEmAMyhAFyIZGAKZiDVaewU4FKi+0gAfgGB2gpsKGkxkQCms1KZgU 0kwvCwJFiizsiOwyKkQoWpBiosADYA2kHcJwR3t328/zx/CdG5tdyQgUUn6s3nZiHgF6X9FL0eso Rcy9s6RwzFn4VoX2W/G1JVTtNWJrI+BofE9BwZGYvzjqw+49e09fnTwkimGNSMsW+/RKAMmQL36/ wN2AKBtZQClzpn3pD8bpOiNgrSfy2y0PZE4ny/Atz/j/McOOJ2ncQOlBobt6RySTEyw50lBR8hhG zYxBXHyr6v7i8leww3NZkinqSOXeOJtVNFD2VuQManVHUQf4BAWhzlwJbtL1N4oVsMNteuy8pUWX jdqaGzqUpcHf24+DaQZvODSjeZ0OvLLpJMg5I62KoxRLB2ZUN5gefyVqcYzHqqyK+RMwpgpbzHMk J+0OqpN6bzzds+Scjonb3N1eeStbyAgqChyZMTVrFAiFIv4kc6IZRFCLRwBbaMbzQtkEjlY21h0A 0ErxWbtqa/AY7VoRVhUJOddoSpLVaVruc9zw8Y/D08rrnukbWcJfXxG7bHo9ENSQcBLM3MkbiNIj suBihvFMPRSmOw71qbx9GDn7y0WthBQvO+KiRdjbf5RQ56sGdeurxVkkt4K7L+g9IVWiRbgzfDkL omPQS33m8ieg8xcVFQ/l3lGYMhmoLnNOj7+5Hp5vICwUDI6l00B5ujgJs6/KWBwRCUzzhSJkBiyo Cm8UeENO3V4HiwbITVYafCgrMh79oG5EkBhBxWJsBDjjjA8FYCoU7KTxNwmVjxrdqYO541MRX2kA SBgw39KubqLTQR42JG7vSqpfxQfawidpELbDxEfTsKUFIqUtorpl/rvR3QVBprzqrApFT6DFVCYv Fg9aq1ZQIk72xlBByQiweO3iJGi4g5pAQoeNZbJYhIl4iRPUSSkKePOYTHxaUtuNGUORDqCCoaZU 5GGtz0M9FDyKfjAXtR4MledSg1KWC157jeUqNIn0MMVTI5WYT4g6vB54TOFcQbsFSsa27AAApRxU jjnY0pG+wbsao1Cd2Tot8XTZMzBWeUM9lOM0KWWR0ZqokAqZzatJIvHWREMODzG5Zk7AYBhRTlrU oRSRqm4x7GUus27M1vEaElXCiNWYNperI5kFL1X7FqBYI4Oin7cTZ0Pz5pH6l+J/UGXb1w8EBlDk LDxwAene+Q3iUcEZFwf8rsHbxNPMQGcPGSZJQ5szHhyHKJqvx5GKRUW9iCs6EzI8ZHB6hI2Ew5hT kuLy9xuU85rNlxZAfQpQuOimBZlhG1UiGHAvcw2VBtlilnfEKk1zDmPLucqkiCJVtxTv9REFdgFm t1haZy1ShNCCfkYy20DAusi5ky0zXXPLSJprz4KV5gggqgjmU4VKIp5GkeyQaYypHaH22W4ty9XJ xnDjzZbFsSLbSqrfyoFjtvVq/dMRqcbVO7iHVHG2nF8r0xEREjVwe4cZ6pKykvg4ZMIZNTmJ6y84 /pGBqFRVMhIu3Ta66ctLJEjZSt4VuBGoDzWyK2PSMWKWKKCMCUsCDMeBtHPj51aVCbBORyYA8pYr rdodG0rCI0WY3IZho8BTFSqkEF8JbbBrU31JzO9zuL3YHywHqOUrnjw63BjpYdGAsb1K6ywaHvg4 rn7dYDqx7UqVvqThXnER5WrAxLRPTAfsGX3KkjkNPTpGNLtSGkUPnj46niIowDUT1uPIuJEkjyWR EyVyOgxbaU16HU/PZ/8XckU4UJDfTQHK --===============0201345201362454360==--