MySQL Bugs: #20577: Partitions: use of to_days() function leads to selection failures

Bug #20577	Partitions: use of to_days() function leads to selection failures
Submitted:	20 Jun 2006 15:34	Modified:	15 Sep 2009 8:04
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug-log	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
I create a table which is partitioned using to_days(date_column).
I insert '0001-01-01' in the date column.
I try to select, where the column is "< '1000-01-01'".
I know that the manual says not to use dates before 1000-01-01,
so this is not a bug that is likely to affect sensible users,
but my concern is that the results of a SELECT should be the same
whether the table is partitioned or non-partitioned.
This is related to an earlier comment on bug#18198.
It is possibly also related to the fact that to_days('0001-01-01')=to_days('2001-01-01').

How to repeat:
mysql> create table tsq (s1 date) partition by range (to_days(s1)) (partition p1
values less than (365243), partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tsq values ('0001-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tsq where s1 < '1000-01-01';
Empty set (0.00 sec)

Thank you for a bug report. Verified just as described with 5.1-BK (ChangeSet@1.2227, 2006-06-20 12:24:12+02:00) on Linux.

this bug is also present in mysql 4.1.21

the issue is that in to_days a date like '0001-01-01' is interpreted as '2001-01-01'. This applies for years 0 to 199.

create table dt ( a datetime ); insert into dt select '0001-01-01';
select to_days(a), from_days(to_days(a)) from dt;

the date is stored correctly in the table, but mistaken by to_days.

Here is another test case. This doesn't depend on
to_days() but it does use early dates, and it does
work when the table is not partitioned. When the
table is partitioned, the final SELECT fails.

create table t5 (s1 date, primary key (s1) using hash) engine=memory partition by key(s1) partitions 999;
insert into t5 values ('0000-00-01');
select * from t5 where s1 between '0000-00-01' and '0000-00-02';
insert into t5 values ('0000-01-01'),('0001-01-01');
select * from t5 where s1 between '0000-00-01' and '0000-00-02';

I checked the test case you gave which you used as proof that partitioning had a bug related to
dates like 0000-01-01 and so forth.

I discovered that this is really a bug somewhere else.

I made two test cases based on your test case.

create table t1 (a date, primary key (a) using hash) engine=memory;
insert into t1 values ('0000-01-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';
insert into t1 values ('0000-00-01'), ('0001-01-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';

This test case reports no rows in result set of last query.

create table t1 (a date, primary key (a) using hash) engine=memory;
insert into t1 values ('0000-00-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';
insert into t1 values ('0000-01-01'), ('0001-01-01');
select * from t1;
select * from t1 where a between '0000-00-01' and '0000-00-02';

This is your test case where the last select reports the correct result.

I found that the problem is that the item used for select checks for some
reason store some state which changes the behaviour of the query
dependent on the order the rows arrive.

I did also analyse the reason of why this happens:
The date '0000-00-01' is stored as the value 1

In get_datetime_value the value is converted using the following:
    if (item->field_type() == MYSQL_TYPE_DATE || value < 100000000L)
      value*= 1000000L;

Thus the value is set 1 million instead of 1, further down this value is
inserted into a cache item.

This means that the next val_int is performed the value is 1 million but
the comparison above is comparing against 100 million and thus the
cached value is changed to 1 million * 1 million and grieve strikes :)

The problem dates are the 99 first days of 0000, these will be multiplied
1 million but will still not be as large as 100 million which is what is
compared against.

Thus the bug you have found is a MySQL Server bug related to DATE handling
of 0000-00-01 to 0000-.... 99 days ahead when used in WHERE clause.
Given that those dates are legal dates this sounds like a bug to me.
I'll file it as a separate bug.

At the same time I'll convert this bug to a Server Data Types category where I
will also place the new bug. The new bug is #32021.

So this bug is now dedicated to solving the problem that 0000 is converted to
2000 in some cases. As bug#32021 this is however not always the case.

The August 1 comment had a test case with dates like '0000-00-01'.
That doesn't mean the bug is solely "related to DATE handling of
0000-00-01 to 0000-.... 99 days ahead" or "dedicated to solving
the problem that 0000 is converted to 2000". The bug is about:
Partitions: use of to_days() function leads to selection failures.

Here is another test case. The dates are not in the range
"0000-00-01 to 0000-0 .... 99 days ahead". The dates are legal.

create table tv (s1 date, key(s1)) engine=innodb
partition by list (to_days(s1))
(partition p1 values in (366), partition p2 values in (null));
insert into tv values
 ('0000-00-00'),('1001-00-00'),('1001-00-01'),('1001-01-00');
select * from tv where s1 between '0000-00-00' and '1001-01-00';

If there's no partitioning I get all the rows, which is good.
If there's partitioning I get zero rows, which is bad.
"Partitions" is the appropriate category because only partitions
are affected, as far as I can tell.

After trying to fix this bug, I realized that this is actually already documented behavior after fixing bug#40972. Since the specified dates are invalid, the TO_DAYS function returns NULL, which causes the pruning to fail.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html

If we were to fix this, then we would have to fix every date function to round the value up or down depending if it should include the endpoint (>=, <=) or not (<, >).

Example:
SELECT * FROM t WHERE c BETWEEN '2000-02-31' and '2009-02-31'

If one would not skip pruning, one would have to first round the values to '2000-03-01' and '2009-02-28' to be able to do the pruning, as well as include the partition that holds the NULL values.

(the patch for bug#46362 can be of use if one would implement this.)

Please look at the bug description and all the comments.

* It is of course true, and was stated in the original report,
  that the manual says dates from 0001-01-01 to 0999-12-31 are
  outside the "supported range", but for those it was explained
  "but my concern is that the results of a SELECT should be the same
  whether the table is partitioned or non-partitioned."
  We accept the dates without even a warning, so they'll exist.

* The additional test case dated 2007-11-01 says:
  "Here is another test case. The dates are not in the range
  "0000-00-01 to 0000-0 .... 99 days ahead". The dates are legal."
  I checked the comment's test case today, it still behaves as
  described, in 5.1, pulled today.

We all know it is difficult to support all of MySQL's date rules.
Therefore it's understandable to leave this low-priority bug unfixed,
as has been the case for three years. But, alas, it's wishful
thinking to just say it's not a bug. I've restored the status to "verified".

(By valid dates I mean dates which actually occur, not virtual dates like month or day equal to 0 or 30:th february etc.)

All valid dates from 0001-01-01 to 9999-12-31 should work, if not then it is a a more serious bug than what we discuss here, if so please file a new bug report for that.

Invalid dates returns NULL from TO_DAYS (and probably all other date to integer functions).
So if we accept invalid dates TO_DAYS is no longer monotonically increasing, and the pruning optimizations are wrong, due to false assumption (at least monotonically increasing), since
a < b -> TO_DAYS(a) <= TO_DAYS(b) is no longer true.
'2009-00-01' < '2009-01-00' -> NULL <= NULL i.e. NULL.

Some technical ramblings for my understanding:
The problem is that the pruning does not handle the above case due to invalid dates, and therefore no partitions are used to serve the query. This could probably be fixed by allowing invalid dates in the val_int_endpoint methods, so that it both sets the return value as the TO_DAYS of the nearest correct date, rounding up or down accordingly to the left_endp variable. And also set it as NULL so that the partition containing the NULL partition also are included.

There might be some additional problems with indexes and ordering when the pruning problem is solved.

When fixing this bug it will also change the pruning a bit:
For range partitioning, it must also include the first partition (holding the NULL values) for queries using range search (i.e. <, >, <=, >=, BETWEEN) to be able to find 'virtual' dates like '2000-00-00'.

This can affect performance for range partitioned tables on TO_DAYS() that have a large first partition. Work around is to create a first partition with VALUES LESS THAN (0).

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/81225

3072 Mattias Jonsson	2009-08-20
      Bug#20577: Partitions: use of to_days() function leads to selection failures
      
      Problem was that the partition containing NULL values
      was pruned away, since '2001-01-01' < '2001-02-00' but
      TO_DAYS('2001-02-00') is NULL.
      
      Added the NULL partition for RANGE/LIST partitioning on TO_DAYS()
      function to be scanned too.
      
      Also fixed a bug that added ALLOW_INVALID_DATES to sql_mode
      (SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
      partitioned table would add it).
     @ mysql-test/include/partition_date_range.inc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added include file to decrease test code duplication
     @ mysql-test/r/partition_pruning.result
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added test results
     @ mysql-test/r/partition_range.result
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Updated test result.
        This fix adds the partition containing NULL values to
        the list of partitions to be scanned.
     @ mysql-test/t/partition_pruning.test
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added test case
     @ sql/item.h
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added MONOTONIC_*INCREASE_NOT_NULL values to be used by TO_DAYS.
     @ sql/item_timefunc.cc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Calculate the number of days as return value even for invalid dates.
        This is so that pruning can be used even for invalid dates.
     @ sql/opt_range.cc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Fixed a bug that added ALLOW_INVALID_DATES to sql_mode
        (SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
        partitioned table would add it).
     @ sql/partition_info.h
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Resetting ret_null_part when a single partition is to be used, this
        to avoid adding the NULL partition.
     @ sql/sql_partition.cc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Always include the NULL partition if RANGE or LIST.
        Use the returned value for the function for pruning, even if
        it is marked as NULL, so that even '2000-00-00' can be
        used for pruning, even if TO_DAYS('2000-00-00') is NULL.
        
        Changed == to >= in get_next_partition_id_list to avoid
        crash if part_iter->part_nums is not correctly setup.

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/81595

3072 Mattias Jonsson	2009-08-26
      Bug#20577: Partitions: use of to_days() function leads to selection failures
      
      Problem was that the partition containing NULL values
      was pruned away, since '2001-01-01' < '2001-02-00' but
      TO_DAYS('2001-02-00') is NULL.
      
      Added the NULL partition for RANGE/LIST partitioning on TO_DAYS()
      function to be scanned too.
      
      Also fixed a bug that added ALLOW_INVALID_DATES to sql_mode
      (SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
      partitioned table would add it).
     @ mysql-test/include/partition_date_range.inc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added include file to decrease test code duplication
     @ mysql-test/r/partition_pruning.result
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added test results
     @ mysql-test/r/partition_range.result
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Updated test result.
        This fix adds the partition containing NULL values to
        the list of partitions to be scanned.
     @ mysql-test/t/partition_pruning.test
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added test case
     @ sql/item.h
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Added MONOTONIC_*INCREASE_NOT_NULL values to be used by TO_DAYS.
     @ sql/item_timefunc.cc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Calculate the number of days as return value even for invalid dates.
        This is so that pruning can be used even for invalid dates.
     @ sql/opt_range.cc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Fixed a bug that added ALLOW_INVALID_DATES to sql_mode
        (SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
        partitioned table would add it).
     @ sql/partition_info.h
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Resetting ret_null_part when a single partition is to be used, this
        to avoid adding the NULL partition.
     @ sql/sql_partition.cc
        Bug#20577: Partitions: use of to_days() function leads to selection failures
        
        Always include the NULL partition if RANGE or LIST.
        Use the returned value for the function for pruning, even if
        it is marked as NULL, so that even '2000-00-00' can be
        used for pruning, even if TO_DAYS('2000-00-00') is NULL.
        
        Changed == to >= in get_next_partition_id_list to avoid
        crash if part_iter->part_nums is not correctly setup.

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.

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

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)

A big hurray to Mattias Jonsson for fixing this old bug.

From the comments, I gather that he actually fixed two other bugs:
"        Changed == to >= in get_next_partition_id_list to avoid
        crash if part_iter->part_nums is not correctly setup."
"        Fixed a bug that added ALLOW_INVALID_DATES to sql_mode
        (SELECT * FROM t WHERE date_col < '1999-99-99' on a RANGE/LIST
        partitioned table would add it)."
Perhaps the docs person will decide that these merit
separate mention in the list of changes.

The change that I did for:
"        Changed == to >= in get_next_partition_id_list to avoid
        crash if part_iter->part_nums is not correctly setup."

does not fix a found bug, rather adding safety IF part_iter->part_nums is incorrect, which it should never be. But there has been a bug in get_next_partition_id_range (bug#40972), that's why I changed it in _list too...

Documented bugfix in the 5.1.39 changelog as follows:

        The use of TO_DAYS() in the partitioning expression led to
        selection failures when the column having the date value
        contained invalid dates. This occurred because the function
        returns NULL in such cases, and the partition containing NULL
        values was pruned away. For example, this problem occurred if
        '2001-02-00' was inserted into a DATE column of such a table,
        and a subsequent query on this table used WHERE date_col <
        '2001-02-00'; '2001-01-01' is less than '2001-02-00' but
        TO_DAYS('2001-02-00') is NULL, and so the row containing
        '2001-01-01' was not returned. Now, for tables using RANGE or
        LIST partitioning and TO_DAYS() in the partitioning expression,
        the NULL partition is also scanned instead of being ignored.

        The fix for this issue also corrects misbehavior such that a
        query of the form SELECT * FROM table WHERE date_col <
        date_val on a table partitioned by RANGE or LIST was handled as
        though the server SQL mode included ALLOW_INVALID_DATES even if
        this was not actually part of the server SQL mode at the time
        the query was issued.

Set status to NDI, waiting for merge to 5.4.

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.

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)

Also documented bugfix in the 5.4.4 changelog. Closed.

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)

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)

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)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

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)