Re: bzr commit into mysql-5.1 branch (mattias.jonsson:3072) Bug#20577

[V Narayanan]

OK to push!

Mattias Jonsson wrote:
> #At file:///Users/mattiasj/clones/bzrroot/b20577-51-bugteam/ based on
> revid:joro@stripped
>
>  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.
>
>     added:
>       mysql-test/include/partition_date_range.inc
>     modified:
>       mysql-test/r/partition_pruning.result
>       mysql-test/r/partition_range.result
>       mysql-test/t/partition_pruning.test
>       sql/item.h
>       sql/item_timefunc.cc
>       sql/opt_range.cc
>       sql/partition_info.h
>       sql/sql_partition.cc
> === added file 'mysql-test/include/partition_date_range.inc'
> --- a/mysql-test/include/partition_date_range.inc	1970-01-01 00:00:00 +0000
> +++ b/mysql-test/include/partition_date_range.inc	2009-08-20 20:18:45 +0000
> @@ -0,0 +1,63 @@
> +# Created for verifying bug#20577.
> +# expects TABLE t1 (... , a DATE, ...)
> +
> +--sorted_result
> +SELECT * FROM t1 WHERE a < '1001-01-01';
> +--sorted_result
> +SELECT * FROM t1 WHERE a <= '1001-01-01';
> +--sorted_result
> +SELECT * FROM t1 WHERE a >= '1001-01-01';
> +--sorted_result
> +SELECT * FROM t1 WHERE a > '1001-01-01';
> +--sorted_result
> +SELECT * FROM t1 WHERE a = '1001-01-01';
> +--sorted_result
> +SELECT * FROM t1 WHERE a < '1001-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a <= '1001-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a >= '1001-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a > '1001-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a = '1001-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a < '1999-02-31';
> +--sorted_result
> +SELECT * FROM t1 WHERE a <= '1999-02-31';
> +--sorted_result
> +SELECT * FROM t1 WHERE a >= '1999-02-31';
> +--sorted_result
> +SELECT * FROM t1 WHERE a > '1999-02-31';
> +--sorted_result
> +SELECT * FROM t1 WHERE a = '1999-02-31';
> +--sorted_result
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
> +--sorted_result
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
> +--sorted_result
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
> +if ($explain_partitions)
> +{
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
> +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
> +}
>
> === modified file 'mysql-test/r/partition_pruning.result'
> --- a/mysql-test/r/partition_pruning.result	2008-12-28 11:33:49 +0000
> +++ b/mysql-test/r/partition_pruning.result	2009-08-20 20:18:45 +0000
> @@ -1,4 +1,429 @@
>  drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +# test of RANGE and index
> +CREATE TABLE t1 (a DATE, KEY(a))
> +PARTITION BY RANGE (TO_DAYS(a))
> +(PARTITION `pNULL` VALUES LESS THAN (0),
> +PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
> +PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
> +PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
> +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
> +('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
> +SELECT * FROM t1 WHERE a < '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a <= '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-01-01';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-01-01';
> +a
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-01-01';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> +SELECT * FROM t1 WHERE a <= '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-00-00';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a <= '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a > '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a = '1999-02-31';
> +a
> +Warning	1292	Incorrect date value: '1999-02-31' for column 'a' at row 1
> +Warnings:
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
> +a
> +0001-01-01
> [phone]
> [phone]
> +# test without index
> +ALTER TABLE t1 DROP KEY a;
> +SELECT * FROM t1 WHERE a < '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a <= '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-01-01';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-01-01';
> +a
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-01-01';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> +SELECT * FROM t1 WHERE a <= '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-00-00';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a <= '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a > '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a = '1999-02-31';
> +a
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
> +a
> +0001-01-01
> [phone]
> [phone]
> +DROP TABLE t1;
> +# test of LIST and index
> +CREATE TABLE t1 (a DATE, KEY(a))
> +PARTITION BY LIST (TO_DAYS(a))
> +(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')),
> +PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
> +PARTITION `pNULL` VALUES IN (NULL),
> +PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
> +PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
> +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
> +('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
> +SELECT * FROM t1 WHERE a < '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a <= '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-01-01';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-01-01';
> +a
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-01-01';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> +SELECT * FROM t1 WHERE a <= '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-00-00';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a <= '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a > '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a = '1999-02-31';
> +a
> +Warning	1292	Incorrect date value: '1999-02-31' for column 'a' at row 1
> +Warnings:
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
> +a
> +0001-01-01
> [phone]
> [phone]
> +# test without index
> +ALTER TABLE t1 DROP KEY a;
> +SELECT * FROM t1 WHERE a < '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a <= '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-01-01';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-01-01';
> +a
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-01-01';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> +SELECT * FROM t1 WHERE a <= '1001-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> +SELECT * FROM t1 WHERE a >= '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a > '1001-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a = '1001-00-00';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a < '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a <= '1999-02-31';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a >= '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a > '1999-02-31';
> +a
> [phone]
> +SELECT * FROM t1 WHERE a = '1999-02-31';
> +a
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
> +a
> +0000-00-00
> +0000-01-02
> +0001-01-01
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
> +a
> [phone]
> [phone]
> [phone]
> +SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
> +a
> +0001-01-01
> [phone]
> [phone]
> +DROP TABLE t1;
>  CREATE TABLE t1
>  (a INT NOT NULL AUTO_INCREMENT,
>  b DATETIME,
>
> === modified file 'mysql-test/r/partition_range.result'
> --- a/mysql-test/r/partition_range.result	2008-11-04 07:43:21 +0000
> +++ b/mysql-test/r/partition_range.result	2009-08-20 20:18:45 +0000
> @@ -745,7 +745,7 @@ a
>  EXPLAIN PARTITIONS SELECT * FROM t1
>  WHERE a >= '2004-07-01' AND a <= '2004-09-30';
>  id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
> -1	SIMPLE	t1	p407,p408,p409	ALL	NULL	NULL	NULL	NULL	9	Using where
> +1	SIMPLE	t1	p3xx,p407,p408,p409	ALL	NULL	NULL	NULL	NULL	18	Using where
>  SELECT * from t1
>  WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
>  (a >= '2005-07-01' AND a <= '2005-09-30');
> @@ -772,7 +772,7 @@ EXPLAIN PARTITIONS SELECT * from t1
>  WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR
>  (a >= '2005-07-01' AND a <= '2005-09-30');
>  id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
> -1	SIMPLE	t1	p407,p408,p409,p507,p508,p509	ALL	NULL	NULL	NULL	NULL	18	Using where
> +1	SIMPLE	t1	p3xx,p407,p408,p409,p507,p508,p509	ALL	NULL	NULL	NULL	NULL	27	Using
> where
>  DROP TABLE t1;
>  create table t1 (a int);
>  insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
>
> === modified file 'mysql-test/t/partition_pruning.test'
> --- a/mysql-test/t/partition_pruning.test	2008-12-28 11:33:49 +0000
> +++ b/mysql-test/t/partition_pruning.test	2009-08-20 20:18:45 +0000
> @@ -9,6 +9,50 @@ drop table if exists t1,t2,t3,t4,t5,t6,t
>  --enable_warnings
>  
>  #
> +# Bug#20577: Partitions: use of to_days() function leads to selection failures
> +#
> +--let $explain_partitions= 0;
> +--let $verify_without_partitions= 0;
> +--echo # test of RANGE and index
> +CREATE TABLE t1 (a DATE, KEY(a))
> +PARTITION BY RANGE (TO_DAYS(a))
> +(PARTITION `pNULL` VALUES LESS THAN (0),
> + PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
> + PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
> + PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
> +if ($verify_without_partitions)
> +{
> +ALTER TABLE t1 REMOVE PARTITIONING;
> +}
> +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
> +      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
> +--source include/partition_date_range.inc
> +--echo # test without index
> +ALTER TABLE t1 DROP KEY a;
> +--source include/partition_date_range.inc
> +DROP TABLE t1;
> +
> +--echo # test of LIST and index
> +CREATE TABLE t1 (a DATE, KEY(a))
> +PARTITION BY LIST (TO_DAYS(a))
> +(PARTITION `p0001-01-01` VALUES IN (TO_DAYS('0001-01-01')),
> + PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')),
> + PARTITION `pNULL` VALUES IN (NULL),
> + PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')),
> + PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01')));
> +if ($verify_without_partitions)
> +{
> +ALTER TABLE t1 REMOVE PARTITIONING;
> +}
> +INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
> +      ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
> +--source include/partition_date_range.inc
> +--echo # test without index
> +ALTER TABLE t1 DROP KEY a;
> +--source include/partition_date_range.inc
> +DROP TABLE t1;
> +
> +#
>  # Bug#40972: some sql execution lead the whole database crashing
>  #
>  # Setup so the start is at partition pX and end is at p1
>
> === modified file 'sql/item.h'
> --- a/sql/item.h	2009-05-25 08:00:40 +0000
> +++ b/sql/item.h	2009-08-20 20:18:45 +0000
> @@ -397,13 +397,20 @@ public:
>    from INT_RESULT, may be NULL, or are unsigned.
>    It will be possible to address this issue once the related partitioning bugs
>    (BUG#16002, BUG#15447, BUG#13436) are fixed.
> +
> +  The NOT_NULL enums are used in TO_DAYS, since TO_DAYS('2001-00-00') returns
> +  NULL which puts those rows into the NULL partition, but
> +  '2000-12-31' < '2001-00-00' < '2001-01-01'. So special handling is needed
> +  for this (see Bug#20577).
>  */
>  
>  typedef enum monotonicity_info 
>  {
>     NON_MONOTONIC,              /* none of the below holds */
>     MONOTONIC_INCREASING,       /* F() is unary and (x < y) => (F(x) <=
> F(y)) */
> -   MONOTONIC_STRICT_INCREASING /* F() is unary and (x < y) => (F(x) < 
> F(y)) */
> +   MONOTONIC_INCREASING_NOT_NULL,  /* But only for valid/real x and y */
> +   MONOTONIC_STRICT_INCREASING,/* F() is unary and (x < y) => (F(x) < 
> F(y)) */
> +   MONOTONIC_STRICT_INCREASING_NOT_NULL  /* But only for valid/real x and y */
>  } enum_monotonicity_info;
>  
>  /*************************************************************************/
>
> === modified file 'sql/item_timefunc.cc'
> --- a/sql/item_timefunc.cc	2009-06-09 16:44:26 +0000
> +++ b/sql/item_timefunc.cc	2009-08-20 20:18:45 +0000
> @@ -960,9 +960,9 @@ enum_monotonicity_info Item_func_to_days
>    if (args[0]->type() == Item::FIELD_ITEM)
>    {
>      if (args[0]->field_type() == MYSQL_TYPE_DATE)
> -      return MONOTONIC_STRICT_INCREASING;
> +      return MONOTONIC_STRICT_INCREASING_NOT_NULL;
>      if (args[0]->field_type() == MYSQL_TYPE_DATETIME)
> -      return MONOTONIC_INCREASING;
> +      return MONOTONIC_INCREASING_NOT_NULL;
>    }
>    return NON_MONOTONIC;
>  }
> @@ -973,12 +973,27 @@ longlong Item_func_to_days::val_int_endp
>    DBUG_ASSERT(fixed == 1);
>    MYSQL_TIME ltime;
>    longlong res;
> -  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
> +  int dummy;                                /* unused */
> +  if (get_arg0_date(&ltime, (TIME_FUZZY_DATE)))
>    {
>      /* got NULL, leave the incl_endp intact */
>      return LONGLONG_MIN;
>    }
>    res=(longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
> +  /* Set to NULL if invalid date, but keep the value */
> +  null_value= check_date(&ltime,
> +                         (ltime.year || ltime.month || ltime.day),
> +                         (TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE),
> +                         &dummy);
> +  if (null_value)
> +  {
> +    /*
> +      Even if the evaluation return NULL, the calc_daynr is useful for pruning
> +    */
> +    if (args[0]->field_type() != MYSQL_TYPE_DATE)
> +      *incl_endp= TRUE;
> +    return res;
> +  }
>    
>    if (args[0]->field_type() == MYSQL_TYPE_DATE)
>    {
>
> === modified file 'sql/opt_range.cc'
> --- a/sql/opt_range.cc	2009-07-16 12:43:17 +0000
> +++ b/sql/opt_range.cc	2009-08-20 20:18:45 +0000
> @@ -5826,6 +5826,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
>        {
>          tree= new (alloc) SEL_ARG(field, 0, 0);
>          tree->type= SEL_ARG::IMPOSSIBLE;
> +        field->table->in_use->variables.sql_mode= orig_sql_mode;
>          goto end;
>        }
>        else
> @@ -5859,7 +5860,10 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND
>            */
>          }
>          else
> +        {
> +          field->table->in_use->variables.sql_mode= orig_sql_mode;
>            goto end;
> +        }
>        }
>      }
>  
>
> === modified file 'sql/partition_info.h'
> --- a/sql/partition_info.h	2008-11-10 20:21:49 +0000
> +++ b/sql/partition_info.h	2009-08-20 20:18:45 +0000
> @@ -300,6 +300,7 @@ static inline void init_single_partition
>  {
>    part_iter->part_nums.start= part_iter->part_nums.cur= part_id;
>    part_iter->part_nums.end= part_id+1;
> +  part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
>    part_iter->get_next= get_next_partition_id_range;
>  }
>  
>
> === modified file 'sql/sql_partition.cc'
> --- a/sql/sql_partition.cc	2009-08-06 12:28:39 +0000
> +++ b/sql/sql_partition.cc	2009-08-20 20:18:45 +0000
> @@ -2766,8 +2766,24 @@ uint32 get_list_array_idx_for_endpoint(p
>  
>    if (part_info->part_expr->null_value)
>    {
> -    DBUG_RETURN(0);
> +    /*
> +      Special handling for MONOTONIC functions that can return NULL for
> +      values that is comparable. I.e.
> +      '2000-00-00' can be compared to '2000-01-01' but TO_DAYS('2000-00-00')
> +      return NULL which cannot be compared used <, >, <=, >= etc.
> +
> +      Otherwise, just return the the first index (lowest value).
> +    */
> +    enum_monotonicity_info monotonic;
> +    monotonic= part_info->part_expr->get_monotonicity_info();
> +    if (monotonic != MONOTONIC_INCREASING_NOT_NULL && 
> +        monotonic != MONOTONIC_STRICT_INCREASING_NOT_NULL)
> +    {
> +      /* F(col) can not return NULL, return index with lowest value */
> +      DBUG_RETURN(0);
> +    }
>    }
> +
>    if (unsigned_flag)
>      part_func_value-= 0x8000000000000000ULL;
>    DBUG_ASSERT(part_info->no_list_values);
> @@ -2916,11 +2932,29 @@ uint32 get_partition_id_range_for_endpoi
>  
>    if (part_info->part_expr->null_value)
>    {
> -    uint32 ret_part_id= 0;
> -    if (!left_endpoint && include_endpoint)
> -      ret_part_id= 1;
> -    DBUG_RETURN(ret_part_id);
> +    /*
> +      Special handling for MONOTONIC functions that can return NULL for
> +      values that is comparable. I.e.
> +      '2000-00-00' can be compared to '2000-01-01' but TO_DAYS('2000-00-00')
> +      return NULL which cannot be compared used <, >, <=, >= etc.
> +
> +      Otherwise, just return the first partition
> +      (may be included if not left endpoint)
> +    */
> +    enum_monotonicity_info monotonic;
> +    monotonic= part_info->part_expr->get_monotonicity_info();
> +    if (monotonic != MONOTONIC_INCREASING_NOT_NULL &&
> +        monotonic != MONOTONIC_STRICT_INCREASING_NOT_NULL)
> +    {
> +      /* F(col) can not return NULL, return partition with lowest value */
> +      if (!left_endpoint && include_endpoint)
> +        DBUG_RETURN(1);
> +      DBUG_RETURN(0);               
> +
> +    }
>    }
> +
> +
>    if (unsigned_flag)
>      part_func_value-= 0x8000000000000000ULL;
>    if (left_endpoint && !include_endpoint)
> @@ -6733,6 +6767,19 @@ int get_part_iter_for_interval_via_mappi
>    }
>    else
>      assert(0);
> +  
> +  if (part_info->part_type == RANGE_PARTITION ||
> +      part_info->has_null_value)
> +  {
> +    enum_monotonicity_info monotonic;
> +    monotonic= part_info->part_expr->get_monotonicity_info();
> +    if (monotonic == MONOTONIC_INCREASING_NOT_NULL ||
> +        monotonic == MONOTONIC_STRICT_INCREASING_NOT_NULL)
> +    {
> +      /* col is NOT NULL, but F(col) can return NULL, add NULL partition */
> +      part_iter->ret_null_part= part_iter->ret_null_part_orig= TRUE;
> +    }
> +  }
>  
>    /* 
>      Find minimum: Do special handling if the interval has left bound in form
> @@ -6959,7 +7006,13 @@ uint32 get_next_partition_id_range(PARTI
>  {
>    if (part_iter->part_nums.cur >= part_iter->part_nums.end)
>    {
> +    if (part_iter->ret_null_part)
> +    {
> +      part_iter->ret_null_part= FALSE;
> +      return 0;                    /* NULL always in first range partition */
> +    }
>      part_iter->part_nums.cur= part_iter->part_nums.start;
> +    part_iter->ret_null_part= part_iter->ret_null_part_orig;
>      return NOT_A_PARTITION_ID;
>    }
>    else
> @@ -6987,7 +7040,7 @@ uint32 get_next_partition_id_range(PARTI
>  
>  uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter)
>  {
> -  if (part_iter->part_nums.cur == part_iter->part_nums.end)
> +  if (part_iter->part_nums.cur >= part_iter->part_nums.end)
>    {
>      if (part_iter->ret_null_part)
>      {
>
>   
> ------------------------------------------------------------------------
>
>