| Bug #46362 | Endpoint should be set to false for TO_DAYS(DATE) | ||
|---|---|---|---|
| Submitted: | 24 Jul 2009 9:30 | Modified: | 18 Sep 2009 9:44 |
| Reporter: | Mikiya Okuno | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.1.36 | OS: | Any |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[13 Aug 2009 23:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/80799 3068 Mattias Jonsson 2009-08-14 Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) There were multiple problems since pruning uses the field for comparison (while evaluate_join_record uses longlong) Fix was to take better care when comparing DATE vs DATETIME. And adding optimization for comparing with 23:59:59. @ mysql-test/r/partition_pruning.result Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Updated result-file @ mysql-test/t/partition_pruning.test Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Added testcases. @ sql-common/my_time.c Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) removed duplicate assignment. @ sql/item.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Changed field_is_equal_to_item into field_cmp_to_item, to better handling DATE vs DATETIME comparision. @ sql/item.h Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Updated comment @ sql/item_timefunc.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Added optimization (pruning) of DATETIME where time-part is 23:59:59 @ sql/opt_range.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Using the new field_cmp_to_item for better pruning.
[18 Aug 2009 20:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81020 3068 Mattias Jonsson 2009-08-18 Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) There were multiple problems since pruning uses the field for comparison (while evaluate_join_record uses longlong) Fix was to take better care when comparing DATE vs DATETIME. And adding optimization for comparing with 23:59:59. (Updated, failed with main.range test, now OK) @ mysql-test/r/partition_pruning.result Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Updated result-file @ mysql-test/t/partition_pruning.test Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Added testcases. @ sql-common/my_time.c Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) removed duplicate assignment. @ sql/item.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Changed field_is_equal_to_item into field_cmp_to_item, to better handling DATE vs DATETIME comparision. @ sql/item.h Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Updated comment @ sql/item_timefunc.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Added optimization (pruning) of DATETIME where time-part is 23:59:59 @ sql/opt_range.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Using the new field_cmp_to_item for better pruning. (Updated, failed with main.range test, now OK)
[26 Aug 2009 10:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81594 3068 Mattias Jonsson 2009-08-26 Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) There were a problem since pruning uses the field for comparison (while evaluate_join_record uses longlong), resulting in pruning failures when comparing DATE to DATETIME. Fix was to always comparing DATE vs DATETIME as DATETIME, by adding ' 00:00:00' to the DATE string. And adding optimization for comparing with 23:59:59, so that DATETIME_col > '2001-02-03 23:59:59' -> TO_DAYS(DATETIME_col) > TO_DAYS('2001-02-03 23:59:59') instead of '>='. @ mysql-test/r/partition_pruning.result Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Updated result-file @ mysql-test/t/partition_pruning.test Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Added testcases. @ sql-common/my_time.c Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) removed duplicate assignment. @ sql/item.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Changed field_is_equal_to_item into field_cmp_to_item, to better handling DATE vs DATETIME comparision. @ sql/item.h Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Updated comment @ sql/item_timefunc.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Added optimization (pruning) of DATETIME where time-part is 23:59:59 @ sql/opt_range.cc Bug#46362: Endpoint should be set to false for TO_DAYS(DATE) Using the new stored_field_cmp_to_item for better pruning.
[28 Aug 2009 10:57]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81843 3084 Mattias Jonsson 2009-08-28 [merge] Manual merge between bug#46362 and bug#20577. @ sql/opt_range.cc Removed duplicate code (if statement must have been duplicated during earlier merge). @ sql/sql_partition.cc After mergeing bug#46362 and bug#20577, the NULL partition was also searched when col = const, fixed by checking if = or range.
[28 Aug 2009 19:05]
Mattias Jonsson
pushed into mysql-5.1-bugteam and mysql-pe
[1 Sep 2009 12:54]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82130 3096 Mattias Jonsson 2009-09-01 post push fix for bug#20577 and bug#46362, disabling warnings
[2 Sep 2009 16:42]
Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:mattias.jonsson@sun.com-20090901125327-v3rszrg5cfwsksxc) (merge vers: 5.1.39) (pib:11)
[11 Sep 2009 23:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/83092 3602 Mattias Jonsson 2009-09-12 post push fix for mysql-pe for bug#20577 and bug#46362. fix for mysql-pe; 'Using where' should not be in the extra column after MRR and other optimizer changes. @ mysql-test/r/partition_pruning.result post push fix for mysql-pe for bug#20577 and bug#46362. fix for mysql-pe; 'Using where' should not be in the extra column after MRR and other optimizer changes.
[14 Sep 2009 16:05]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 8:21]
Jon Stephens
Documented bugfix in the 5.4.4 changelog as follows:
Partition pruning did not always work correctly when the
table's partitioning key used the TO_DAYS() function.
Set status = NDI, waiting for push to 5.1.
[18 Sep 2009 9:44]
Jon Stephens
I failed to notice 5.1.39 push already shown. Bugfix now also documented in the 5.1.39 changelog, closed.
[1 Oct 2009 5:59]
Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25]
Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25]
Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:20]
Paul DuBois
Move 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50]
Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[13 Oct 2009 20:30]
James Day
The fix for this bug introduced new bug #47963 "using null microsecond part (e.g. "YYYY-MM-DD HH:MM:SS.0000") in a WHERE condition may lead to wrong results due to improper DATETIMEs comparison in some cases". The fix is expected in version 5.1.41.
Description: In function Item_func_to_days::val_int_endpoint(), it leaves incl_endp intact if the argument data type is DATE like below. 971 longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp) 972 { 973 DBUG_ASSERT(fixed == 1); 974 MYSQL_TIME ltime; 975 longlong res; 976 if (get_arg0_date(<ime, TIME_NO_ZERO_DATE)) 977 { 978 /* got NULL, leave the incl_endp intact */ 979 return LONGLONG_MIN; 980 } 981 res=(longlong) calc_daynr(ltime.year,ltime.month,ltime.day); 982 983 if (args[0]->field_type() == MYSQL_TYPE_DATE) 984 { 985 // TO_DAYS() is strictly monotonic for dates, leave incl_endp intact 986 return res; 987 } However, it should be set to false IMHO, as TO_DAYS() is MONOTONIC_STRICT_INCREASING for DATE values. So, any comparison should not include endpoints whether left_endp is true or false. Due to this, range partitions using TO_DAYS() function cannot be pruned correctly if the given value is same as the range border. How to repeat: mysql> CREATE TABLE range_test ( -> id int(10) unsigned NOT NULL, -> cdate datetime NOT NULL, -> PRIMARY KEY (id, cdate) -> ) PARTITION BY RANGE (TO_DAYS(cdate )) ( -> PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')), -> PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')), -> PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')), -> PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')), -> PARTITION p20090405 VALUES LESS THAN MAXVALUE -> ); Populate rows into this table, and execute the following EXPLAIN commands. mysql> explain partitions select * from range_test where cdate < cast('2009-04-03' as datetime); +----+-------------+------------+---------------------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+---------------------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | SIMPLE | range_test | p20090401,p20090402 | index | NULL | PRIMARY | 12 | NULL | 10000 | Using where; Using index | +----+-------------+------------+---------------------+-------+---------------+---------+---------+------+-------+--------------------------+ 1 row in set (0.00 sec) mysql> explain partitions select * from range_test where cdate < cast('2009-04-03' as date); +----+-------------+------------+-------------------------------+-------+---------------+---------+---------+------+-------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------------------------------+-------+---------------+---------+---------+------+-------+--------------------------+ | 1 | SIMPLE | range_test | p20090401,p20090402,p20090403 | index | NULL | PRIMARY | 12 | NULL | 10000 | Using where; Using index | +----+-------------+------------+-------------------------------+-------+---------------+---------+---------+------+-------+--------------------------+ 1 row in set (0.00 sec) Another thought is that include_endpoint is set to false in get_partition_id_range_for_endpoint() call when using DATETIME, and include_endpoint is set to true when using DATE. Please refer to the following two stack traces. The former is a DATETIME case, and the latter is a DATE case. (gdb) bt #0 Item_func::get_arg0_date () at item_func.h:152 #1 Item_func::get_arg0_date () at item_func.h:152 #2 Item_func_to_days::val_int_endpoint (this=0x1010b46e8, left_endp=false, incl_endp=0x10983eb5c) at item_timefunc.cc:976 #3 0x0000000100006ede in get_partition_id_range_for_endpoint (part_info=0x10a9f2060, left_endpoint=<value temporarily unavailable, due to optimizations>, include_endpoint=false) at item_timefunc.cc:976 #4 0x00000001000075d7 in get_part_iter_for_interval_via_mapping (part_info=0x10a9f2060, is_subpart=<value temporarily unavailable, due to optimizations>, min_value=0x1010b6890 "??U?E\022", max_value=0x1010b6890 "??U?E\022", flags=0, part_iter=0x1010b46e8) at item_timefunc.cc:976 #5 0x0000000100149755 in find_used_partitions (ppar=0x1010b46e8, key_tree=0x0) at item_timefunc.cc:976 #6 0x000000010014eb87 in prune_partitions (thd=0x101034200, table=0x10a9f2e00, pprune_cond=0x1010dc600) at item_timefunc.cc:976 #7 0x00000001000fcc04 in JOIN::optimize (this=0x0) at item_timefunc.cc:976 #8 0x00000001001036bd in mysql_select (thd=0x0, rref_pointer_array=0x1010361a0, tables=0x1010dc078, wild_num=1, fields=<value temporarily unavailable, due to optimizations>, conds=0x4, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x1010dc7f0, unit=0x101035ba8, select_lex=0x10a9f2060) at item_timefunc.cc:976 #9 0x00000001001039f6 in mysql_explain_union (thd=0x10a9f2060, unit=0x0, result=0x10983eb5c) at item_timefunc.cc:976 #10 0x00000001000a7b58 in execute_sqlcom_select (thd=0x10983eb5c, all_tables=0x1010dc078) at item_timefunc.cc:976 #11 0x00000001000ae2c4 in mysql_execute_command (thd=0x10983eb5c) at item_timefunc.cc:976 #12 0x00000001000aecb8 in mysql_parse (thd=0x1010b46e8, inBuf=0x0, length=178200672, found_semicolon=0x10983eb5c) at item_timefunc.cc:976 #13 0x00000001000af684 in dispatch_command (command=<value temporarily unavailable, due to optimizations>, thd=0x0, packet=0x100fd6001 "", packet_length=90) at item_timefunc.cc:976 #14 0x00000001000b010c in do_command (thd=0x10a9f2060) at item_timefunc.cc:976 #15 0x00000001000a4173 in handle_one_connection (arg=0x1010b46e8) at item_timefunc.cc:976 #16 0x00007fff8222ae8b in _pthread_start () #17 0x00007fff8222ad4d in thread_start () (gdb) bt #0 Item_func::get_arg0_date () at item_func.h:152 #1 Item_func::get_arg0_date () at item_func.h:152 #2 Item_func_to_days::val_int_endpoint (this=0x1010b4e58, left_endp=false, incl_endp=0x10983eb5c) at item_timefunc.cc:976 #3 0x0000000100006ede in get_partition_id_range_for_endpoint (part_info=0x1010b4b40, left_endpoint=<value temporarily unavailable, due to optimizations>, include_endpoint=true) at item_timefunc.cc:976 #4 0x00000001000075d7 in get_part_iter_for_interval_via_mapping (part_info=0x1010b4b40, is_subpart=<value temporarily unavailable, due to optimizations>, min_value=0x10aa18e90 "??U?E\022", max_value=0x10aa18e90 "??U?E\022", flags=0, part_iter=0x1010b4e58) at item_timefunc.cc:976 #5 0x0000000100149755 in find_used_partitions (ppar=0x1010b4e58, key_tree=0x0) at item_timefunc.cc:976 #6 0x000000010014eb87 in prune_partitions (thd=0x101034200, table=0x10a9f1c00, pprune_cond=0x1010dc5f8) at item_timefunc.cc:976 #7 0x00000001000fcc04 in JOIN::optimize (this=0x0) at item_timefunc.cc:976 #8 0x00000001001036bd in mysql_select (thd=0x0, rref_pointer_array=0x1010361a0, tables=0x1010dc070, wild_num=1, fields=<value temporarily unavailable, due to optimizations>, conds=0x4, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x1010dc7e8, unit=0x101035ba8, select_lex=0x1010b4b40) at item_timefunc.cc:976 #9 0x00000001001039f6 in mysql_explain_union (thd=0x1010b4b40, unit=0x0, result=0x10983eb5c) at item_timefunc.cc:976 #10 0x00000001000a7b58 in execute_sqlcom_select (thd=0x10983eb5c, all_tables=0x1010dc070) at item_timefunc.cc:976 #11 0x00000001000ae2c4 in mysql_execute_command (thd=0x10983eb5c) at item_timefunc.cc:976 #12 0x00000001000aecb8 in mysql_parse (thd=0x1010b4e58, inBuf=0x0, length=17517376, found_semicolon=0x10983eb5c) at item_timefunc.cc:976 #13 0x00000001000af684 in dispatch_command (command=<value temporarily unavailable, due to optimizations>, thd=0x0, packet=0x100fd6001 "", packet_length=86) at item_timefunc.cc:976 #14 0x00000001000b010c in do_command (thd=0x1010b4b40) at item_timefunc.cc:976 #15 0x00000001000a4173 in handle_one_connection (arg=0x1010b4e58) at item_timefunc.cc:976 #16 0x00007fff8222ae8b in _pthread_start () #17 0x00007fff8222ad4d in thread_start () I am not certain why include_endpoint values are different in each case. Suggested fix: Set "*incl_endp = false" in line 985.