MySQL Bugs: #27670: LOAD DATA INFILE processing does not respect TIMESTAMP default value

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

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.

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.

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.

Pushed into 5.1.19-beta

Pushed into 5.0.42

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.

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)

Reproduced also on 5.1.56