MySQL Bugs: #41446: mysqlimport / Load Data File - partial columns import incorrect ?

Bug #41446	mysqlimport / Load Data File - partial columns import incorrect ?
Submitted:	13 Dec 2008 2:21	Modified:	15 Dec 2008 7:32
Reporter:	Lars Wittmaack	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.45	OS:	Linux (Red Hat 5.0)
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA INFILE, mysqlimport
Triage:	Needs Triage: D5 (Feature request)

Description:
The reference manual says 
"12.2.6. LOAD DATA INFILE  Syntax
[...]
If you want to load only some of a table's columns, specify a column list:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);"

Expected behaviour:
a) using a column list will replace values in the columns provided in the column list only
b) default values are only set for columns if no column list provided 

Observed behaviour:
a) Importing data from a file that has less columns than the table results in getting default values set for the rest of the table columns (as long as a default value is set)

How to repeat:
datafile with '$' as seperator
-----------------
row1Int1$row1NewText2$row1NewText3
-----------------

table "tbl1" in database "test"
--------------------------------------------------
|    col1   |    col2  |    col3   |     col4    |
--------------------------------------------------
| row1Int1  | row1text2| row1text3 | row1number1 |
| row2Int1  | row2text2| row2text3 | row2number1 |
--------------------------------------------------
col1 = primary key 
col2 = string
col3 = string
col4 = int(6), zero fill, default value 000001

Import query:
a) mysqlimport --columns=col1,col2,col3 -fields-terminated-by='$' --replace Test datafile
b) mysql -e "LOAD DATA LOCAL INFILE 'datafile' REPLACE INTO TABLE test.tbl1 FIELDS TERMINATED BY '$' (col1,col2,col3);"

Expected result
-------------------------------------------------------
|    col1   |     col2    |     col3    |     col4    |
-------------------------------------------------------
| row1Int1  | row1NewText2| row1NewText3| row1number1 |
| row2Int1  | row2text2   | row2text3   | row2number1 |
-------------------------------------------------------

Result:
-------------------------------------------------------
|    col1   |     col2    |     col3    |     col4    |
-------------------------------------------------------
| row1Int1  | row1NewText2| row1NewText3| 000001      |
| row2Int1  | row2text2   | row2text3   | row2number1 |
-------------------------------------------------------

Suggested fix:
Import values from the datafile's fields into the provided columns as per column list only. Set default values if no column list is given.

Alternatively provide an additional option "[replace | ignore | update]" that will preserve values for columns that are not mentioned in the column list option.

Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.0/en/load-data.html: "If you specify REPLACE, input rows replace existing rows." So current behavior is correct.

But bug can be verified as feature request "add UPDATE keyword or any other possiblity to replace columns desired"

Hello Sveta, 

This appear to be a bug with Mysql version 5 and above that the "Load Data Infile" doesn't work correctly if you have a column with default value
and I have managed to reproduce this error on Mysql Version 5.0.51A, 5.0.67 and 5.1.22.

It seems that other user has reported the same thing in but it hasn't been acknowledged
http://bugs.mysql.com/bug.php?id=27670

TEST CSV file content
=================================
2|test product 1|87|46.35~
3|test product 2|27|37.10~
4|test product 3|34|22.30~
==================================

TEST table schema
===================================
CREATE TABLE `TestTbl` (
  `id` mediumint(4) NOT NULL auto_increment,
  `product_id` mediumint(4) NOT NULL default '0',
  `title` varchar(50) default NULL,
  `country_id` mediumint(8) default NULL,
  `product_minimum` float(5,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
=====================================

The load data sql statement works fine on Mysql version 4.1.22 as tested below:

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 4.1.22-standard |
+-----------------+
1 row in set (0.00 sec)

mysql> load data infile "/tmp/test.txt" into table TestTbl fields terminated by "|" LINES TERMINATED BY "~" (product_id, title,country_id, product_minimum);
Query OK, 4 rows affected, 4 warnings (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'product_id' at row 4 |
| Warning | 1261 | Row 4 doesn't contain data for all columns      |
| Warning | 1261 | Row 4 doesn't contain data for all columns      |
| Warning | 1261 | Row 4 doesn't contain data for all columns      |
+---------+------+-------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from TestTbl;
+----+------------+----------------+------------+-----------------+
| id | product_id | title          | country_id | product_minimum |
+----+------------+----------------+------------+-----------------+
|  1 |          2 | test product 1 |         87 |           46.35 |
|  2 |          3 | test product 1 |         27 |           37.10 |
|  3 |          4 | test product 1 |         34 |           22.30 |

Now retest the same "load data" statement in mysql 5.0.51A and you will notice that the data values in "product_id" get messed up.

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.51a-log | 
+-------------+

mysql> load data infile "/tmp/test.txt" into table TestTbl fields terminated by "|" LINES TERMINATED BY "~" (product_id, title,country_id, product_minimum);
Query OK, 4 rows affected, 6 warnings (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 3

mysql> show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1261 | Row 4 doesn't contain data for all columns                     | 
| Warning | 1261 | Row 4 doesn't contain data for all columns                     | 
| Warning | 1261 | Row 4 doesn't contain data for all columns                     | 
+---------+------+----------------------------------------------------------------

mysql> select * from TestTbl;
+----+------------+----------------+------------+-----------------+
| id | product_id | title          | country_id | product_minimum |
+----+------------+----------------+------------+-----------------+
|  1 |          2 | test product 1 |         87 |           46.35 | 
|  2 |          0 | test product 2 |         27 |           37.10 | 
|  3 |          0 | test product 3 |         34 |           22.30 | 
|  4 |          0 | NULL           |       NULL |            NULL | 
+----+------------+----------------+------------+-----------------+
4 rows in set (0.00 sec)

Sai