[MDEV-22581] LOAD DATA ENCLOSE BY preserves quotes if input file has lines terminated with '\r\n' Created: 2020-05-15  Updated: 2020-05-23  Resolved: 2020-05-18

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Data types
Affects Version/s: 10.4.8
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Marios Hadjieleftheriou Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian 4.19.67-2


 Description   

I am using LOAD DATA INFILE with ENCLOSED BY to ingest a csv file whose values are all enclosed by quotes (it does not matter whether it is single or double quotes).

For VARCHAR and TEXT fields, the quotes are properly removed from the values for all columns in the table except the last column, if the input csv file has lines terminated by '\r\n'.

Here is how to reproduce it:

CREATE TABLE `test_quotes` (
 
  `date` int(11) NOT NULL,
 
  `int_test` int(11) DEFAULT NULL,
 
  `varchar_test` varchar(5) DEFAULT NULL,
 
  `varchar_test2` varchar(5) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC


echo -e "'20200515','1','foo','bar'\r\n" > test_quotes
 
 
 
LOAD DATA INFILE 'test_quotes' INTO TABLE `test_quotes` FIELDS TERMINATED BY ',' ENCLOSED BY "'";
 
 
 
select * from test_quotes;
 
+----------+----------+--------------+---------------+
 
| date     | int_test | varchar_test | varchar_test2 |
 
+----------+----------+--------------+---------------+
 
| 20200515 |        1 | foo          | 'bar'         |
 
+----------+----------+--------------+---------------+



 Comments   
Comment by Marios Hadjieleftheriou [ 2020-05-15 ]

I realized that it was my mistake. It works fine if I do this:
LOAD DATA INFILE 'test_quotes' INTO TABLE `test_quotes` FIELDS TERMINATED BY ',' ENCLOSED BY "'" LINES TERMINATED BY '\r\n'

Does it make sense to make the default line terminator be both \n and \r\n?

Comment by Marios Hadjieleftheriou [ 2020-05-23 ]

Then it seems to preserve the quotes for the last column only, not all
columns.

On Mon, May 18, 2020 at 8:25 AM Sergei Golubchik (Jira) <jira@mariadb.org>

Generated at Thu Feb 08 09:15:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.