Repository: duckdb/duckdb (language: python)

Issue:
CSV reader will delete valid records when erroneous newlines are present
### What happens?

When using the CSV reader in version `1.1.1`, valid rows from the CSV will be deleted when there are erroneous newlines present in the CSV surrounding the valid records. This occurs when you use the `ignore_errors` parameter. If you set `ignore_errors` to `False` or use `store_rejects`, then no data loss occurs.

I believe this is correlated to CSVs with a large amount of records. During my testing, as I've reduced the amount of records in my test CSV the amount of dataloss decreased as the amount of records decreased. I'll provide my sample data below.

This issue first began happening in version `1.1.0`, and this is not an issue in version `1.0.0`.

### To Reproduce

First, download the sample data here: [test_duckdb_data.csv.zip](https://github.com/user-attachments/files/17192226/test_duckdb_data.csv.zip)

Using this CSV, we'll create two views in DuckDB:

```
CREATE VIEW test_1 AS
SELECT *
FROM read_csv_auto('test_duckdb_data.csv',
    header=True,
    ignore_errors=True,
    delim=',',
    all_varchar=True
    );
```
`test_1` is ignoring errors.

```
CREATE VIEW test_2 AS
SELECT *
FROM read_csv_auto('test_duckdb_data.csv',
    header=True,
    ignore_errors=True,
    delim=',',
    all_varchar=True,
    store_rejects=True
    );
```

`test_2` is storing rejects (or you can set `ignore_errors=False`).

Once these two views have been loaded from the same data, you can test the amount of records for both. You should see a discrepancy:

```
WITH view_counts AS (
    SELECT
        (SELECT COUNT(*) FROM test_1) AS test_1_count,
        (SELECT COUNT(*) FROM test_2) AS test_2_count
    )
SELECT
    test_1_count,
    test_2_count,
    ABS((test_2_count - test_1_count)) AS difference
FROM
    view_counts;
```

And you can looking at the records that exist in `test_2` but not in `test_1`:

```
SELECT *
FROM test_2
EXCEPT
SELECT *
FROM test_1;
```


### OS:

macOS 14.6.1, Apple M1 (AArch64)

### DuckDB Version:

1.1.1

### DuckDB Client:

Python

### Hardware:

_No response_

### Full Name:

Phillip Wright

### Affiliation:

Shipt

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:
Thanks for reporting, we'll take a look!

Fix patch:
diff --git a/data/csv/auto/14177.csv b/data/csv/auto/14177.csv
new file mode 100644
index 000000000000..a88945f8e55b
--- /dev/null
+++ b/data/csv/auto/14177.csv
@@ -0,0 +1,14 @@
+"id","description"
+"1","This is a test description for id 1"
+"2","This is a test description,
+This is a
+longer description for id 2"
+"3","This is a test description,
+This is a
+longer description for id 3"
+"4","This is a test description,
+This is a
+longer description for id 4"
+"5","This is a test description,
+This is a
+longer description for id 5"
\ No newline at end of file
diff --git a/src/execution/operator/csv_scanner/scanner/string_value_scanner.cpp b/src/execution/operator/csv_scanner/scanner/string_value_scanner.cpp
index 173ca8a17e72..43636c50b406 100644
--- a/src/execution/operator/csv_scanner/scanner/string_value_scanner.cpp
+++ b/src/execution/operator/csv_scanner/scanner/string_value_scanner.cpp
@@ -1470,10 +1470,6 @@ void StringValueScanner::SetStart() {
 		}
 		return;
 	}
-	if (state_machine->options.IgnoreErrors()) {
-		// If we are ignoring errors we don't really need to figure out a line.
-		return;
-	}
 	// The result size of the data after skipping the row is one line
 	// We have to look for a new line that fits our schema
 	// 1. We walk until the next new line


Test patch:
diff --git a/test/sql/copy/csv/auto/test_14177.test b/test/sql/copy/csv/auto/test_14177.test
new file mode 100644
index 000000000000..4e5354e44caf
--- /dev/null
+++ b/test/sql/copy/csv/auto/test_14177.test
@@ -0,0 +1,11 @@
+# name: test/sql/copy/csv/auto/test_14177.test
+# description: Test CSV Sample works for #14177
+# group: [auto]
+
+statement ok
+PRAGMA enable_verification
+
+query I
+select count(*) FROM (FROM read_csv('data/csv/auto/14177.csv', buffer_size=80, ignore_errors = true)) as t
+----
+5
\ No newline at end of file