Bug #31384 DATE_ADD() and DATE_SUB() return binary data
Submitted: 4 Oct 2007 0:01 Modified: 2 Mar 2011 3:11
Reporter: Jim Winstead Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.1, 5.5, 6.0 OS:Any
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: BINARY, charset

[4 Oct 2007 0:01] Jim Winstead
Description:
The DATE_ADD() and DATE_SUB() functions return a binary string if the first argument is a string. This results in incorrect handling by drivers such as Connector/ODBC and Connector/Net because they treat binaryy strings as, surprisingly enough, binary data.

Bug #30235 is a recent example of a bug reported against Connector/ODBC because of this server bug.

How to repeat:
SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field

Suggested fix:
String data returned by functions like this should not be set to use the BINARY character set. There is no way to distinguish true binary data from function results like this.
[4 Oct 2007 0:22] MySQL Verification Team
Thank you for the bug report.

[miguel@skybr 5.0]$ bin/mysql -uroot test -T
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field;
Field   1:  `field`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY 

+---------------------+
| field               |
+---------------------+
| 2007-08-03 17:32:00 | 
+---------------------+
1 row in set (0.00 sec)

mysql>
[10 Oct 2007 20:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35306

ChangeSet@1.2537, 2007-10-11 01:35:08+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #31384: DATE_ADD() and DATE_SUB() return binary data.
  
  The binary character set has been replaced with the latin1 charset
  to fix this problem.
[11 Oct 2007 10:31] Alexander Barkov
This is not correct to use my_charset_latin1 for the result.
It should be thd->variables.collation_connection.
The code should also be modified to support UCS2. Now it does not.

Also, we need to discuss this bug with PeterG.
These functions were intentionally made to return binary strings.
[22 Oct 2007 18:22] [ name withheld ]
When executing this query and I get a NULL result, would that be a result of this bug?
SELECT DATE_FORMAT('%Y', DATE_SUB(DATE('2007-11-30 09:00:00'),  INTERVAL 1 DAY))
[24 Oct 2007 9:30] Alexander Barkov
The format string should be the second argument:

mysql> SELECT DATE_FORMAT(DATE_SUB(DATE('2007-11-30 09:00:00'),  INTERVAL 1 DAY), '%Y');
+---------------------------------------------------------------------------+
| DATE_FORMAT(DATE_SUB(DATE('2007-11-30 09:00:00'),  INTERVAL 1 DAY), '%Y') |
+---------------------------------------------------------------------------+
| 2007                                                                      |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
[25 Apr 2008 3:47] Sveta Smirnova
Bug #35959 was marked as duplicate of this one.
[2 Sep 2010 12:51] Alexander Barkov
In version 5.5 many functions were fixed to return character data.

DATE_ADD was fixed to return VARCHAR in this context:

mysql> drop table if  exists t1; create table t1 as SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field; show create table t1;

+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                  |
+-------+-----------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `field` varchar(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

However, protocol still sends binary meta-data on the client side:
$mysql-tmp --column-type-info test

mysql> select DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field;
Field   1:  `field`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY 

+---------------------+
| field               |
+---------------------+
| 2007-08-03 17:32:00 |
+---------------------+
1 row in set (0.00 sec)

So this is a bug in the patch for 
WL#2649 Number-to-string conversions.
[19 Jan 2011 14:04] Alexander Barkov
Hmm. MySQL-5.0 created a DATETIME column in the same context,
which looked more correct:

mysql> drop table if exists t1; create table t1 as SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field; show create table t1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+--------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `field` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[19 Jan 2011 14:14] Alexander Barkov
More info: with mysql-5.0.27 I get:

mysql> select version(); drop table if exists t1; create table t1 as SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1, now() as field2; show create table t1; SELECT *, DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field3 from t1;
...
+-----------+
| version() |
+-----------+
| 5.0.27    |
+-----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.26 sec)
Records: 1  Duplicates: 0  Warnings: 0

...

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `field1` datetime default NULL,
  `field2` datetime NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Field   1:  `field1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY

Field   2:  `field2`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY

Field   3:  `field3`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY

+---------------------+---------------------+---------------------+
| field1              | field2              | field3              |
+---------------------+---------------------+---------------------+
| 2007-08-03 17:32:00 | 2011-01-19 16:59:42 | 2007-08-03 17:32:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

I.e. at a "CREATE TABLE ... SELECT" query,
DATE_SUB() creates a DATATIME column, similar to what NOW() does.

However, as "SELECT..." query, DATE_SUB is reported as STRING type.

Looks inconsistent. I'd expect DATE_SUB to be reported as DATETIME type
in a SELECT query.
[19 Jan 2011 14:27] Alexander Barkov
MySQL-5.0.93 demonstrates different behavior.
It creates a VARBINARY() in a
"CREATE TABLE t1 AS SELECT DATE_SUB('...', INTERVAL 1 MINUTE)" query:

mysql> select version(); drop table if exists t1; create table t1 as SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1, now() as field2; show create table t1; SELECT *, DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field3 from t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

...

+--------------+
| version()    |
+--------------+
| 5.0.93-debug |
+--------------+
1 row in set (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

...

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `field1` varbinary(29) default NULL,
  `field2` datetime NOT NULL default '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Field   1:  `field1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       VAR_STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   0
Flags:      BINARY

Field   2:  `field2`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      NOT_NULL BINARY

Field   3:  `field3`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   31
Flags:      BINARY

+---------------------+---------------------+---------------------+
| field1              | field2              | field3              |
+---------------------+---------------------+---------------------+
| 2007-08-03 17:32:00 | 2011-01-19 17:13:32 | 2007-08-03 17:32:00 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
[20 Jan 2011 10:03] Alexander Barkov
Most likely MySQL-5.0 most likely create a VARBINARY() column in this context:

"CREATE TABLE t1 AS SELECT DATE_SUB('...', INTERVAL 1 MINUTE)" query:

after this patch:

  http://bugs.mysql.com/bug.php?id=27216
[21 Jan 2011 13:39] Alexander Barkov
Ok, returning VARCHAR, DATE, DATETIME depending on arguments
is intentional (but somewhat strange) behaviour and
its described in the manual as follows:

>  The return value depends on the arguments:
> * DATETIME if the first argument is a DATETIME (or TIMESTAMP) value,
> or if the first argument is a DATE and the unit value uses HOURS, MINUTES,
> or SECONDS.
> * String otherwise.

So I am fixing the original problem only and do not change return
data type detection.
[21 Jan 2011 13:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129337

3271 Alexander Barkov	2011-01-21
      Bug#31384 DATE_ADD() and DATE_SUB() return binary data
      
      Problem: DATE_ADD() is a hybrid function and can return
      DATE, DATETIME or VARCHAR data type depending on arguments.
      
      In case of VARCHAR data type, DATE_ADD() reported "binary" character set,
      which was wrong.
      
      Fix: make DATE_ADD() return @character_set_connection in VARCHAR context.
     @ mysql-test/include/ctype_numconv.inc
        Adding tests
     @ mysql-test/r/ctype_binary.result
        Adding tests
     @ mysql-test/r/ctype_cp1251.result
        Adding tests
     @ mysql-test/r/ctype_latin1.result
        Adding tests
     @ mysql-test/r/ctype_ucs.result
        Adding tests
     @ mysql-test/r/ctype_utf8.result
        Adding tests
     @ sql/item_strfunc.cc
        - Moving code from Item_str_ascii_func::val_str() to
        Item_str_func::val_str_from_val_str_ascii(), as
        this code needs to be shared by Item_date_add_interval.
        - Adding str2 parameter to be used as a buffer, instead of
         using private ascii_buf member.
     @ sql/item_strfunc.h
        - Moving code from Item_str_ascii_func::val_str() to
        Item_str_func::val_str_from_val_str_ascii()
        - Removing "String *val_str_convert_from_ascii(String *str, String *ascii_buf)"
          prototype as it was neither used nor declared.
     @ sql/item_timefunc.h
        - Overwriting parent's charset_for_protocol() method,
          becase we need to behave differenlty in VARCHAR and DATE/DATETYPE context.
        - Adding ascii_buf for conversion.
        - Adding val_str_ascii() prototype.
        - Adding val_str() which uses newly added
          Item_str_func::val_str_from_val_str_ascii(),
          passing ascii_buf as a conversion buffer.
[10 Feb 2011 6:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130943

3294 Alexander Barkov	2011-02-10
      Bug#31384 DATE_ADD() and DATE_SUB() return binary data
      
      Problem: DATE_ADD() is a hybrid function and can return
      DATE, DATETIME or VARCHAR data type depending on arguments.
      
      In case of VARCHAR data type, DATE_ADD() reported "binary" character set,
      which was wrong.
      
      Fix: make DATE_ADD() return @character_set_connection in VARCHAR context.
           @ mysql-test/include/ctype_numconv.inc
        Adding tests
           @ mysql-test/r/ctype_binary.result
        Adding tests
           @ mysql-test/r/ctype_cp1251.result
        Adding tests
           @ mysql-test/r/ctype_latin1.result
        Adding tests
           @ mysql-test/r/ctype_ucs.result
        Adding tests
           @ mysql-test/r/ctype_utf8.result
        Adding tests
           @ sql/item_strfunc.cc
        - Moving code from Item_str_ascii_func::val_str() to
        Item_str_func::val_str_from_val_str_ascii(), as
        this code needs to be shared by Item_date_add_interval.
        - Adding str2 parameter to be used as a buffer, instead of
         using private ascii_buf member.
           @ sql/item_strfunc.h
        - Moving code from Item_str_ascii_func::val_str() to
        Item_str_func::val_str_from_val_str_ascii()
        - Removing "String *val_str_convert_from_ascii(String *str, String *ascii_buf)"
          prototype as it was neither used nor declared.
           @ sql/item_timefunc.h
        - Overwriting parent's charset_for_protocol() method,
          becase we need to behave differenlty in VARCHAR and DATE/DATETYPE context.
        - Adding ascii_buf for conversion.
        - Adding val_str_ascii() prototype.
        - Adding val_str() which uses newly added
          Item_str_func::val_str_from_val_str_ascii(),
          passing ascii_buf as a conversion buffer.
[10 Feb 2011 8:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130955

3318 Alexander Barkov	2011-02-10
      Bug#31384 DATE_ADD() and DATE_SUB() return binary data
      
      Problem: DATE_ADD() is a hybrid function and can return
      DATE, DATETIME or VARCHAR data type depending on arguments.
      
      In case of VARCHAR data type, DATE_ADD() reported "binary" character set,
      which was wrong.
      
      Fix: make DATE_ADD() return @character_set_connection in VARCHAR context.
       @ mysql-test/include/ctype_numconv.inc
         Adding tests
       @ mysql-test/r/ctype_binary.result
         Adding tests
       @ mysql-test/r/ctype_cp1251.result
         Adding tests
       @ mysql-test/r/ctype_latin1.result
         Adding tests
       @ mysql-test/r/ctype_ucs.result
         Adding tests
       @ mysql-test/r/ctype_utf8.result
         Adding tests
       @ sql/item_strfunc.cc
        - Moving code from Item_str_ascii_func::val_str() to
        Item_str_func::val_str_from_val_str_ascii(), as
        this code needs to be shared by Item_date_add_interval.
        - Adding str2 parameter to be used as a buffer, instead of
         using private ascii_buf member.
       @ sql/item_strfunc.h
        - Moving code from Item_str_ascii_func::val_str() to
        Item_str_func::val_str_from_val_str_ascii()
        - Removing "String *val_str_convert_from_ascii(String *str, String *ascii_buf)"
          prototype as it was neither used nor declared.
       @ sql/item_timefunc.h
        - Overwriting parent's charset_for_protocol() method,
          becase we need to behave differenlty in VARCHAR and DATE/DATETYPE context.
        - Adding ascii_buf for conversion.
        - Adding val_str_ascii() prototype.
        - Adding val_str() which uses newly added
          Item_str_func::val_str_from_val_str_ascii(),
          passing ascii_buf as a conversion buffer.
[10 Feb 2011 8:23] Bugs System
Pushed into mysql-5.5 5.5.10 (revid:alexander.barkov@oracle.com-20110210081808-0egpjmqvr4m1hr7m) (version source revid:alexander.barkov@oracle.com-20110210081808-0egpjmqvr4m1hr7m) (merge vers: 5.5.10) (pib:24)
[10 Feb 2011 8:51] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:alexander.barkov@oracle.com-20110210084705-14u68nklbiuw4kb9) (version source revid:alexander.barkov@oracle.com-20110210084705-14u68nklbiuw4kb9) (merge vers: 5.6.2) (pib:24)
[10 Feb 2011 13:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/131039

3322 Alexander Barkov	2011-02-10
      Bug#31384 DATE_ADD() and DATE_SUB() return binary data
      
      An after-fix to workaround different metadata in "mtr --ps" output.
[10 Feb 2011 13:44] Bugs System
Pushed into mysql-5.5 5.5.10 (revid:alexander.barkov@oracle.com-20110210133818-9sxqewix0fg8c50j) (version source revid:alexander.barkov@oracle.com-20110210133818-9sxqewix0fg8c50j) (merge vers: 5.5.10) (pib:24)
[10 Feb 2011 13:45] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:alexander.barkov@oracle.com-20110210134116-fbpj8z01gm8nonyd) (version source revid:alexander.barkov@oracle.com-20110210134116-fbpj8z01gm8nonyd) (merge vers: 5.6.2) (pib:24)
[2 Mar 2011 3:11] Paul DuBois
Noted in 5.5.10, 5.6.2 changelogs.

DATE_ADD() and DATE_SUB() return a string if the first argument is a
string, but incorrectly returned a binary string. Now they return a 
character string with a collation of connection_collation.

CHANGESET - http://lists.mysql.com/commits/131039