| 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: | |
| 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
[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
