Bug #41370 TIMESTAMP field does not accepts NULL from FROM_UNIXTIME()

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

Verified just as described with 5.1.30 on Windows.

Workaround:  INSERT INTO test_timestamp (test_field) values (ascii(FROM_UNIXTIME('9999999999')));

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)

Leandro,

right, but you can use ifnull():

INSERT INTO test_timestamp (test_field) values(ifnull(FROM_UNIXTIME('9999999999'),(ascii(FROM_UNIXTIME('9999999999')))));

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(&ltime, 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(&ltime, 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(&ltime, TIME_FUZZY_DATE))
    return set_field_to_null_with_conversions(field, 0);

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.

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.

queued for 5.0.78, 5.1.32, 6.0.10 in -bugteam

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)

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)

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)

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.

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)

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)

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)