bk commit into 5.0 tree (Kristofer.Pettersson:1.2279) BUG#21811

[kpettersson]

Below is the list of changes that have just been committed into a local
5.0 repository of Kristofer Pettersson. When Kristofer Pettersson does a push these
changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-02 12:12:44+02:00, Kristofer.Pettersson@naruto. +5 -0
  Bug#21811 Odd casting with date + INTERVAL arithmetic
  - Type casting was not consequent, thus when adding a DATE type with
    a WEEK interval the result type was DATETIME and not DATE as is the
    norm.
  - By changing the order of the date internal enumerations the deviant
    type casting is resolved (Item_date_add_interval::fix_length_and_dec()
    which determines result time for this operation assumes that addition
    of any interval with value <= INTERVAL_DAY to date value will result
    in date). There are two independant places to change:
    interval_names[] and interval_type.

  mysql-test/r/func_date_add.result@stripped, 2006-10-02 12:12:40+02:00,
Kristofer.Pettersson@naruto. +14 -0
    Updated result file for type casting test

  mysql-test/r/func_time.result@stripped, 2006-10-02 12:12:40+02:00,
Kristofer.Pettersson@naruto. +2 -2
    Updated result file for type casting test

  mysql-test/t/func_date_add.test@stripped, 2006-10-02 12:12:41+02:00,
Kristofer.Pettersson@naruto. +14 -1
    Added test for type casting when adding intervals to date.

  sql/item_timefunc.cc@stripped, 2006-10-02 12:12:41+02:00, Kristofer.Pettersson@naruto. +6
-2
    Changed order of "week" key word to match the date interval enumeration.

  sql/item_timefunc.h@stripped, 2006-10-02 12:12:41+02:00, Kristofer.Pettersson@naruto. +11 -8
    Changed the order of the enumeration to better follow interval sizes.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	Kristofer.Pettersson
# Host:	naruto.
# Root:	C:/cpp/bug21811/my50-bug21811

--- 1.124/sql/item_timefunc.cc	2006-10-02 12:13:04 +02:00
+++ 1.125/sql/item_timefunc.cc	2006-10-02 12:13:04 +02:00
@@ -2151,11 +2151,15 @@
           (date_sub_interval == other->date_sub_interval));
 }
 
+/*
+   'interval_names' reflects the order of the enumeration interval_type.
+   See item_timefunc.h
+ */
 
 static const char *interval_names[]=
 {
-  "year", "quarter", "month", "day", "hour",
-  "minute", "week", "second", "microsecond",
+  "year", "quarter", "month", "week", "day",  
+  "hour", "minute", "second", "microsecond",
   "year_month", "day_hour", "day_minute", 
   "day_second", "hour_minute", "hour_second",
   "minute_second", "day_microsecond",

--- 1.68/sql/item_timefunc.h	2006-10-02 12:13:04 +02:00
+++ 1.69/sql/item_timefunc.h	2006-10-02 12:13:04 +02:00
@@ -630,18 +630,21 @@
 };
 
 /*
-  The following must be sorted so that simple intervals comes first.
-  (get_interval_value() depends on this)
+  'interval_type' must be sorted so that simple intervals comes first,
+  ie year, quarter, month, week, day, hour, etc. The order based on
+  interval size is also important and the intervals should be kept in a
+  large to smaller order. (get_interval_value() depends on this)
 */
 
 enum interval_type
 {
-  INTERVAL_YEAR, INTERVAL_QUARTER, INTERVAL_MONTH, INTERVAL_DAY, INTERVAL_HOUR,
-  INTERVAL_MINUTE, INTERVAL_WEEK, INTERVAL_SECOND, INTERVAL_MICROSECOND ,
-  INTERVAL_YEAR_MONTH, INTERVAL_DAY_HOUR, INTERVAL_DAY_MINUTE,
-  INTERVAL_DAY_SECOND, INTERVAL_HOUR_MINUTE, INTERVAL_HOUR_SECOND,
-  INTERVAL_MINUTE_SECOND, INTERVAL_DAY_MICROSECOND, INTERVAL_HOUR_MICROSECOND,
-  INTERVAL_MINUTE_MICROSECOND, INTERVAL_SECOND_MICROSECOND
+  INTERVAL_YEAR, INTERVAL_QUARTER, INTERVAL_MONTH, INTERVAL_WEEK, 
+  INTERVAL_DAY, INTERVAL_HOUR, INTERVAL_MINUTE, INTERVAL_SECOND, 
+  INTERVAL_MICROSECOND, INTERVAL_YEAR_MONTH, INTERVAL_DAY_HOUR, 
+  INTERVAL_DAY_MINUTE, INTERVAL_DAY_SECOND, INTERVAL_HOUR_MINUTE, 
+  INTERVAL_HOUR_SECOND, INTERVAL_MINUTE_SECOND, INTERVAL_DAY_MICROSECOND,
+  INTERVAL_HOUR_MICROSECOND, INTERVAL_MINUTE_MICROSECOND,
+  INTERVAL_SECOND_MICROSECOND
 };
 
 class Item_date_add_interval :public Item_date_func

--- 1.9/mysql-test/r/func_date_add.result	2006-10-02 12:13:04 +02:00
+++ 1.10/mysql-test/r/func_date_add.result	2006-10-02 12:13:04 +02:00
@@ -71,3 +71,17 @@
 NULL
 NULL
 drop table t1;
+End of 4.1 tests
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY;
+CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY
[phone]
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH;
+CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH
[phone]
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR;
+CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR
[phone]
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK;
+CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK
[phone]
+End of 5.0 tests

--- 1.67/mysql-test/r/func_time.result	2006-10-02 12:13:04 +02:00
+++ 1.68/mysql-test/r/func_time.result	2006-10-02 12:13:04 +02:00
@@ -612,7 +612,7 @@
 2003-01-03 01:01:01
 select date_add(date,INTERVAL "1" WEEK) from t1;
 date_add(date,INTERVAL "1" WEEK)
-2003-01-09 00:00:00
[phone]
 select date_add(date,INTERVAL "1" QUARTER) from t1;
 date_add(date,INTERVAL "1" QUARTER)
 2003-04-02
@@ -621,7 +621,7 @@
 2003-01-02 00:01:00
 select timestampadd(WEEK, 1, date) from t1;
 timestampadd(WEEK, 1, date)
-2003-01-09 00:00:00
[phone]
 select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
 timestampadd(SQL_TSI_SECOND, 1, date)
 2003-01-02 00:00:01

--- 1.8/mysql-test/t/func_date_add.test	2006-10-02 12:13:05 +02:00
+++ 1.9/mysql-test/t/func_date_add.test	2006-10-02 12:13:05 +02:00
@@ -64,4 +64,17 @@
 select * from t1;
 drop table t1;
 
-# End of 4.1 tests
+--echo End of 4.1 tests
+
+#
+# Bug#21811
+#
+# Make sure we end up with an appropriate
+# date format (DATE) after addition operation
+#
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY;
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH;
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR;
+SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK;
+
+--echo End of 5.0 tests