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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.114 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.