| Bug #41370 | TIMESTAMP field does not accepts NULL from FROM_UNIXTIME() | ||
|---|---|---|---|
| Submitted: | 10 Dec 2008 18:13 | Modified: | 11 Feb 2009 3:47 |
| Reporter: | Leandro Morgado | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.60sp1, 5.1.30 | OS: | Any |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
[10 Dec 2008 18:29]
Valeriy Kravchuk
Verified just as described with 5.1.30 on Windows.
[11 Dec 2008 17:48]
Sveta Smirnova
Workaround: INSERT INTO test_timestamp (test_field) values (ascii(FROM_UNIXTIME('9999999999')));
[11 Dec 2008 19:50]
Leandro Morgado
Sveta: the work around with ASCII only works if FROM_UNIXTIME returns NULL. If it returns a valid value then it will be converted to an ASCII value. The following should be the same.
ysql> SELECT FROM_UNIXTIME('123123');
+-------------------------+
| FROM_UNIXTIME('123123') |
+-------------------------+
| 1970-01-02 11:12:03 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT ASCII(FROM_UNIXTIME('123123'));
+--------------------------------+
| ASCII(FROM_UNIXTIME('123123')) |
+--------------------------------+
| 49 |
+--------------------------------+
1 row in set (0.00 sec)
Here is a workaround that returns a proper timestamp fo
INSERT INTO test_timestamp (test_field) VALUES ( IF(ISNULL(FROM_UNIXTIME('123123')) , NULL, FROM_UNIXTIME('123123')) );
mysql> SELECT IF(ISNULL(FROM_UNIXTIME('123123')) , NULL, FROM_UNIXTIME('123123'));
+---------------------------------------------------------------------+
| IF(ISNULL(FROM_UNIXTIME('123123')) , NULL, FROM_UNIXTIME('123123')) |
+---------------------------------------------------------------------+
| 1970-01-02 11:12:03 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
[11 Dec 2008 20:42]
Sveta Smirnova
Leandro,
right, but you can use ifnull():
INSERT INTO test_timestamp (test_field) values(ifnull(FROM_UNIXTIME('9999999999'),(ascii(FROM_UNIXTIME('9999999999')))));
[22 Jan 2009 7:52]
Tatiana Azundris Nuernberg
completely different code paths for NULL and func.
#0 set_field_to_null (field=0x1171128) at field_conv.cc:118
#1 0x00000000005e695c in Item::save_date_in_field (this=0x116b290, field=0x1171128) at item.cc:346
#2 0x000000000064f2f8 in Item_date_func::save_in_field (this=0x116b290, field=0x1171128, no_conversions=false) at item_timefunc.h:390
#3 0x0000000000711e61 in fill_record (thd=0x7fc8b074f298, fields=@0x7fc8b07515d8, values=@0x116b170, ignore_errors=false) at sql_base.cc:8090
#4 0x0000000000711f6e in fill_record_n_invoke_before_triggers (thd=0x7fc8b074f298, fields=@0x7fc8b07515d8, values=@0x116b170, ignore_errors=false, triggers=0x0, event=TRG_EVENT_INSERT) at sql_base.cc:8135
#5 0x0000000000769520 in mysql_insert (thd=0x7fc8b074f298, table_list=0x116ad38, fields=@0x7fc8b07515d8, values_list=@0x7fc8b0751620, update_fields=@0x7fc8b0751608, update_values=@0x7fc8b07515f0, duplic=DUP_ERROR, ignore=false) at sql_insert.cc:747
#6 0x00000000006cc13d in mysql_execute_command (thd=0x7fc8b074f298) at sql_parse.cc:3088
#7 0x00000000006d1fd4 in mysql_parse (thd=0x7fc8b074f298, inBuf=0x116ac18 "INSERT INTO t1 (f2) VALUES (FROM_UNIXTIME('9999999999'))", length=56, found_semicolon=0x416aff00) at sql_parse.cc:5809
#8 0x00000000006d2e38 in dispatch_command (command=COM_QUERY, thd=0x7fc8b074f298, packet=0x7fc8b07a2eb9 "INSERT INTO t1 (f2) VALUES (FROM_UNIXTIME('9999999999'))", packet_length=56) at sql_parse.cc:1216
for the FROM_UNIXTIME(), we get a fail because our miracle-value 999... exceeds the allowed maximum.
item.cc:336 in 5.1
int Item::save_date_in_field(Field *field)
{
MYSQL_TIME ltime;
if (get_date(<ime, TIME_FUZZY_DATE))
return set_field_to_null(field);
...
An error is then thrown in set_field_to_null(). (If we suppress the error, we just generate a "0000-00-00 ..." entry, which isn't really what we want.)
We can force the intended behaviour like so:
item.cc:336 in 5.1
int Item::save_date_in_field(Field *field)
{
MYSQL_TIME ltime;
if (get_date(<ime, TIME_FUZZY_DATE))
{
field->set_default();
return 0;
}
...
The ASCII(NULL) variant moves like this:
gdb) bt
#0 Item_func_ascii::val_int (this=0x116b1d8) at item_func.cc:2629
#1 0x00000000005e6d52 in Item::save_in_field (this=0x116b1d8, field=0x1171128, no_conversions=false) at item.cc:4889
#2 0x0000000000711e61 in fill_record (thd=0x7fc8b074f298, fields=@0x7fc8b07515d8, values=@0x116b150, ignore_errors=false) at sql_base.cc:8090
#3 0x0000000000711f6e in fill_record_n_invoke_before_triggers (thd=0x7fc8b074f298, fields=@0x7fc8b07515d8, values=@0x116b150, ignore_errors=false, triggers=0x0, event=TRG_EVENT_INSERT) at sql_base.cc:8135
#4 0x0000000000769520 in mysql_insert (thd=0x7fc8b074f298, table_list=0x116ad18, fields=@0x7fc8b07515d8, values_list=@0x7fc8b0751620, update_fields=@0x7fc8b0751608, update_values=@0x7fc8b07515f0, duplic=DUP_ERROR, ignore=false) at sql_insert.cc:747
#5 0x00000000006cc13d in mysql_execute_command (thd=0x7fc8b074f298) at sql_parse.cc:3088
#6 0x00000000006d1fd4 in mysql_parse (thd=0x7fc8b074f298, inBuf=0x116ac18 "INSERT INTO t1 (f2) VALUES (ASCII(NULL))", length=40, found_semicolon=0x416aff00) at sql_parse.cc:5809
So we'll do the same thing here:
int Item::save_date_in_field(Field *field)
{
MYSQL_TIME ltime;
if (get_date(<ime, TIME_FUZZY_DATE))
return set_field_to_null_with_conversions(field, 0);
[23 Jan 2009 6:23]
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/63868 2734 Tatiana A. Nurnberg 2009-01-23 Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME() When storing a NULL to a TIMESTAMP NOT NULL DEFAULT ..., NULL returned from some functions threw a 'cannot be NULL error.' NULL-returns now correctly result in the timestamp-field being assigned its default value.
[30 Jan 2009 16:15]
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/64673 2728 Tatiana A. Nurnberg 2009-01-30 Bug#41370: TIMESTAMP field does not accepts NULL from FROM_UNIXTIME() When storing a NULL to a TIMESTAMP NOT NULL DEFAULT ..., NULL returned from some functions threw a 'cannot be NULL error.' NULL-returns now correctly result in the timestamp-field being assigned its default value.
[2 Feb 2009 16:22]
Tatiana Azundris Nuernberg
queued for 5.0.78, 5.1.32, 6.0.10 in -bugteam
[3 Feb 2009 9:12]
Bugs System
Pushed into 5.0.78 (revid:joro@sun.com-20090203090422-v91rh3gmx2ulhbu9) (version source revid:azundris@mysql.com-20090131010736-3jy0zq3q3lxvpb8c) (merge vers: 5.0.78) (pib:6)
[3 Feb 2009 9:40]
Bugs System
Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:azundris@mysql.com-20090131010841-2tlg3da2ranivq04) (merge vers: 5.1.32) (pib:6)
[4 Feb 2009 11:16]
Bugs System
Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:aelkin@mysql.com-20090202205045-ibm2u4upsznckdmi) (merge vers: 6.0.10-alpha) (pib:6)
[11 Feb 2009 3:47]
Paul DuBois
Noted in 5.0.72, 5.1.32, 6.0.10 changelog. For a TIMESTAMP NOT NULL DEFAULT ... column, storing NULL as the return value from some functions caused a "cannot be NULL" error. NULL returns now correctly cause the column default value to be stored.
[17 Feb 2009 14:55]
Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:42]
Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:18]
Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090203064601-uyuzm4mielj3fz76) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
Description: When inserting a NULL value into a TIMESTAMP field it works as usual (ie, sets the current date) if the NULL is provided directly or via a function. However, if the NULL originates from a FROM_UNIXTIME() function, it will give an error and refuse to insert. I have tested this on Ubuntu 8.04 (4.1.22-standard) and the insert works with FROM_UNIXTIME(). The test case attached has been executed on CentOS 5 (5.0.60sp1-enterprise, 5.1.30-enterpise) and Windows Vista (5.0.60sp1-enterprise). How to repeat: TEST CASE: mysql> CREATE TABLE `test_timestamp` ( `pk` int(11) NOT NULL auto_increment, `test_field` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`pk`) ) ENGINE=MyISAM; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO test_timestamp (test_field) VALUES (NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test_timestamp (test_field) VALUES (ASCII(NULL)); Query OK, 1 row affected (0.00 sec) mysql> SELECT ASCII(NULL); +-------------+ | ASCII(NULL) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test_timestamp; +----+---------------------+ | pk | test_field | +----+---------------------+ | 1 | 2008-12-10 17:28:12 | | 2 | 2008-12-10 17:28:20 | +----+---------------------+ 2 rows in set (0.00 sec) mysql> INSERT INTO test_timestamp (test_field) values (FROM_UNIXTIME('9999999999')); ERROR 1048 (23000): Column 'test_field' cannot be null mysql> SELECT FROM_UNIXTIME('9999999999'); +-----------------------------+ | FROM_UNIXTIME('9999999999') | +-----------------------------+ | NULL | +-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM test_timestamp; +----+---------------------+ | pk | test_field | +----+---------------------+ | 1 | 2008-12-10 17:28:12 | | 2 | 2008-12-10 17:28:20 | +----+---------------------+ Suggested fix: The FROM_UNIXTIME NULL value should be accepted by the insert on the TIMESTAMP field and not differ from regular NULL and ASCII(NULL).