COPY INTO on a file works fine on Linux/OSX, but not on Windows

[monetdb-team]

Date: 2014-05-21 07:53:40 +0200
From: @hannesmuehleisen
To: SQL devs <>
Version: 11.17.13 (Jan2014-SP1)
CC: @njnes

Last updated: 2014-10-31 14:14:07 +0100

Comment 19814

Date: 2014-05-21 07:53:40 +0200
From: @hannesmuehleisen

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.114 Safari/537.36
Build Identifier:

I am running the following two commands on the DB:

CREATE TABLE DS (LopNr BIGINT, LopNr_Relationsperson BIGINT);
COPY 1 OFFSET 2 RECORDS INTO DS FROM '[...]/DS.txt' USING DELIMITERS '\t','\r\n','"' NULL AS '' LOCKED;

This works fine on OSX and Linux, but not on Windows. Error given:
"Incomplete record at end of file. Failed to import table."

Reproducible: Always

Steps to Reproduce:

1. CREATE TABLE
2. COPY INTO
3. ERROR

Actual Results:

Error message

Expected Results:

COPY INTO succeeds

Comment 19815

Date: 2014-05-21 07:54:13 +0200
From: @hannesmuehleisen

Created attachment 286
File that fails to import

Attached file: DS.txt (text/plain, 41 bytes)
Description: File that fails to import

Comment 19816

Date: 2014-05-21 07:55:47 +0200
From: @hannesmuehleisen

File has emtpy line at the end, but the COPY INTO reflects that so it should be ignored.

Comment 19817

Date: 2014-05-21 13:21:30 +0200
From: @sjoerdmullender

Have you tried using ... USING DELIMITERS '\t','\n','"' ... on Windows?

Comment 19818

Date: 2014-05-21 17:11:12 +0200
From: @hannesmuehleisen

Sjoerd, good guess, that works! Weird...

Comment 19819

Date: 2014-05-21 17:21:12 +0200
From: @hannesmuehleisen

Of course, this behavior should be somewhat consistent between platforms.

Comment 19820

Date: 2014-05-21 17:44:56 +0200
From: @sjoerdmullender

If you're reading a file in TEXT mode on Windows, \r\n in the file are transparently converted to \n as seen by the program. So when you specify \r\n as delimiter, you need an extra \r in the input: one to match the \r in the delimiter, and one to be discarded by the transparent text mode conversion.

In order to make things really consistent between platforms, perhaps we should ignore any \r we encounter, both in the input file and in the delimiter (or perhaps only when the \r occurs just before a \n).

Comment 19890

Date: 2014-07-09 14:04:04 +0200
From: @njnes

if (on windows) we ignore \r before a \n in de delimiter (when reading
a file in text mode) we should have consistent behaviour.

Comment 19984

Date: 2014-08-06 19:09:09 +0200
From: @njnes

what is the current status of this bug

Comment 20150

Date: 2014-09-12 14:56:56 +0200
From: @sjoerdmullender

I'm assuming this is fixed now with changesets 1f4ed128ee53 and 80e6748ab8cc

Comment 20355

Date: 2014-10-31 14:14:07 +0100
From: @sjoerdmullender

Oct2014 has been released.