[Bug]: Columns cannot correspond when use load data inline

gaoy121 commented 6 months ago

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

v1.1.3

Commit ID

14c4496

Other Environment Information

- Hardware parameters:
- OS type: Centos7
- Others:

Actual Behavior

执行如下load data 语句：

load data inline format='csv', data=$XXX$
zhangsan,26,XiAn $XXX$
into table user 
fields terminated by ','
lines terminated by '\r\n'
(city,age,name)

zhangsan对应city列 26对应age列 XiAn对应name列

但最终的结果依然是按照数据库中的列存储的，结果如下：

Expected Behavior

No response

Steps to Reproduce

1.创建数据库
create database test;

2.创建数据表
use test;

CREATE TABLE `user` (
`name` VARCHAR(255) DEFAULT null,
`age` INT DEFAULT null,
`city` VARCHAR(255) DEFAULT null
)

3.执行load data语句
load data inline format='csv', data=$XXX$
zhangsan,26,XiAn $XXX$
into table user 
fields terminated by ','
lines terminated by '\r\n'
(city,age,name) 

4.查询表数据
select * from user;

Additional information

No response

aronchanisme commented 6 months ago

跟这个一并考虑：https://github.com/matrixorigin/MO-Cloud/issues/1433

aronchanisme commented 5 months ago

转测

aronchanisme commented 4 months ago

测试case

-- 1. 创建数据库
create database if not exists test;

-- 2. 创建数据表
use test;
CREATE TABLE `user` (
`name` VARCHAR(255) DEFAULT null,
`age` INT DEFAULT null,
`city` VARCHAR(255) DEFAULT null
);

-- 3. 执行load data语句
load data inline format='csv', data=$XXX$
XiAn,16,zhangsan $XXX$
into table user 
fields terminated by ','
lines terminated by '\r\n'
(city,age,name) ;
-- 即City='Xian', age=16, name='zhangsan'

-- 4. 查询表数据
select * from user;
-- 预期：
-- +----------+------+------+
-- | name     | age  | city |
-- +----------+------+------+
-- | zhangsan |   16 | XiAn |
-- +----------+------+------+

aronchanisme commented 4 months ago

Fixed. @gaoy121 Pls kindly take a look, thx.

Test result:

main(0cc26c508)


github@test0:/data/mo/1.2-dev/matrixone$ mo_ctl connect
2024-07-22 10:57:19.712 UTC+0800    [INFO]    Checking connectivity
2024-07-22 10:57:19.782 UTC+0800    [INFO]    Ok, connecting for user ... 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2730
Server version: 8.0.30-MatrixOne-v026508 MatrixOne

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> select git_version(); +---------------+ | git_version() | +---------------+ | 0cc26c508 | +---------------+ 1 row in set (0.00 sec)

mysql> system mo_ctl get_cid 2024-07-22 10:57:27.862 UTC+0800 [INFO] Try get mo commit id commit 0cc26c5089c918c6feb3a0548f06f19c82e0a766 Author: nitao badboynt@126.com Date: Mon Jul 22 01:01:55 2024 +0800

Refactor prepare to reuse Compile part 2 (#17625)

fix a bug that cause tpch hang when refactoring prepare statement

Approved by: @ouyuanning

2024-07-22 10:57:27.911 UTC+0800 [INFO] Get commit id succeeded mysql> system mo_ctl get_branch 2024-07-22 10:57:30.480 UTC+0800 [INFO] Try get mo branch 2024-07-22 10:57:30.531 UTC+0800 [INFO] Get branch succeeded, current branch: main mysql> mysql> -- 1. 创建数据库 mysql> create database if not exists test; Query OK, 1 row affected (0.01 sec)

mysql> mysql> -- 2. 创建数据表 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 user ( -> name VARCHAR(255) DEFAULT null, -> age INT DEFAULT null, -> city VARCHAR(255) DEFAULT null -> ); Query OK, 0 rows affected (0.03 sec)

mysql> mysql> -- 3. 执行load data语句 mysql> load data inline format='csv', data=$XXX$ -> XiAn,16,zhangsan $XXX$ -> into table user -> fields terminated by ',' -> lines terminated by '\r\n' -> (city,age,name) ; Query OK, 1 row affected (0.01 sec)


2. `1.2-dev`(`ebcfce8e5`)
```bash
github@test0:/data/mo/1.2-dev/matrixone$ mo_ctl connect
2024-07-22 11:01:02.321 UTC+0800    [INFO]    Checking connectivity
2024-07-22 11:01:02.402 UTC+0800    [INFO]    Ok, connecting for user ... 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7109
Server version: 8.0.30-MatrixOne-v85 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> select git_version();
+---------------+
| git_version() |
+---------------+
| ebcfce8e5     |
+---------------+
1 row in set (0.00 sec)

mysql> system mo_ctl get_cid
2024-07-22 11:01:15.944 UTC+0800    [INFO]    Try get mo commit id
commit ebcfce8e541ecd1dfb05411553b5e6b15da09741
Author: LiuBo <g.user.lb@gmail.com>
Date:   Fri Jul 19 22:04:24 2024 +0800

    [enhancement] stats: add metrics for stats trigger updating (#17618)

    add metrics for stats trigger updating

    Approved by: @XuPeng-SH, @zhangxu19830126, @sukki37
2024-07-22 11:01:15.991 UTC+0800    [INFO]    Get commit id succeeded
mysql> system mo_ctl get_branch
2024-07-22 11:01:18.047 UTC+0800    [INFO]    Try get mo branch
2024-07-22 11:01:18.102 UTC+0800    [INFO]    Get branch succeeded, current branch: 1.2-dev
mysql> 
mysql> -- 1. 创建数据库
mysql> create database if not exists test;
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> -- 2. 创建数据表
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 `user` (
    -> `name` VARCHAR(255) DEFAULT null,
    -> `age` INT DEFAULT null,
    -> `city` VARCHAR(255) DEFAULT null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> -- 3. 执行load data语句
mysql> load data inline format='csv', data=$XXX$
    -> XiAn,16,zhangsan $XXX$
    -> into table user 
    -> fields terminated by ','
    -> lines terminated by '\r\n'
    -> (city,age,name) ;
Query OK, 1 row affected (0.01 sec)

mysql> -- 即City='Xian', age=16, name='zhangsan'
mysql> 
mysql> -- 4. 查询表数据
mysql> select * from user;
+-----------+------+------+
| name      | age  | city |
+-----------+------+------+
| zhangsan  |   16 | XiAn |
+-----------+------+------+
1 row in set (0.00 sec)

mysql> -- 预期：
mysql> -- +----------+------+------+
mysql> -- | name     | age  | city |
mysql> -- +----------+------+------+
mysql> -- | zhangsan |   16 | XiAn |
mysql> -- +----------+------+------+

matrixorigin / matrixone