error when loading data from file using default or null value

Bug #496619 reported by Lee Bieber
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Confirmed
Wishlist
Stewart Smith
Cherry
Won't Fix
High
Stewart Smith

Bug Description

drizzle> create table t1 (i int, t timestamp default Now(), i2 int);
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into t1 VALUES (10,default,199);
Query OK, 1 row affected (0 sec)

drizzle> select * from t1;
+------+---------------------+------+
| i | t | i2 |
+------+---------------------+------+
| 10 | 2009-12-14 17:26:43 | 199 |
+------+---------------------+------+
1 row in set (0 sec)

drizzle> insert into t1 (i,i2) VALUES (10,199);
Query OK, 1 row affected (0.01 sec)

drizzle> select * from t1;
+------+---------------------+------+
| i | t | i2 |
+------+---------------------+------+
| 10 | 2009-12-14 17:26:43 | 199 |
| 10 | 2009-12-14 17:27:33 | 199 |
+------+---------------------+------+
2 rows in set (0 sec)

drizzle> LOAD DATA INFILE '/tmp/data' INTO TABLE t1 FIELDS TERMINATED BY '\t';
ERROR 1685 (HY000): Received an invalid value '' for a UNIX timestamp.

where /tmp/data is
10\t\t1099

drizzle> LOAD DATA INFILE '/tmp/data' INTO TABLE t1 FIELDS TERMINATED BY '\t';
ERROR 1685 (HY000): Received an invalid value 'default' for a UNIX timestamp.

where /tmp/data is
10\tdefault\t1099

drizzle> LOAD DATA INFILE '/tmp/data' INTO TABLE t1 FIELDS TERMINATED BY '\t';
Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

where /tmp/data is
10\t2009-12-15\t1099

drizzle> select * from t1;
+------+---------------------+------+
| i | t | i2 |
+------+---------------------+------+
| 10 | 2009-12-14 17:26:43 | 199 |
| 10 | 2009-12-14 17:27:33 | 199 |
| 2 | 2009-12-15 00:00:00 | 1099 |
+------+---------------------+------+
3 rows in set (0 sec)

Changed in drizzle:
status: New → Confirmed
milestone: none → bell
importance: Undecided → High
assignee: nobody → Stewart Smith (stewart-flamingspork)
Changed in drizzle:
milestone: bell → cherry
Changed in drizzle:
milestone: 2010-05-24 → none
Revision history for this message
Stewart Smith (stewart) wrote :

So.... I'm thinking this is Not A Bug....

to get NULL, it must be enclosed by, and if default value is wanted, must be enclosed by with NOT NULL column.

otherwise it is true that empty string is invalid for timestamp. we could change this, but the errors are generated in the lower layer, not really giving us an option to overturn them sanely or generically without per-type special cases.

At best I think this gets moved to Wishlist... or "not a bug"

Changed in drizzle:
importance: High → Wishlist
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.