Complete DATA TYPE behavior for FIXED ROW LOAD DATA INFILE

[Marco Carvalho]

Complete LOAD DATA INFILE behavior for fixed row format and solutions.

* USING:
"If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''),
a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used
between fields (but you can still have a line terminator). Instead, column values are written and
read using the display widths of the columns. For example, if a column is declared as INT(7),
values for the column are written using seven-character fields. On input, values for the column
are obtained by reading seven characters." 722 Manual page;

Exemple:
LOAD DATA INFILE 'load.txt'
INTO TABLE foo
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '\r\n' /* Windows line terminator :P */

* DATA Types:

- INTEGER family (smallint, int, mediumint, bigint)

No problem detected, just put in COLUMN DEFINITION the size and it'll work fine.

- Float point family (decimal, real, float, double)

FLOAT and DOUBLE mess up with data probaly by they round procedures. Use DECIMAL
and REAL when import file has a DOT separating the precisions. Like this:

DECIMAL(5,2) = "123.45"
REAL(7,1) = "123456.7"

Data exceding column definition will couse:
"123.45" DECIMAL(4,2) = '99.99' ;
"123456.7" REAL(6,1) = '99999.9';

If your data don't have a DOT is better to import the data like INTEGER in a temp table
and do a INSERT INTO xxxx SELECT colB/100, colB/10 from yyyy. Just see the exemples above;

- STRING types (CHAR, VARCHAR, BINARY, VARBINARY)

CHAR and VARCHAR don't read the field size in column definition, MySQL read the fields
until they reaches a line terminator cousing the DATA TOO LONG error, as you can see in
exemples above.

BINARY and VARBINARY work fine, like floating point data without DOT import to a temporary
table and do a INSERT INTO xxxx SELECT CAST(VARCHAR, colA) FROM yyyy.


/**********************************************************
* BEGIN number type test for LOAD DATA INFILE behavior *
**********************************************************/

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.17-nt

mysql> create table numbers(
-> tint int(5),
-> tsmallint smallint(2),
-> tdecimal decimal(7,3),
-> tnumeric numeric(4),
-> tfloat float (4,1),
-> treal real(15,2),
-> tdouble double (25,5));
Query OK, 0 rows affected (0.42 sec)

---Numbers.txt file---
55555227777333444444411515151515151222525252525252525252555555
11111223333333444455556666666666666667777777777777777777777777
----------------------

mysql> load data infile 'f:/numbers.txt'
-> into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
ERROR 1264 (22003): Out of range value adjusted for column 'tdecimal' at row 1

---Numbers.txt file---
55555227777.3334444444.11515151515151.222525252525252525252.555555
11111223333.3334444555.56666666666666.667777777777777777777.777777
----------------------

mysql> load data infile 'f:/numbers.txt'
-> into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
ERROR 1264 (22003): Out of range value adjusted for column 'tnumeric' at row 1

mysql> load data infile 'f:/numbers.txt'
-> IGNORE into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';

