MySQL Bugs: #21811: Odd casting with date + INTERVAL arithmetic

Bug #21811	Odd casting with date + INTERVAL arithmetic
Submitted:	24 Aug 2006 12:56	Modified:	14 Nov 2006 3:25
Reporter:	Felix Geerinckx (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15/5.0BK/5.1BK	OS:	Windows (Windows/Linux)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
SELECT CURRENT_DATE + INTERVAL 1 DAY -> date
SELECT CURRENT_DATE + INTERVAL 1 MONTH -> date
SELECT CURRENT_DATE + INTERVAL 1 YEAR -> date

but

SELECT CURRENT_DATE + INTERVAL 1 WEEK -> datetime

How to repeat:
mysql 5.0.15-nt > SELECT CURRENT_DATE + INTERVAL 1 DAY;
+-------------------------------+
| CURRENT_DATE + INTERVAL 1 DAY |
+-------------------------------+
| 2006-08-25                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql 5.0.15-nt > SELECT CURRENT_DATE + INTERVAL 1 MONTH;
+---------------------------------+
| CURRENT_DATE + INTERVAL 1 MONTH |
+---------------------------------+
| 2006-09-24                      |
+---------------------------------+
1 row in set (0.00 sec)

mysql 5.0.15-nt > SELECT CURRENT_DATE + INTERVAL 1 YEAR;
+--------------------------------+
| CURRENT_DATE + INTERVAL 1 YEAR |
+--------------------------------+
| 2007-08-24                     |
+--------------------------------+
1 row in set (0.01 sec)

mysql 5.0.15-nt > SELECT CURRENT_DATE + INTERVAL 1 WEEK;
+--------------------------------+
| CURRENT_DATE + INTERVAL 1 WEEK |
+--------------------------------+
| 2006-08-31 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

mysql 5.0.15-nt >

Thank you for the bug report. Verified as described.

ChangeSet@1.2539, 2006-09-22 11:22:01+02:00, Kristofer.Pettersson@naruto. +1 -0
  Bug#21811 Odd casting with date + INTERVAL arithmetic
  - The definition of the result type of a type_date function didn't
    include INTERVAL_WEEK
  - This patch adds an explicit test for INTERVAL_WEEK which results
    in the result type for an item_date_add_intervall operation
    being DATE rather than DATETIME when one parameter is
    INTERVAL_WEEK.

  sql/item_timefunc.cc@1.107, 2006-09-22 11:21:27+02:00, Kristofer.Pettersson@na
ruto. +1 -1
    Added explicit test for INTERVAL_WEEK to evaluate as MYSQL_TYPE_DATE

# 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/my41-bug21811

--- 1.106/sql/item_timefunc.cc  2006-09-22 11:22:14 +02:00
+++ 1.107/sql/item_timefunc.cc  2006-09-22 11:22:14 +02:00
@@ -1876,7 +1876,7 @@
     cached_field_type= MYSQL_TYPE_DATETIME;
   else if (arg0_field_type == MYSQL_TYPE_DATE)
   {
-    if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
+    if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH || int_type
 == INTERVAL_WEEK )
       cached_field_type= arg0_field_type;
     else
       cached_field_type= MYSQL_TYPE_DATETIME;

Approved patch for 5.0 with small note about typo in ChangeSet comment.

ChangeSet@1.2279, 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.

Noted in 4.1.23, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

Adding a day, month, or year interval to a DATE value produced a 
DATE, but adding a week interval produced a DATETIME value. Now all
produce a DATE value.