LOAD DATA need support column/variable list and SET clause

[lysu]

For the last part of https://dev.mysql.com/doc/refman/8.0/en/load-data.html, load data support column/variable list and SET clause is useful if we want to load only some of a table's columns or import BINARY, VARBINARY and BIT data into TiDB.

1. create a table with a binary column.

CREATE TABLE `ld` (`v` binary(1) DEFAULT NULL );

2. prepare a data file in /home/data/test.txt with content

1

3. load data into binary column using

LOAD DATA LOCAL INFILE '/home/robi/data' INTO TABLE ld FIELDS TERMINATED BY ',' (@var1) set v = unhex(@var1);

and do a select

select * from ld;

2. What did you expect to see?

mysql> LOAD DATA LOCAL INFILE '/home/robi/data' INTO TABLE ld FIELDS TERMINATED BY ',' (@var1) set v = unhex(@var1);
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from ld;
+------+
| v    |
+------+
| �    |
+------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> LOAD DATA LOCAL INFILE '/home/robi/data' INTO TABLE ld FIELDS TERMINATED BY ',' (@var1) set v = unhex(@var1);
ERROR 1105 (HY000): line 1 column 86 near ") set v = unhex(@var1)" (total length 108)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

2.0.6

[kolbe]

It looks like the parser supports this now, but it's not handled correctly (see #11941).

[ghost]

Confirming that this was recently fixed in master (using some mysql cli magic to shell-out):

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 VARCHAR(255) DEFAULT NULL);
\! echo "68656C6C6F" > /tmp/test.txt
LOAD DATA LOCAL INFILE '/tmp/test.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' (@var1) SET c1 = unhex(@var1);
select * from t1;

..

mysql> select * from t1;
+-------+
| c1    |
+-------+
| hello |
+-------+
1 row in set (0.01 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-893-g4e829aaee
Edition: Community
Git Commit Hash: 4e829aaee7b656aa807814708ae05af5233302af
Git Branch: master
UTC Build Time: 2020-08-04 12:40:52
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

I am going to close this issue now. Thanks!