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:Microsoft Windows (Windows/Linux)
Assigned to: Kristofer Pettersson

[24 Aug 2006 12:56] Felix Geerinckx
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 >
[24 Aug 2006 13:16] Miguel Solorzano
Thank you for the bug report. Verified as described.
[22 Sep 2006 9:43] Kristofer Pettersson
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;
[2 Oct 2006 10:32] Dmitry Lenev
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.
[14 Nov 2006 3:25] Paul Dubois
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.