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:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.36 OS:Any
Assigned to: Mattias Jonsson
Triage: Triaged: D2 (Serious)

[24 Jul 2009 9:30] Mikiya Okuno
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(&ltime, 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.
[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.