Query OK, 2 rows affected, 6 warnings (0.03 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 6

mysql> select * from numbers;
+-------+-----------+----------+----------+--------+-----------------+---------------------------+
| tint | tsmallint | tdecimal | tnumeric | tfloat | treal | tdouble |
+-------+-----------+----------+----------+--------+-----------------+---------------------------+
| 55555 | 22 | 7777.333 | 9999 | 4.1 | 515151515151.22 | 2525252525252526100.00000 |
| 11111 | 22 | 3333.333 | 9999 | 5.6 | 666666666666.66 | 7777777777777777700.00000 |
+-------+-----------+----------+----------+--------+-----------------+---------------------------+
2 rows in set (0.05 sec)

mysql> alter table numbers drop column tnumeric;
Query OK, 2 rows affected (0.52 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> delete from numbers;
Query OK, 2 rows affected (0.06 sec)

---Numbers.txt file---
55555227777.333444.11515151515151.222525252525252525252.55555
11111223333.333555.56666666666666.667777777777777777777.77777
----------------------

mysql> load data infile 'f:/numbers.txt'
-> IGNORE into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
Query OK, 2 rows affected, 3 warnings (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 3

+-------+-----------+----------+--------+------------------+----------------------------+
| tint | tsmallint | tdecimal | tfloat | treal | tdouble |
+-------+-----------+----------+--------+------------------+----------------------------+
| 55555 | 22 | 7777.333 | 44.1 | 1515151515151.20 | 22525252525252530000.00000 |
| 11111 | 22 | 3333.334 | 55.5 | 6666666666666.60 | 67777777777777779000.00000 |
+-------+-----------+----------+--------+------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> alter table numbers drop column tfloat;
Query OK, 2 rows affected (0.24 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> delete from numbers;
Query OK, 2 rows affected (0.06 sec)

---Numbers.txt file---
55555227777.3331515151515151.222525252525252525252.55555
11111223333.3336666666666666.667777777777777777777.77777
----------------------

mysql> load data infile 'f:/numbers.txt'
-> into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 2

mysql> select * from numbers;
+-------+-----------+----------+-----------------+---------------------------+
| tint | tsmallint | tdecimal | treal | tdouble |
+-------+-----------+----------+-----------------+---------------------------+
| 55555 | 22 | 7777.333 | 515151515151.22 | 2525252525252526100.00000 |
| 11111 | 22 | 3333.334 | 666666666666.66 | 7777777777777777700.00000 |
+-------+-----------+----------+-----------------+---------------------------+
2 rows in set (0.00 sec)

/*

Without IGNORE clause only the double type bogus the LOAD DATA.

FLOAT - it have a strange behavior importing fixed row format, don't use it;
DOUBLE - mess up with precision, don't use it!

I'll remove the point-precision to observe the behavior.

*/

mysql> delete from numbers;
Query OK, 2 rows affected (0.03 sec)

mysql> alter table numbers drop column tdouble;
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0

---Numbers.txt file---
55555227777333151515151515122
11111223333333666666666666666
----------------------

mysql> load data infile 'f:/numbers.txt'
-> into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
ERROR 1264 (22003): Out of range value adjusted for column 'tdecimal' at row 1

mysql> load data infile 'f:/numbers.txt'
-> IGNORE into table numbers
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 2

+-------+-----------+----------+------------------+
| tint | tsmallint | tdecimal | treal |
+-------+-----------+----------+------------------+
| 55555 | 22 | 9999.999 | 1515151515122.00 |
| 11111 | 22 | 9999.999 | 6666666666666.00 |
+-------+-----------+----------+------------------+
^ ^ ^^ ^ ^ ^ ^
| | || | | | |
55555 22 777733315 1515151515122
11111 22 333333366 6666666666666
----------------------
/* This is the probaly behavior of LOAD DATA

Treats 777733315 and 333333366 as INT and try to cast as DECIMAL(7,3) ROUND UP and
the mess is done. It's not a bad behavior, but lead us to use only INTEGER type and
write a procedure to exchange the data to real tables doing the divisions, like this:
*/

mysql> create table imptmp(
-> tint int(5),
-> tsmalint int(2),
-> tdecimal int(7),
-> treal bigint(15)
-> );
Query OK, 0 rows affected (0.11 sec)

mysql> load data infile 'f:/numbers.txt'
-> into table imptmp
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from imptmp;
+-------+----------+----------+-----------------+
| tint | tsmalint | tdecimal | treal |
+-------+----------+----------+-----------------+
| 55555 | 22 | 7777333 | 151515151515122 |
| 11111 | 22 | 3333333 | 666666666666666 |
+-------+----------+----------+-----------------+
2 rows in set (0.00 sec)

mysql> insert into numbers (tint, tsmallint,tdecimal,treal)
-> select tint, tsmallint, (tdecimal/1000), (treal/100)
-> from imptmp;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from numbers;
+-------+-----------+----------+------------------+
| tint | tsmallint | tdecimal | treal |
+-------+-----------+----------+------------------+
| 55555 | 22 | 7777.333 | 1515151515151.22 |
| 11111 | 22 | 3333.333 | 6666666666666.66 |
+-------+-----------+----------+------------------+
2 rows in set (0.00 sec)

/**********************************************************
* END of number types test for LOAD DATA INFILE behavior *
**********************************************************/

/*******************************************************
* BEGIN string types test for LOAD DATA INFILE begavior *
*******************************************************/

/****************************************************************************************/
/* NOTE: TEXT, BLOB and family can't be imported by LOAD DATA INFILE, so I won't test they. */
/****************************************************************************************/

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 5.0.17-nt

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

mysql> create table text(
-> tchar char(5),
-> tvarchar varchar(6),
-> tbinary binary(4),
-> tvarbinary varbinary(7));
Query OK, 0 rows affected (0.09 sec)

---text.txt file---
5___56____64__47_____7
5aaa56aaaa64aa47aaaaa7
1234512345612341234567
-------------------

mysql> load data infile 'f:/text.txt'
-> into table text
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
ERROR 1406 (22001): Data too long for column 'tchar' at row 1

/*
As I already knew, the char type behavior in fixed row import is read the data until
a field separetor or a line separetor is reached. Let's try this:
*/

mysql> load data infile 'f:/text.txt'
-> IGNORE into table text
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
Query OK, 3 rows affected, 12 warnings (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 12

/* Owww.. 12 warnings... let me guess, DATA TOO LONG and TRUNCATED! */

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

mysql> select * from text;
+-------+----------+---------+------------+
| tchar | tvarchar | tbinary | tvarbinary |
+-------+----------+---------+------------+
| 5___5 | 7_____ | | |
| 5aaa5 | 7aaaaa | | |
| 12345 | 123456 | | |
+-------+----------+---------+------------+
3 rows in set (0.00 sec)

/* Bad! bad! bad! server..., no donut for you!
the LOAD DATA don't read the CHAR size, just like VARCHAR as you can see here:
*/

mysql> alter table text drop column tchar;
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from text;
Query OK, 3 rows affected (0.05 sec)

---text.txt file---
6____64__47_____7
6aaaa64aa47aaaaa7
12345612341234567
-------------------

mysql> load data infile 'f:/text.txt'
-> into table text
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
ERROR 1406 (22001): Data too long for column 'tvarchar' at row 1

/* as you see, same problem. Let's try binary types */

mysql> alter table text drop column tvarchar;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

---text.txt file---
4__47_____7
4aa47aaaaa7
12341234567
-------------------

mysql> load data infile 'f:/text.txt'
-> into table text
-> fields terminated by ''
-> enclosed by ''
-> lines terminated by '\r\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from text;
+---------+------------+
| tbinary | tvarbinary |
+---------+------------+
| 4__4 | 7_____7 |
| 4aa4 | 7aaaaa7 |
| 1234 | 1234567 |
+---------+------------+
3 rows in set (0.00 sec)

/* God bless them! */