Affects Version/s: 1.14.0
Fix Version/s: 1.17.0
- Tested with latest version Apache Drill 1.14.0, and building the latest version from master (Github repo), commit ad61c6bc1dd24994e50fe7dfed043d5e57dba8f9 at Nov 5, 2018.
- Linux x64, Ubuntu 16.04
- OpenJDK Runtime Environment (build 1.8.0_171-8u171-b11-0ubuntu0.17.10.1-b11)
- Apache Maven 3.5.0
When you export to a CSV using CTAS the result of a query, most of the time the generated file is OK, but if you have in the results text columns with "," characters, the resulting CSV file is broken, because does not enclose the cells with commas inside with the " character.
Steps to reproduce the bug:
Lets say you have the following table in some source of data, maybe a CSV file too:
Note that the second row of data, in the column "product_name", it has a value with a comma inside (Samsung S9, Black), so all the cell value is enclosed with " characters, while the rest of the column cells aren't, despite they could be enclosed too.
So if you query this file, Drill will interpret correctly the file and does not interpret that comma inside the cell as a separator like the rest of the commas in the file:
But now, if you want to query the file and export the result as CSV using the CTAS feature, using the following steps:
The output file is this:
The text Samsung S9, Black in the cell is not quoted, so any CSV interpreter like an office tool, a Java/Python/... library will interpret it as two cell instead of one. Even Apache Drill will interpret it wrong:
Note that the ending part _ Black_ was interpreted as a following cell, and the real following cell is not showed, but it's not an error in the Drill interpreter, it's an error of how Drill exported the result that now in the last query was used as input.
Here is how the file is interpreted by LibreOffice Calc: