Bug #55907 CSV Engine seems to have changed format of numeric handling
Submitted: 11 Aug 2010 8:39 Modified: 11 Aug 2010 10:43
Reporter: Tobias Asplund Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: CSV Severity:S3 (Non-critical)
Version:5.1, 5.6.99 OS:Any
Assigned to: CPU Architecture:Any

[11 Aug 2010 8:39] Tobias Asplund
Description:
If you check the manual at
http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html
as well as the article on
http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html
it displays numeric columns stored enclosed by double quotes.
This behavior seems to have changed.

This will make it a bit harder to use CSV as a staging-point for import/export since not all applications will accept CSV files without enclosures.

How to repeat:
mysql> CREATE TABLE t10 ( a INT not null, b CHAR(10) not null ) engine = csv;
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO t10 VALUES (10, 'asdf');
Query OK, 1 row affected (0.31 sec)

mysql> \! cat /var/mysql/5.1/test/t10.CSV
10,"asdf"
[11 Aug 2010 9:08] Emil Hansen
The behavior seems to be inconsistent.

Having a table city_csv:
10:58 world describe city_csv;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| city  | char(50) | NO   |     | NULL    |       |
| cc    | char(50) | NO   |     | NULL    |       |
| dist  | char(50) | NO   |     | NULL    |       |
| pop   | int(11)  | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

And changing the csv file contents to the following:
"1", "Kabul", "AFG", "Kabol", 178000
"2", "Qandahar", "AFG", "Qandahar", "237500"

Will yield the following error:
11:00 world select * from city_csv;
ERROR 1194 (HY000): Table 'city_csv' is marked as crashed and should be repaired
Warning (Code 1366): Incorrect integer value: ' "237500"' for column 'pop' at row 1
Error (Code 1194): Table 'city_csv' is marked as crashed and should be repaired
[11 Aug 2010 10:43] Sveta Smirnova
Thank you for the report.

Verified as described. Can be documentation bug though.
[11 Aug 2010 12:11] Emil Hansen
I doubt it is a documentation issue, since the "Id" column from the earlier example, accepts INT values in quotes (and without quotes as well).

Eg.
Produces valid output:
"1", "Kabul", "AFG", "Kabol", 178000
2, "Qandahar", "AFG", "Qandahar", 237500
"3", "Herat", "AFG", "Herat", 186800

Produces error:
"1", "Kabul", "AFG", "Kabol", 178000
2, "Qandahar", "AFG", "Qandahar", "237500"
"3", "Herat", "AFG", "Herat", 18680