From: Ole John Aske Date: December 21 2010 10:38am Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3398) List-Archive: http://lists.mysql.com/commits/127377 Message-Id: <20101221103816.64EC3223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0610217540039383759==" --===============0610217540039383759== 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 3398 Ole John Aske 2010-12-21 SPJ-scan-scan: Updated cherry picked fix for bug 57030: 'BETWEEN evaluation is incorrect' after reviewers had some suggestions for improvements / rewrite 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-12-03 14:55:15 +0000 +++ b/mysql-test/r/range.result 2010-12-21 10:38:10 +0000 @@ -1682,7 +1682,7 @@ pk i4 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 +1 SIMPLE t1 const i4_uq i4_uq 5 const 1 SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; pk i4 1 10 @@ -1752,5 +1752,19 @@ SELECT * FROM t1 WHERE i4 BETWEEN 10 AND pk i4 1 10 2 20 +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; +pk i4 pk i4 +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; +pk i4 pk i4 DROP TABLE t1; End of 5.1 tests === modified file 'mysql-test/t/range.test' --- a/mysql-test/t/range.test 2010-12-03 14:55:15 +0000 +++ b/mysql-test/t/range.test 2010-12-21 10:38:10 +0000 @@ -1381,6 +1381,14 @@ EXPLAIN SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +#Should detect the EQ_REF 't2.pk=t1.i4' +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; + +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; DROP TABLE t1; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-12-07 15:06:29 +0000 +++ b/sql/sql_select.cc 2010-12-21 10:38:10 +0000 @@ -3424,27 +3424,7 @@ add_key_field(KEY_FIELD **key_fields,uin eq_func is NEVER true when num_values > 1 */ if (!eq_func) - { - /* - Additional optimization: if we're processing - "t.key BETWEEN c1 AND c1" then proceed as if we were processing - "t.key = c1". - TODO: This is a very limited fix. A more generic fix is possible. - There are 2 options: - A) Make equality propagation code be able to handle BETWEEN - (including cases like t1.key BETWEEN t2.key AND t3.key) - B) Make range optimizer to infer additional "t.key = c" equalities - and use them in equality propagation process (see details in - OptimizerKBAndTodo) - */ - 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; - } - + return; if (field->result_type() == STRING_RESULT) { if ((*value)->result_type() != STRING_RESULT) @@ -3640,9 +3620,65 @@ add_key_fields(JOIN *join, KEY_FIELD **k case Item_func::OPTIMIZE_KEY: { Item **values; - // BETWEEN, IN, NE - if (is_local_field (cond_func->key_item()) && - !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) + /* + Build list of possible keys for 'a BETWEEN low AND high'. + It is handled similar to the equivalent condition + 'a >= low AND a <= high': + */ + if (cond_func->functype() == Item_func::BETWEEN) + { + Item_field *field_item; + bool equal_func= FALSE; + uint num_values= 2; + values= cond_func->arguments(); + + bool binary_cmp= (values[0]->real_item()->type() == Item::FIELD_ITEM) + ? ((Item_field*)values[0]->real_item())->field->binary() + : TRUE; + + /* + Additional optimization: If 'low = high': + Handle as if the condition was "t.key = low". + */ + if (!((Item_func_between*)cond_func)->negated && + values[1]->eq(values[2], binary_cmp)) + { + equal_func= TRUE; + num_values= 1; + } + + /* + Append keys for 'field value[]' if the + condition is of the form:: + ' BETWEEN value[1] AND value[2]' + */ + if (is_local_field (values[0])) + { + field_item= (Item_field *) (values[0]->real_item()); + add_key_equal_fields(key_fields, *and_level, cond_func, + field_item, equal_func, &values[1], + num_values, usable_tables, sargables); + } + /* + Append keys for 'value[0] field' if the + condition is of the form: + 'value[0] BETWEEN field1 AND field2' + */ + for (uint i= 1; i <= num_values; i++) + { + if (is_local_field (values[i])) + { + field_item= (Item_field *) (values[i]->real_item()); + add_key_equal_fields(key_fields, *and_level, cond_func, + field_item, equal_func, &values[0], + 1, usable_tables, sargables); + } + } + } // if ( ... Item_func::BETWEEN) + + // IN, NE + else if (is_local_field (cond_func->key_item()) && + !(cond_func->used_tables() & OUTER_REF_TABLE_BIT)) { values= cond_func->arguments()+1; if (cond_func->functype() == Item_func::NE_FUNC && @@ -3656,21 +3692,6 @@ add_key_fields(JOIN *join, KEY_FIELD **k cond_func->argument_count()-1, usable_tables, sargables); } - if (cond_func->functype() == Item_func::BETWEEN) - { - values= cond_func->arguments(); - for (uint i= 1 ; i < cond_func->argument_count() ; i++) - { - Item_field *field_item; - if (is_local_field (cond_func->arguments()[i])) - { - field_item= (Item_field *) (cond_func->arguments()[i]->real_item()); - add_key_equal_fields(key_fields, *and_level, cond_func, - field_item, 0, values, 1, usable_tables, - sargables); - } - } - } break; } case Item_func::OPTIMIZE_OP: --===============0610217540039383759== 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\ # uggochx2uza194eu # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: 01cab351f1bb9a3f19beda0edb7cb580981bdfec # timestamp: 2010-12-21 11:38:16 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # ohqk2q83bou0prz4 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWc4S2PwABPR/gFVwACh5//// /3f+6r////pgCh9eyby3Up0NZTW2xlo0aOjQAUKMNEI0hqHo1NBp6T0T0hieao9I08oNqAAAA9QB zTEZGTTJoBkNGQyZAAADI0yNAwhkCUU1PNTQajJqZqeqbU9IZqaaHpD1NNAAAAANACU0iZEp4p4K D9EnqPUaBkY9QTEep6jI0ANNAPQhzTEZGTTJoBkNGQyZAAADI0yNAwhkCSIgE0xMmgTTU8SY1GTU 8kaE2mU/RQeU9TEaaG0mlAIWnWfpzHgew/OU/QiECJ8SRLg3A/EJZimg+ZUdw9Q6O8TEFBoGLrP4 xG9JiUduolxQ9363hSLWLbOKxK6/Dr2KZHYj0xw1MiplNWylk94pZ803IbBAsCvlJ4xBrw2TtdG9 q/eWO4+eURoYCVepuq2SSuMKSaaWBuoAS4VuCEpAEui3g2c0/qdH8Xc+77h59TqSFEjv/YBy0Lgt 5t9uZs7JISEmMX6G2Y3hFeSiMa0ailDAZYuoJqKZx/NUss3Hhqr8Yu7z7eMnC4r4boVYlKARDcqv xgCSJLDahkaUcYA6W64AXTq5yRUhWpQbSx46qSbvF/VcXYOIVGsrun1jZKpoYJDEkZLqRYDFkXpL hc4K8pSVTAe8CsNgWhWA4HA4IiUkl5FK6Cds1jWEdGxhwPzz1wxkaFzkplmS4RNGQLHjwhBDBssz Ik0lQuhMOm4/H4azSw9BlESQQEGRHe3kjkU9fM8N4c4f2vIBJvcrgzTHcFYjr0e7Qoq3y5KxlkM0 O8xInqASkagRAKNCylkr1rFATgnmVYFI7dbeAOXnmZteeUzQEfefT2H1fX6zb4TTlaAzLkgMiSZA P50J04y6A6Cno6EzhRhMu5RQtbIZshawh4VhaWBkMTkQCYwOCxgiURxjC83HsDr54NKu0SJlB94p F80sjlWY20xvXu2Hghx84FLZ90QD4aXSWGM4isQYsZRH49RVmPPSIhHKTghlQ/jzatdV9KhuCshI GHJxV6Bo1BBBxtb8xVkKxCHuHpcOIrnUxEyrOwtJdPogBMyFUCJy9gm0CHEYsGUhZfFbi4j9pKE8 JbL3E95Kbqiu85HirfcI8hlr2aSXM8E2xMxElovMyxkMSJrkDJMZMKhEVoae+yyacdWXk5t1ffmu DAbM016qarLIgsJVrYcuHB3ceaJwaI9SIUrshUBfOrKJeHOmCHGNUxcEjCX3zNmbKZGsONg5RkVb y2FJogF5r4C345WuJ9rpvSeAssqwOBUWnTAM9ZBjbLImc3ojMNTqqvhmSeUoP1Z86DInQIEmjuEx ZWYbcMzphsRSxQxjHkaTYueOcu85RgdJhXpqIDTuiB0jGBq1cKC+kyGm0xI21PJLikQ3XvEKFN1I VI+KlMBw185bVlgUisMxjKcymOYoMK2KUiPYdPQtLkuggkxGcvxFZPQyQkZGSgulsnfNrrbrxx6E 92lyV4VLQireF79xUX3DCmaiDCmrwpEadfLxv3ccnz0MOUgyqq4t7cDLxBNQaX8K7XiFf8JvFnq6 +KcvXGAwmlLuj1KSwk6y5kjdQgPv+Idx8KQb1hxs86xJMkscGY750ffuelAcQNdGX9A06VreB/BE ecsALoiJLlAd2REtCjMM1qgTVAYY5QrReE2TB8G+I7ON83sAcK2QwE2cVC+YEc6Ewk0AU+YWUB81 n/g1olChBcZQzAYmQJLsQJ0ZP1/YC7I19xSJZjYLCNRYJOBKEoZDWYSEkjc8Ss977UXi7kFTzdFq uz5hobWBDnkZxBAcW1Y2BpUBJmgBAcwNRnH4ptFmomOb4Wg+Bo7j20o+Ahk57q/Lk9jMe05jZ76n VxQN8Rj7bkBix8f7lgegt1zxEuIaE9okQ/2+qCD0yQSf5HfsxYhJbt6Cw99S+W5B8kBvNXF8/NqN uQ9eUsPgdNX4GcwNp8uBmHLDSXHEn6zKfXs1EhvTJVHiMrjl8ibkdSFn2b0OMHUea5hjEiaigdH9 1QGQ4rHmOHpLKNGaoRogJgKz5Ga4wHY+lTcjLZFwlzbMjNchdIW2ZJEB3i63ptunZpUaKEURJkQi lI6TI0jkvVt5is8fM+aUJwoic24iddYZdtAQJ+06z5PEYZMYv2yzEBAecTLSaVBs5QzPiR4nX51k WVAYa2O9n6kWIpExIllDIRmehMUSdTl5tbZ8q38VQVYGgf7BeG82GeWZd3c5d5BIwR+UBBRoJdw9 4RW0dFPSdp0Lraqzg+8vufZbaiRJX2zpNwuWRuG4RVZUSJUtSlzpKrEPGUXSyY2cBceyE3OZBjq6 InUcO4lifiQORoKwmjaEF0FPoovIEvl+72vvuANlKTgDwkQUxgoLRuAiy7N9walsa1AVEGF1ypGh duyIi06juo4emVWeUtH7QM5ySzGNZLIpxB8onEzRVBMI1oJ4Mxo+s7wZCYI+Jhamt8tJjtSbUJFd tXP6Xfg67w8FKfRvpQUlzTngm0CZHjHJq0vihAVQU1kQZKxNl+YQtQseSsdUk+jj/Cg8DnQG6UF5 JU0DkRRSII1bye0zHmiQMF9VJAXqq7DpMthaMEgsL4BIhSKaJEDGDpLce+SKhnQFSAgKASokrKYo cthtq7SReEq5imVWFtKEbtvbMlcAbhwdkqAgoTKMd9652om9GNDKYKZpIcionk6XCDUEEA/EaFDh zjYKdWIMwyJoPSOKVKIwMvI8FAWcr7DCubcMU0DJMyXfl5NvJFy069WvOrBFlK8h/n1oDBZjmF+R /gGT9uVAXtehMkbXcyGWway8X7oDATom5QTK3jSuFOIQHJTQWoJzmO8irMGTIuPjEOgVJRZPVyOw 9CFmJ5YkD2nXkwLX9qjlYO86cO6CuImqsIJY3SJUi8zt6HU5FJGzj0DMTkp2Bz0GgqQaly16djo4 kpUzUeqxC/bv2oWmQaKKWUzCj0pNI6MyVNW9UUCMMCYUZcBhKRR22nXzXmwt1hTunzrOaUSwVKp4 FgorfegOOZGGJRuXWzMzV8BtcHHMOZTlkEGYVLp8xZKe1obNDu45hGKyxMVUAbxgPzYIDAjv+iul T+wTVE5kUI2zF2DJXMmS82q5VF5cJmSqO+rHjoPJJ6oq2QjMJcl1JMlCoNlIki44IvQKkY53TEt0 qhL4Okui7WI93q+ymvmmWAeiCShnwDbqMZpzN2CMX3kyoCy3/nvcf2OpzNkWj6YsMLYaTxPsnDEt xn0yET1kYGfsEZcikSoPiOuKTJVlltRMKRHpUylEW1sGA7cC36wgvijSKYyNH+LuSKcKEhnCWx+A --===============0610217540039383759==--