MySQL Bugs: #31384: DATE_ADD() and DATE

Bug #31384	DATE_ADD() and DATE_SUB() return binary data
Submitted:	4 Oct 2007 2:01	Modified:	29 Sep 2008 9:14
Reporter:	Jim Winstead
Status:	Verified
Category:	Server: Charsets	Severity:	S2 (Serious)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Gleb Shchepa	Target Version:
Tags:	BINARY, charset
Triage:	Triaged: D5 (Feature request)

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.

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>

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.

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.

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))

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)

Bug #35959 was marked as duplicate of this one.