MySQL Bugs: #14770: LOAD DATA INFILE doesn't respect default values for columns

Bug #14770	LOAD DATA INFILE doesn't respect default values for columns
Submitted:	8 Nov 2005 20:28	Modified:	18 Aug 2006 12:50
Reporter:	Anderson Vitous	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-max, 4.1.10a	OS:	Linux (Linux (RHEL4))
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
When a table's column(s) are defined to be NOT NULL and have a nonzero default value (INT columns; may affect strings or other types but untested), LOAD DATA INFILE fails to populate the column with the defined default value when a null is encountered in import data for the column; instead, the column will receive the "implicit" default value of 0.  Warnings are generated for each such row stating the default value will be used, but it is not.

How to repeat:
Test scripts/data will be attached following bug submission.

1.  Create a table having at least one column defined as INT NOT NULL DEFAULT k (where k is nonzero).  (test case:  defvaluetest.sql)

2.  Populate table with a row having a null for the column of interest.  (test case:  defvaluetestload.sql using data in defvaluetestdata.dat)

3.  Observe the column received a value of 0, not k as expected.

Suggested fix:
Functionally, all possible paths to populate a non-nullable column with a null which will use a default value should always respect the default value defined in the schema.

Test scripts/data demonstrating default value issue

Attachment: defvaluetest.tar.gz (application/x-gzip, text), 351 bytes.

This behavior was encountered both with the mysql command line client (same version/platform as server) and with Perl DBD::mysql 2.9008.

Just verified this behavior is present in server 4.1.10a as well.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/b/defvaluetest.sql
Query OK, 0 rows affected (0.04 sec)

mysql> show create table defvaluetest\G
*************************** 1. row ***************************
       Table: defvaluetest
Create Table: CREATE TABLE `defvaluetest` (
  `f1` int(11) NOT NULL default '1',
  `f2` int(11) NOT NULL default '5'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> load data infile '/home/miguel/b/defvaluetestdata.dat' into table defvaluetest;
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 2

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column 'f2' at row 2 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column 'f1' at row 3 |
+---------+------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from defvaluetest;
+----+----+
| f1 | f2 |
+----+----+
|  1 |  1 |
|  2 |  0 |
|  0 |  3 |
+----+----+
3 rows in set (0.01 sec)

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source /home/miguel/b/defvaluetest.sql
Query OK, 0 rows affected (0.06 sec)

mysql> show create table defvaluetest\G
*************************** 1. row ***************************
       Table: defvaluetest
Create Table: CREATE TABLE `defvaluetest` (
  `f1` int(11) NOT NULL default '1',
  `f2` int(11) NOT NULL default '5'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> load data infile '/home/miguel/b/defvaluetestdata.dat' into table defvaluetest;
Query OK, 3 rows affected, 2 warnings (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 2

mysql> show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'f2' at row 2 |
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'f1' at row 3 |
+---------+------+----------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from defvaluetest;
+----+----+
| f1 | f2 |
+----+----+
|  1 |  1 |
|  2 |  0 |
|  0 |  3 |
+----+----+
3 rows in set (0.02 sec)

mysql>

This is expected behavior. There is implicit default value for this case. It is
0 for numeric data types and '' for strings. Warning should be changed and it
deserves a note in "LOAD DATA INFILE" section of mysql manual.

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/9888

ChangeSet@1.2240, 2006-08-01 18:32:56+05:00, svoj@may.pils.ru +16 -0
  BUG#14770 - LOAD DATA INFILE doesn't respect default values for
              columns
  Fixed confusing warning.

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/9938

ChangeSet@1.2240, 2006-08-02 17:15:50+05:00, svoj@may.pils.ru +16 -0
  BUG#14770 - LOAD DATA INFILE doesn't respect default values for
              columns
  Fixed confusing warning.
  
  Quoting INSERT section of the manual:
  ----
  Inserting NULL into a column that has been declared NOT NULL. For
  multiple-row INSERT statements or INSERT INTO ... SELECT statements, the
  column is set to the implicit default value for the column data type. This
  is 0 for numeric types, the empty string ('') for string types, and the
  "zero" value for date and time types. INSERT INTO ... SELECT statements are
  handled the same way as multiple-row inserts because the server does not
  examine the result set from the SELECT to see whether it returns a single
  row. (For a single-row INSERT, no warning occurs when NULL is inserted into
  a NOT NULL column. Instead, the statement fails with an error.)
  ----
  This is also true for LOAD DATA INFILE. For INSERT user can specify
  DEFAULT keyword as a value to set column default. There is no similiar
  feature available for LOAD DATA INFILE.

merged from 5.0 engines to main version 5.0.25

I beg to differ whether this behavior *should* be expected.

LOAD DATA INFILE inserts rows into the database, and all actions populating a new row should respect any default column values specified when the table was defined, whether said action occurs during an INSERT statement (multi-row or not), including INSERT INTO ... SELECT, or by an implicit row insertion carried out by LOAD DATA INFILE.

In other words, a defined default value for a column SHOULD ALWAYS BE RESPECTED.  Other enterprise class databases, such as Sybase, behave this way (e.g. bcp will respect default column values when bulk loading and no value is found for a column).  It's only reasonable to expect that MySQL would behave the same way.

I've placed this bug in Analysing status and asked one of our software architects to review this, as this behaviour is highly inconsistent, and I agree with the submitter that we should consider changing it to match the behaviour of INSERT in this regard.

I've discussed this with PeterG, and our conclusion is that fixing the warning message as described was the right thing to do here, as LOAD DATA is (otherwise) behaving as described in the Manual.

If the submitter would like to see this behaviour changed, he should please submit a Feature Request bug.

I've documented the fix in the 5.0.25 changelog.

BUT - since this is apparently tagged for 5.1 as well as 5.0, I need to know which 5.1.x to which the fix applies before this fix can be fully documented and the bug closed. 

Thanks!

It was pushed in 5.1.12.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Also noted bugfix in 5.1.12 changelog. Bug closed.