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

[Mattias Jonsson]

#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)
     {


Attachment: [text/bzr-bundle]