Bug #63536 Possible bug in function current_date( )

• Submitted: 2 Dec 2011 8:16
• Modified: 2 Dec 2011 20:05
• Reporter: Luis Basura
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[2 Dec 2011 8:16] Luis Basura

Description:
I don't know if this is a bug. I will show you what has happened to me today:

Today is December 2nd. When run:

SELECT current_date( ) -7

i got the following output: 

current_date() - 7
20111195

...the day field is being substracted from 100, giving a 95 value... I don't know if this is a bug but OMHO it shouldn't show a day number bigger than 31, should it?.

Rgds

How to repeat:
current_date() - 7

[2 Dec 2011 8:28] MySQL Verification Team

how should mysql guess that you want to subtract 7 days and not 7 years?
also, it might be casting to a number....

see:

mysql> select current_date() - 7;
+--------------------+
| current_date() - 7 |
+--------------------+
|           20111195 |
+--------------------+
1 row in set (0.00 sec)

mysql> select current_date() - interval 7 day;
+---------------------------------+
| current_date() - interval 7 day |
+---------------------------------+
| 2011-11-25                      |
+---------------------------------+
1 row in set (0.00 sec)

[2 Dec 2011 9:11] Peter Laursen

Documentation says

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_curdate

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. 
mysql> SELECT CURDATE();
        -> '2008-06-13'
mysql> SELECT CURDATE() + 0;
        -> 20080613

What you do is the same as the last example.

Use 'interval' operator instead of '-' operator.  Examples here:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Peter
(not a MySQL person).

[2 Dec 2011 11:12] lou shuai

the result type of current_date is string, the result of a string plus a int is double according to the conversion of mysql internals, so the result is normal

[2 Dec 2011 11:16] MySQL Verification Team

result of current_date() is DATE.

mysql> select current_date();
Field   1:  `current_date()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      NOT_NULL BINARY

[2 Dec 2011 11:47] Peter Laursen

Alternatively use "SELECT ADDDATE(CURDATE(),-2);"

[2 Dec 2011 12:09] Peter Laursen

I think this explains:

-- first we produce a DATE
SELECT CAST('2011-12-02' AS DATE); --returns "2011-12-02" - a DATE

-- subtracting a integer from a date returns an INTEGER
SELECT (CAST('2011-12-02' AS DATE) - 2); -- returns "20111200" - an INTEGER

-- adddate() example
SELECT ADDDATE('2011-12-02', -2); -- returns "2011-11-30" - a date

-- conclusion: use DATE/TIME functions&operators, not artimetical operators.

[5 Dec 2011 8:33] lou shuai

@Shane Bester
the field type of current_date is DATE,but the result type of the item is RESULT_STRING,according to the rule in mysql as shown below:
Item_result r0= args[0]->result_type();
  Item_result r1= args[1]->result_type();

  if (r0 == REAL_RESULT || r1 == REAL_RESULT ||
      r0 == STRING_RESULT || r1 ==STRING_RESULT)
  {
    count_real_length();
    max_length= float_length(decimals);
    hybrid_type= REAL_RESULT;
  }

if r0/r1 is REAL OR STRING RESULT, the result of operation of +,-,*,% is real, that is double