[Bug]: load data inline does not work when specifying fields enclosed by '"' by throwing an error ERROR 20301 (HY000): invalid input: csvParser error: unterminated quoted field

[aronchanisme]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

1.2-dev

Commit ID

a5c552e

Other Environment Information

- Hardware parameters: 8c 16g ssd
- OS type: Debian 12 (kernal: 6.1.0-18-amd64)
- Others: none

Actual Behavior

load data inline does not work when specifying fields enclosed by '"' by throwing an error ERROR 20301 (HY000): invalid input: csvParser error: unterminated quoted field

MySQL [test]> drop table user;
Query OK, 0 rows affected (0.016 sec)

MySQL [test]> create table user (name varchar(25), age int, city varchar(25));
Query OK, 0 rows affected (0.013 sec)

MySQL [test]> system cat /tmp/1.csv
"A","23","Hello"
MySQL [test]> load data local infile '/tmp/1.csv' into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
Query OK, 1 row affected (0.011 sec)

MySQL [test]> select * from user;
+------+------+-------+
| name | age  | city  |
+------+------+-------+
| A    |   23 | Hello |
+------+------+-------+
1 row in set (0.004 sec)

MySQL [test]> load data inline format='csv', data=$XXX$
    -> "zhangsan","26","Shanxi;Xian" $XXX$
    -> into table user
    -> fields terminated by ',' enclosed by '"'
    -> lines terminated by '\n'
    -> (name,age,city);
ERROR 20301 (HY000): invalid input: csvParser error: unterminated quoted field
MySQL [test]> load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi;Xian" $XXX$ into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
ERROR 20301 (HY000): invalid input: csvParser error: unterminated quoted field
MySQL [test]> load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi" $XXX$ into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
ERROR 20301 (HY000): invalid input: csvParser error: unterminated quoted field
MySQL [test]> system vi /tmp/2.csv
MySQL [test]> load data local infile '/tmp/2.csv' into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
Query OK, 1 row affected (0.004 sec)

MySQL [test]> system cat /tmp/2.csv
"zhangsan","26","Shanxi;Xian"
MySQL [test]> select * from test.user;
+----------+------+-------------+
| name     | age  | city        |
+----------+------+-------------+
| A        |   23 | Hello       |
| zhangsan |   26 | Shanxi;Xian |
+----------+------+-------------+
2 rows in set (0.001 sec)

MySQL [test]> select git_version();
+---------------+
| git_version() |
+---------------+
| a5c552ef5     |
+---------------+
1 row in set (0.001 sec)

Expected Behavior

Given an csv file, which contains content like this

"zhangsan","26","Shanxi;Xian"

Both mo and mysql can load data local infile from the csv file and put data into the table successfully, however mo's load data inline way to load the data directly from sql statement failed. The expectation is below sql works

load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi" $XXX$ into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi;XiAn" $XXX$ into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);

Expected result of a select * from test.user after the load:

MySQL [test]> select * from test.user;
+------------+------+-------------+
| name       | age  | city        |
+------------+------+-------------+
| zhangsan   |   26 | Shanxi|
| zhangsan   |   26 | Shanxi;Xian |
+------------+------+-------------+
2 rows in set (0.001 sec)

Steps to Reproduce

create database if not exists test;
use test;
drop table if exists user;
create table user (name varchar(25), age int, city varchar(25));
load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi" $XXX$ into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi;XiAn" $XXX$ into table test.user fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
select * from user;

Additional information

#16790

[aronchanisme]

#16790 is stuck by this issue.

[jensenojs]

@aronchanisme load data infile是能正确支持的, 我看了一下文档load data inline是没有那些 terminated by enclosed by lines terminated by 之类的说明的, load data inline可能是没有做这些解析的. 莫尘当时做这个东西的时候可能就没打算支持这些东西

[jensenojs]

not working ont it

[huby2358]

[huby2358]

之前改的有问题，csvparser这块还要再看一下

[huby2358]

这个load data inline失败，load data infile成功，是因为$XXX$ "zhangsan","26","Shanxi" $XXX$， 是因为数据data前后有空格，如果是$XXX$"zhangsan","26","Shanxi"$XXX$， 这个load 成功，这个data才和上面load data infile的内容相同，如果load data infile里面第一个字段内容后最后一个有空格，也会报错。

[aronchanisme]

Syntax issue. Doc needs to be updated.

root@test0:~# mo_ctl connect
2024-08-28 15:55:00.321 UTC+0800    [INFO]    Checking connectivity
2024-08-28 15:55:00.368 UTC+0800    [INFO]    Ok, connecting for user ... 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9087
Server version: 8.0.30-MatrixOne-v1.2.2 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t2(name varchar(25),age int,city varchar(25));
Query OK, 0 rows affected (0.02 sec)

mysql> load data inline format='csv', data=$XXX$"zhangsan","26","Shanxi;XiAn"$XXX$ into table t2 fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+----------+------+-------------+
| name     | age  | city        |
+----------+------+-------------+
| zhangsan |   26 | Shanxi;XiAn |
+----------+------+-------------+
1 row in set (0.00 sec)

mysql> load data inline format='csv', data=$XXX$ "zhangsan","26","Shanxi;XiAn" $XXX$ into table t2 fields terminated by ',' enclosed by '"'  lines terminated by '\n' (name,age,city);
ERROR 20301 (HY000): invalid input: csvParser error: unterminated quoted field

mysql> select git_version();
+---------------+
| git_version() |
+---------------+
| 74f81f7       |
+---------------+
1 row in set (0.00 sec)

[huby2358]

跟产品和测试对齐了，保持现状，不用修改了

[aronchanisme]

Doc request : matrixorigin/matrixorigin.io#589