Bug 3965 - Not possible to quote/escape single quote character in the name of the file to load.
Summary: Not possible to quote/escape single quote character in the name of the file t...
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.21.13 (Jul2015-SP2)
Hardware: x86_64 (amd64/em64t) Windows
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-03-23 23:15 CET by Gennadiy
Modified: 2016-04-14 13:40 CEST (History)
2 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Gennadiy 2016-03-23 23:15:39 CET
User-Agent:       Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:38.9) Gecko/20100101 Goanna/2.0 Firefox/38.9 PaleMoon/26.1.1
Build Identifier: 

I am trying to load data from a text file that has single quote character in its name into a table:

> "C:\Program Files\MonetDB\MonetDB5\mclient.bat" --port=50000 --user=monetdb
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2)
Database: MonetDB v11.21.13 (Jul2015-SP2), 'soft10_20160318_172621'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>
sql>
sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin's_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED;
more>
more>


Everything works fine if there is no single quote character in the name of the file:

sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachins_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED;
120 affected rows (12.3s)


I was trying to precede the single quote character by a \ (backslash) or to double it, but it still did not work:

sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED;
could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory

sql>COPY 120 OFFSET 2 RECORDS INTO "observations_to_score1" FROM 'C:\\tmp\\sachin''s_data_with_requested_column_03_03_2016.csv' USING DELIMITERS ',', '\n', '"' LOCKED;
could not open file 'C:\\tmp\\sachin\'s_data_with_requested_column_03_03_2016.csv': No such file or directory


> dir c:\tmp\sa*.csv
 Volume in drive C is S3A9506D003
 Volume Serial Number is 00B9-C775

 Directory of c:\tmp

2016-03-18  17:12           144,202 sachin's_data_with_requested_column_03_03_2016.csv
2016-03-18  17:12           144,202 sachins_data_with_requested_column_03_03_2016.csv
               2 File(s)        288,404 bytes
               0 Dir(s)  377,532,665,856 bytes free

I am using the following version of MonetDB:

# MonetDB 5 server v11.21.13 "Jul2015-SP2"
# Serving database 'soft10_20160318_172621', using 4 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 3.799 GiB available main-memory.
# Copyright (c) 1993-July 2008 CWI.
# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
# Visit http://www.monetdb.org/ for further information
# Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
# Start processing logs sql/sql_logs version 52200
# Start reading the write-ahead log 'sql_logs\sql\log.11'
# Finished reading the write-ahead log 'sql_logs\sql\log.11'
# Finished processing logs sql/sql_logs
# MonetDB/SQL module loaded
>

Reproducible: Always
Comment 1 MonetDB Mercurial Repository cwiconfidential 2016-03-30 12:33:47 CEST
Changeset bbacfc235a00, made by Niels Nes <niels@cwi.nl> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=bbacfc235a00

Changeset description:

	fix bug 3965, ie convert internal represenation of the filename, such
	that single quotes can be used in filenames.
Comment 2 Niels Nes cwiconfidential 2016-03-30 12:45:41 CEST
internal filename strings are now properly converted to allow for single quotes.
Use '' in your sql to pass the quote.
Comment 3 Sjoerd Mullender cwiconfidential 2016-04-11 11:16:00 CEST
Jul2015-SP4 has been released.
Comment 4 MonetDB Mercurial Repository cwiconfidential 2016-04-14 10:43:57 CEST
Changeset 59a69a3cac1e, made by Sjoerd Mullender <sjoerd@acm.org> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=59a69a3cac1e

Changeset description:

	Convert internal representation of file names for COPY BINARY INTO.
	Also see changeset bbacfc235a00 and bug 3965.
Comment 5 Hannes Muehleisen cwiconfidential 2016-04-14 13:40:03 CEST
eh this broke all of our R import scripts on windows :(
Comment 6 Hannes Muehleisen cwiconfidential 2016-04-14 13:40:36 CEST
including the automated csv import included in the MonetDB R package.