Bug #27670 LOAD DATA INFILE processing does not respect TIMESTAMP default value
Submitted: 5 Apr 2007 16:03 Modified: 10 May 2007 17:58
Reporter: Shawn Green Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.38 enterprise OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: DEFAULT, LOAD DATA INFILE, timestamp

[5 Apr 2007 16:03] Shawn Green
Description:
According to the docs for LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.0/en/load-data.html) the following things are supposed to happen:

* If an input line has too few fields, the table columns for which input fields are missing are set to their default values.

*  An empty field value is interpreted differently than if the field value is missing ... For date and time types, the column is set to the appropriate “zero” value for the type

In this case, there is a DEFAULT defined on a TIMESTAMP column but the source data is not wide enough to populate it. The expected behavior is for case 1 and the DEFAULT of CURRENT_TIMESTAMP should be applied to that column. The actual behavior is the second case and the TIMESTAMP column is being filled with the value '000-00-00 00:00:00"

This is a case apparently not covered by the patch for bug #14770.

How to repeat:
Tested with 5.0.38-enterprise

Table definition:

CREATE TABLE `tableA` (
`A` varchar(2) NOT NULL,
`B` varchar(1) NOT NULL,
`C` varchar(1) NOT NULL,
`D` varchar(1) NOT NULL,
`E` varchar(10) NOT NULL default 'test',
`F_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`A`)
);

Source data (save as test.csv file)

AC,N,N,N
AP,N,N,Y
BD,N,N,Y
CA,N,N,Y
JK,N,N,Y

Bulk import the data with:
LOAD DATA INFILE 'test.csv' into table tableA FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Check the values of the data in the F_timestamp column.

Suggested fix:
Restore the documented functionality to TIMESTAMP columns so that when LOAD DATA INFILE processes source data that is too short to reach them, the DEFAULT value defined on the table is applied.

NOTE: a partial workaround does exist by using the SET syntax. 

LOAD DATA INFILE .... SET F_timestamp=NOW()

but this still defeats the purpose of defining a default value for the column.
[7 May 2007 20:55] 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/26251

ChangeSet@1.2475, 2007-05-08 00:52:12+04:00, evgen@moonbone.local +3 -0
  Bug#27670: LOAD DATA does not set TIMESTAMP default value when there is not
  enough data.
  
  The LOAD DATA sets the current time in the TIMESTAMP field with
  CURRENT_TIMESTAMP default value when the field is detected to be a null.
  But when the LOAD DATA command loads data from a file that doesn't contain
  enough data for all fields then the rest of fields are simply set to null
  without any check. This leads to no value being inserted to such TIMESTAMP
  field.
  
  Now the read_sep_field() and the read_fixed_length() functions set current
  time to the TIMESTAMP field with CURRENT_TIMESTAMP default value in all cases
  when a NULL value is loaded to the field.
[8 May 2007 20:25] 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/26331

ChangeSet@1.2475, 2007-05-09 00:23:16+04:00, evgen@moonbone.local +3 -0
  Bug#27670: LOAD DATA does not set CURRENT_TIMESTAMP default value for a
  TIMESTAMP field when no value has been provided.
  
  The LOAD DATA sets the current time in the TIMESTAMP field with
  CURRENT_TIMESTAMP default value when the field is detected as a null.
  But when the LOAD DATA command loads data from a file that doesn't contain
  enough data for all fields then the rest of fields are simply set to null
  without any check. This leads to no value being inserted to such TIMESTAMP
  field.
  
  Now the read_sep_field() and the read_fixed_length() functions set current
  time to the TIMESTAMP field with CURRENT_TIMESTAMP default value in all cases
  when a NULL value is loaded to the field.
[10 May 2007 6:38] Bugs System
Pushed into 5.1.19-beta
[10 May 2007 6:52] Bugs System
Pushed into 5.0.42
[10 May 2007 17:58] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.

LOAD DATA did not use CURRENT_TIMESTAMP as the default value for a
TIMESTAMP column for which no value was provided.
[13 Nov 2008 18:33] Anand Ganapathy
The fix doesnt seem to work in mysql 5.0.67 or 5.1.26 community release. Has this been checked into these releases ?

Tried a similar test case as listed in the bug and I see only zeros for the timestamp column.

mysql> show create table tableA \G
*************************** 1. row ***************************
       Table: tableA
Create Table: CREATE TABLE `tablea` (
  `A` varchar(2) collate latin1_general_cs NOT NULL,
  `B` varchar(1) collate latin1_general_cs NOT NULL,
  `F_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
1 row in set (0.00 sec)

mysql> load data infile '../../test.csv' into table tableA fields terminated by
',' lines terminated by '\n';
Query OK, 3 rows affected, 6 warnings (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 6

mysql> select * from tableA \G
*************************** 1. row ***************************
          A: AC
          B: N
F_timestamp: 0000-00-00 00:00:00
*************************** 2. row ***************************
          A: AP
          B: N
F_timestamp: 0000-00-00 00:00:00
*************************** 3. row ***************************
          A: BD
          B: N
F_timestamp: 0000-00-00 00:00:00
3 rows in set (0.00 sec)
[6 Aug 2011 23:03] Rene' Cannao'
Reproduced also on 5.1.56