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 18:03	Modified:	10 May 2007 19:58
Reporter:	Shawn Green
Status:	Closed
Category:	Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.38 enterprise	OS:	Any
Assigned to:	Evgeny Potemkin	Target Version:
Tags:	LOAD DATA INFILE, timestamp, DEFAULT

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.