MySQL Bugs: #55907: CSV Engine seems to have changed format of numeric handling

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

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"

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

Thank you for the report.

Verified as described. Can be documentation bug though.

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