From 4655118bea2bcb14a06f01046fb06052fd37214c Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Wed, 26 Aug 2009 12:51:23 +0200 Subject: 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. --- sql/item_timefunc.cc | 10 +++++++--- 1 file changed, 7 insertions(+), 3 deletions(-) (limited to 'sql/item_timefunc.cc') diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index d79b0b02998..eebede32f8d 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -991,15 +991,19 @@ longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp) point to day bound ("strictly less" comparison stays intact): col < '2007-09-15 00:00:00' -> TO_DAYS(col) < TO_DAYS('2007-09-15') + col > '2007-09-15 23:59:59' -> TO_DAYS(col) > TO_DAYS('2007-09-15') which is different from the general case ("strictly less" changes to "less or equal"): col < '2007-09-15 12:34:56' -> TO_DAYS(col) <= TO_DAYS('2007-09-15') */ - if (!left_endp && !(ltime.hour || ltime.minute || ltime.second || - ltime.second_part)) - ; /* do nothing */ + if ((!left_endp && !(ltime.hour || ltime.minute || ltime.second || + ltime.second_part)) || + (left_endp && ltime.hour == 23 && ltime.minute == 59 && + ltime.second == 59)) + /* do nothing */ + ; else *incl_endp= TRUE; return res; -- cgit v1.2.1 From 67214ef4334deccdd1f1d89c49c4edecd9075962 Mon Sep 17 00:00:00 2001 From: Mattias Jonsson Date: Wed, 26 Aug 2009 12:59:49 +0200 Subject: 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. --- sql/item_timefunc.cc | 21 ++++++++++++++++++--- 1 file changed, 18 insertions(+), 3 deletions(-) (limited to 'sql/item_timefunc.cc') diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index d79b0b02998..eefe47232ae 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -960,9 +960,9 @@ enum_monotonicity_info Item_func_to_days::get_monotonicity_info() const 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_endpoint(bool left_endp, bool *incl_endp) DBUG_ASSERT(fixed == 1); MYSQL_TIME ltime; longlong res; - if (get_arg0_date(<ime, TIME_NO_ZERO_DATE)) + int dummy; /* unused */ + if (get_arg0_date(<ime, 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(<ime, + (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) { -- cgit v1.2.1