[BUG] datafusion-cli may fail to read csv files generated by tpchgen-cli

[niebayes]

Describe the bug

Using datafusion-cli to read csv files generated by tpchgen-cli sometimes fails.
This issue was originally posted at #66 (comment)

To Reproduce
Steps to reproduce the behavior:

1. Git clone the latest of the main branch (rev: cb325ad)
2. Build with cargo: cargo build --release
3. Generate csv files in a dedicated directory: ./target/release/tpchgen-cli -f csv -o gen
4. Install the latest release of datafusion-cli: cargo install datafusion-cli
5. Ensure datafusion-cli is of version 46.0.1

• Start datafusion-cli by running datafusion-cli in your terminal.
• Run sql select version() which should print a message containing 46.0.1.

6. Use datafusion-cli to read the part.csv file by running sql select * from './gen/part.csv'. Note be sure to not add extra limit clause.
7. The datafusion-cli should report an error like: Arrow error: Parser error: Error while parsing value p_partkey for column 0 at line 24597

Expected behavior
All csv files generated by tpchgen-cli should be readable by datafusion-cli.

Screenshots

Environment (please complete the following information):

• OS: Darwin Mac 24.0.0 Darwin Kernel Version 24.0.0: Mon Aug 12 20:52:18 PDT 2024; root:xnu-11215.1.10~2/RELEASE_ARM64_T8122 arm64
• Compiler Version: 1.85.1

[niebayes]

@alamb I filed a new ticket to track the issue.

[niebayes]

I also tried to use datafusion-cli to read a csv file generated by myself, and it turns out there's no problem using datafusion-cli to read other csv files.

Here is the codes to generate a csv file with the same schema as the Part table.

#[tokio::test]
    async fn datafusion_read_large_csv() {
        use arrow::csv::WriterBuilder;
        use arrow::util::data_gen::create_random_batch;
        use arrow_array::RecordBatch;
        use arrow_schema::{DataType, Field, Schema};
        use std::fs::OpenOptions;
        use std::sync::Arc;

        let schema = Arc::new(Schema::new(vec![
            Field::new("p_partkey", DataType::Int64, false),
            Field::new("p_name", DataType::Utf8, false),
            Field::new("p_mfgr", DataType::Utf8, false),
            Field::new("p_brand", DataType::Utf8, false),
            Field::new("p_type", DataType::Utf8, false),
            Field::new("p_size", DataType::Int32, false),
            Field::new("p_container", DataType::Utf8, false),
            // Replace Decimal with Float64 since `create_random_batch` does not support Decimal data type yet.
            //
            // Field::new("p_retailprice", DataType::Decimal128(15, 2), false),
            Field::new("p_retailprice", DataType::Float64, false),
            Field::new("p_comment", DataType::Utf8, false),
        ]));

        let file = OpenOptions::new()
            .create(true)
            .truncate(true)
            .write(true)
            .read(true)
            .open("part.csv")
            .unwrap();
        let mut writer = WriterBuilder::new().build(file);

        // Write 200 * 1000 = 200,000 rows into this file.
        for _ in 0..200 {
            let batch = create_random_batch(schema.clone(), 1000, 0.0, 0.0).unwrap();
            writer.write(&batch).unwrap();
        }
    }

[alamb]

I agree I can reproduce it.

Here is the file: part.zip

At first I thought the error is related to the fact that datafusion tries to read the CSV file using multiple cores which might not work with embedded quotes

However, even when I tuned that off I get a parse error:

> set datafusion.optimizer.repartition_file_scans = false;
0 row(s) fetched.
Elapsed 0.001 seconds.

> explain select * from 'part.csv';
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                                                                                                   |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | TableScan: part.csv projection=[p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment]                                                                                             |
| physical_plan | DataSourceExec: file_groups={1 group: [[Users/andrewlamb/Downloads/part.csv]]}, projection=[p_partkey, p_name, p_mfgr, p_brand, p_type, p_size, p_container, p_retailprice, p_comment], file_type=csv, has_header=true |
|               |                                                                                                                                                                                                                        |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.017 seconds.

> select * from 'part.csv';
Arrow error: Parser error: Error while parsing value p_partkey for column 0 at line 100001
>

[niebayes]

@alamb
I have tested some scenarios:

1. ok: arrow::csv::Reader read csv files generated by tpchgen-cli
2. ok: arrow::csv::Reader read other csv files.
3. ok: datafusion-cli read other csv files.
4. err: datafusion-cli read csv files generated by tpchgen-cli

Some might-not-be-correct discovery:

• The value that makes parsing fail must be the first value of a certain row
• The data type of the value must be integer.

[alamb]

Thanks @niebayes -- I agree this looks like a bug with datafusion somehow -- perhaps you can file a ticket there?

[niebayes]

Filed a ticket: apache/datafusion#15456

[alamb]

I think the bug is here:

tpchgen-rs/tpchgen-cli/src/csv.rs

Line 27 in d912ec4

writeln!(&mut buffer, "{}", <$FORMATTER>::header())

CSV headers are written to frequently -- they should be only written on the first output chunk

[alamb]

Kudos to @chenkovsky for finding this:

apache/datafusion#15456 (comment)

[alamb]

Here is a PR to fix the problem:

• Do not generate CSV header multiple times #78