| Bug #10478 | 'LOAD DATA' statement does not support for 'date' format. | ||
|---|---|---|---|
| Submitted: | 9 May 2005 14:51 | Modified: | 28 May 2005 0:14 |
| Reporter: | Disha | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.5-beta-pre-release | OS: | Windows (Windows xp) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[9 May 2005 16:14]
MySQL Verification Team
Verified on Linux BK source.
[28 May 2005 0:14]
Jim Winstead
I was unable to repeat. I got: mysqltest: At line 80: query 'load data infile '../../std_data/loaddata6.dat' into table t1' failed: 1292: Incorrect date value: ''19970505'' for column 'd1' at row 1 That's what I would expect, because no ENCLOSED BY clause exists in the LOAD DATA.
Description: Create a table using 'date' datatype. If inserted the data using INSERT statement the data gets load properly. But if used 'LOAD DATA' statement to insert the data , the proper data does not get inserted. Create a file named 'trialload2.txt' at 'C:\' which contains the data as follows, '19970505' '19970505' '97-05-05' '1997.05.05' '1997 05 05' '0000-00-00' 19970505 19970505 97-05-05 1997.05.05 1997 05 05 0000-00-00 How to repeat: 1. delimiter// 2. create database test// 3. use test 4. set @@sql_mode='TRADITIONAL,NO_ZERO_DATE,NO_ZERO_IN_DATE '// 5. create table t1 (d1 date )// 6. LOAD DATA LOCAL INFILE 'c://trialload2.txt' INTO TABLE t1// 7. Select * from t1// 8. The execution of step (7) displays the result as, +------------+ | d1 | +------------+ | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 0000-00-00 | | 1997-05-05 | | 1997-05-05 | | 0000-00-00 | | 0000-00-00 | +------------+ 12 rows in set (0.01 sec) 9. Expected Result: 'LOAD DATA' statement should load the data sucessfully. 10. Actual Result: The proper data does not get inserted.