From: Mattias Jonsson Date: February 4 2011 2:08pm Subject: bzr commit into mysql-5.1 branch (mattias.jonsson:3576) Bug#28928 List-Archive: http://lists.mysql.com/commits/130417 X-Bug: 28928 Message-Id: <201102041409.p14A1F8L002255@rcsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3782583687060879295==" --===============3782583687060879295== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///Users/mattiasj/mysql-bzr/b28928-51/ based on revid:dmitry.shulga@stripped 3576 Mattias Jonsson 2011-02-04 Bug#28928: UNIX_TIMESTAMP() should be considered unary monotonic by partition pruning Preliminary patch to support range pruning for UNIX_TIMESTAMP. TODO: Fix the tests, add better tests. modified: mysql-test/r/partition.result mysql-test/t/partition.test sql/item_timefunc.cc sql/item_timefunc.h === modified file 'mysql-test/r/partition.result' --- a/mysql-test/r/partition.result 2010-12-22 09:50:36 +0000 +++ b/mysql-test/r/partition.result 2011-02-04 14:08:34 +0000 @@ -76,6 +76,15 @@ a b 2007-07-30 17:35:48 p1 2009-07-14 17:35:55 pmax 2009-09-21 17:31:42 pmax +SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +a b +2007-07-30 17:35:48 p1 +EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 +EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( PARTITION p3 VALUES LESS THAN (1247688000), PARTITION pmax VALUES LESS THAN MAXVALUE); @@ -84,6 +93,15 @@ a b 2007-07-30 17:35:48 p1 2009-07-14 17:35:55 pmax 2009-09-21 17:31:42 pmax +SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +a b +2007-07-30 17:35:48 p1 +EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 +EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 system PRIMARY NULL NULL NULL 1 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( === modified file 'mysql-test/t/partition.test' --- a/mysql-test/t/partition.test 2010-12-22 09:50:36 +0000 +++ b/mysql-test/t/partition.test 2011-02-04 14:08:34 +0000 @@ -112,10 +112,16 @@ INSERT INTO t1 VALUES ('2009-07-14 17:35 INSERT INTO t1 VALUES ('2009-09-21 17:31:42', 'pmax'); SELECT * FROM t1; +SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; ALTER TABLE t1 REORGANIZE PARTITION pmax INTO ( PARTITION p3 VALUES LESS THAN (1247688000), PARTITION pmax VALUES LESS THAN MAXVALUE); SELECT * FROM t1; +SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a between '2007-01-01' and '2007-08-01'; +EXPLAIN PARTITIONS SELECT * FROM t1 where a = '2007-07-30 17:35:48'; SHOW CREATE TABLE t1; DROP TABLE t1; === modified file 'sql/item_timefunc.cc' --- a/sql/item_timefunc.cc 2010-11-12 10:12:15 +0000 +++ b/sql/item_timefunc.cc 2011-02-04 14:08:34 +0000 @@ -1316,6 +1316,26 @@ longlong Item_func_unix_timestamp::val_i return (longlong) TIME_to_timestamp(current_thd, <ime, ¬_used); } +enum_monotonicity_info Item_func_unix_timestamp::get_monotonicity_info() const +{ + if (args[0]->type() == Item::FIELD_ITEM && + (args[0]->field_type() == MYSQL_TYPE_TIMESTAMP)) + return MONOTONIC_INCREASING; + return NON_MONOTONIC; +} + + +longlong Item_func_unix_timestamp::val_int_endpoint(bool left_endp, bool *incl_endp) +{ + DBUG_ASSERT(fixed == 1); + DBUG_ASSERT(arg_count == 1 && + args[0]->type() == Item::FIELD_ITEM && + args[0]->field_type() == MYSQL_TYPE_TIMESTAMP); + Field *field=((Item_field*) args[0])->field; + /* Leave the incl_endp intact */ + return ((Field_timestamp*) field)->get_timestamp(&null_value); +} + longlong Item_func_time_to_sec::val_int() { === modified file 'sql/item_timefunc.h' --- a/sql/item_timefunc.h 2011-01-28 12:37:39 +0000 +++ b/sql/item_timefunc.h 2011-02-04 14:08:34 +0000 @@ -349,6 +349,8 @@ public: Item_func_unix_timestamp(Item *a) :Item_int_func(a) {} longlong val_int(); const char *func_name() const { return "unix_timestamp"; } + enum_monotonicity_info get_monotonicity_info() const; + longlong val_int_endpoint(bool left_endp, bool *incl_endp); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} /* UNIX_TIMESTAMP() depends on the current timezone --===============3782583687060879295== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/mattias.jonsson@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: mattias.jonsson@stripped\ # 4gubgd4j28g595bi # target_branch: file:///Users/mattiasj/mysql-bzr/b28928-51/ # testament_sha1: f49b67d94a4221751166d219765a9514a6a2b53b # timestamp: 2011-02-04 15:08:45 +0100 # base_revision_id: dmitry.shulga@stripped\ # sakc2ctbjb9n7tqv # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWdppeVcABPJfgFAwWff//3+3 /mq////6YAn/UAAAAAAAAAAAAA1U9qjRkAAxNNAA0BkaYQAAADQ4aaYIZDTTIyYQDTQBhNGmTAAg aHDTTBDIaaZGTCAaaAMJo0yYAEDQ4aaYIZDTTIyYQDTQBhNGmTAAgaHDTTBDIaaZGTCAaaAMJo0y YAEDQSSCAACAAQBNCMmU2gmnpiTRmpjSQLw8A/6DAewPkEA+4iH8g8OB+QH+w94e8DgMIBwB4Dw3 q14QtXlgkRGCQ62eQMgtNJCtUfRA8Y8i7m2B8iNt4eOFtUJzAYcWSko6ByHIR4xnUvlFzsfIYnCR 8hGr/0hSCihfcUcwPrDmA7pD3gqI/sUeYP1DIGZhmGZMxRxVU8aX4vMWyYaLs2FMokXvGI5yPeMD nujEo2hTdoC4+oE3ruOnX2yUxG/yB8AJv/eIL2fYBZaXgQNkkS9nBd+/pxkok+fEhJxC1gcYy6ZC KK6egKSeIDvp87LuQLx6urve9vTeaaOY7s63h3XRt6JRZILvStS92IDgR3DhMMcHy0hsP0mPH3S5 D3cKqZQ8GGaf98I2wsoBeqSR4eTiUCkDziRsNJeH+Eg4ERdxzBTZYDMTdrbw6YHO15AKBxWu6U7Y GrpLir9aQaXpgC5Uplt1oc+QOgdcUl1iycmYNrbAzJBCz/ABYA9SgwAwpxkk5w4GJyA+ZopLBQB0 HCRKKQGAhEFMOJgeKZfuh5XMbiXv9FZXRNUeA71h6g4h1BvtzVz4FMYg1xKCrAIMBcyIMSXMknB7 5N5y16ZEBLaEoSS5EZFJI66l3CjZnUzygJyxvqeYxBPFBRgMGV2D6j2l2yp5QJ72+3aFvVNwComL hTlRaDsjVhAD6a7yV731B9gG8YE5IH0JJiwksBwbRxqHDwqIGLyslvGwnaWfQOpd+Uf4BWKglnM1 oel3j1QTYGossLpzTNKLEgFoYnF5kb58CHBrBKcrhJmfUExcPG5CmN9ZcXKgiY12sSEIRcxzLzQX l5kevYdxTyty0OwufgJQemSBzDgWgdMMMNlBbv7OvUpE0qnqpWsXXeTQbYlKm3nHD+jWagXD6G8F UUkGZqrzUaQgQYixO3F9xdUa8KOWpZsr7Q3grBYXmZ37yUFdYSGkmJX5byUi9lhnQ+sZNSQNpuHO viXFeWVhz2Tll0lQ5aKxXhYcDeOKzXfVfVAEnhkRHhyO6eycy5Tq0ulMVGUU0RWHLMuLuJ0qJRod R1K4fXRa4lfEH5ASWEYA+ssL5dwwLRRrmqrYolHykRT0urnB+OUpiQrKycEzUmL6AgVQz30XwLzA mMBUAov22g4K3E9ZJ9uJTUs6MUfOa2GUgrhQXqO8o0ksXyXufcFMrpXhQ9EXCaVoopSkmabwPVsY 6ftAQ//nnRwR7hWzdE5mGOYUuGQSC5+0NXoF1TLkmQvkB7g/wB+AfH6FuLMMzJmb9iGv5/B4J2J2 Vi/mhwKJ8QVoxYFYFp94WFSF72t/oY/gtCoKg/gDJzwHDASExOyuBVgYjAwKoF/RF5kCxAphWDwc XOERJiDhF4Js7bwTGRMBenBMB8z6APCgNGZ1ZBpBZGIKoCoUwMTE4RqGJQoBWkgWGQMA8CjAgewr BVlAExMGBefojzo8C3QU6KRth7/Yu8Otc3BzD5BEckOCJBW/i5A8TeH+hwOPvJ9x3hsBd9Ajzd3t PsPce0v3kSwofoUl35SGAez8PuxSCFf3mWy+r3kkyTL+z824BSiNADZVVpHsi21m+EEicpQHEe3e wBsx8vUZmo0HD3Gqcx/H/JwNx2/kTnQ965ldFmJvN4ioPiCvBclENobSU4mdb+IlJq6SOOInai8c Vfmtx26iWY0msifBQ1mmnRC9D2cnJBICMsl7YbA4J3AnCcDzSNocSPLg164Y5HgCp0VCznFpH1SI GCaqwkuK7So8cnEKY70SyKtKPxsCQDwBb3h5ZN5rpQ3yJI6Q5tJNy0mgYrhHyIj5jaQDxCg0MdGd YBMlf6KSAeiqzebW69+DP9AEgdpCgwFvY8eGOpm+bh4HYFOpJUBEDuSgi5MLs75dO52wE/lVzswl QyGKVvXRyYBkuZBBqBSYBNz2gdB3C6QqGieJ32m7rOI4vn6NFEpajI17uH6sf0HAIh5VrqQ9eGYz CoNJEOO7YC4dSzBSS+iLkqar7DUVhOddRqNp8NxYjrKQUxwOHem5Ech/YI7DPvBdpCnpUPWw5CJx yBjjWZHeYxQEwansicNHJBF1P+tYMxPw7kPBPBbtRHNuw6krLAtOR5DyXs7d81mTCYTBOaSo3Jej SMxIkXhxDTgaKg0bQWncbFE5gHIhzKys7U4+sDcHkyXMgS3lWJxC5VhBLDm8LVaQf3MliHiEiQMA 4Hh0LwkW0hPxgec4j5vSRVxZNhu4UZFYMkDDMpxhal0XxOaGB3XUGgXUQcK4bWt+BebS7vNGwK0V IvI5FZ9NBAzBSRpcMkelMhFQaYsBNU9MAyUyUjhMzPZKEQgPRSesoHgKMSZhgZk4c4XzYctGC2MU nYcjSVecrC06IRqrQd4wOAG7doWopRAJf7zAHFgWF+xIJ0nK4cmC7h2o9NFFAcrWGY8BOxBWjjfy YGE3XsGYlB7iKPYCuuKqi8nDiTFVI/SbjWVmZAwNEIEDRFGw3WjEyx5ceplmUQkCQQtmEoLxbQHy mSPJSf22rzORGZkNbWVrM1rzEI5WhPqJ6HpDonAc8aZNtCUKgjEYA0odu5baFTVWGKgytDDNTBZu SiFhqtXBlYTIDkHmZmZmbxLTNYaQtEDkzJPDBJyMHWGIVdWmfl0i8cOM3yhQC5nnkn7CUVhDzgM6 sKAjBD1LI8PzRtFrNyuLRDMfUT6JEBjiBmw1CQ9I4Ek7IrSuV3rV4PU6iTvFjCxAOSJh4YhYHsGP E7DoC6aOPugcTLgr9+ScaXgZlpLoJTRsVR1GBLtqomyVix9J5bUuY/AvPb3GvIC0F1jigwDhmW9j 0xFB+AaRwWnoYVAPCbaPBfWS3EgMsA7BxYa8y5FUzGrzh/8XckU4UJDaaXlX --===============3782583687060879295==--