Bug #98143 LOAD DATA INFILE column list does not allow column named `ROW`
Submitted: 7 Jan 2020 15:13 Modified: 7 Jan 2020 16:39
Reporter: Andew Thornton Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.18 OS:Red Hat
Assigned to: CPU Architecture:Any

[7 Jan 2020 15:13] Andew Thornton
MySQL client running a load data infile command with a column list does not allow a column named ROW in the column list.  5.7 and before allowed this.

How to repeat:
echo -e "1" > /tmp/test

mysql> CREATE TABLE t1 (`ROW` int, `ROW1` int);
Query OK, 0 rows affected (0.05 sec)

mysql> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1 (ROW1);
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1 (ROW);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROW)' at line 1

Suggested fix:
It either needs to work as previous or allow columns to be encapsulated by the ` as per normal.
[7 Jan 2020 15:22] MySQL Verification Team
Thank you for the bug report. Reserved word as docomented:


ROW (R); became reserved in 8.0.2

ROWS (R); became reserved in 8.0.2
[7 Jan 2020 15:34] Andew Thornton
Thanks for the clarification.  The issue is it breaks existing systems.
I can create a column named `ROW`, I can select, update, whatever -
but I now can't use LOAD DATA to upload the data to it anymore.
All other MySQL commands allow me to instruct MySQL to understand that a column is a column by encapsulating it with the ` character, which would have solved the problem.
Is there no way of uploading data into a column that has a reserved keyword?  As there are more and more reserved keywords all the time, eventually all columns will have to be called something obscure rather than what we want to call them!
[7 Jan 2020 15:43] Andew Thornton
Maybe this is a better description of the actual bug then:

LOAD DATA INFILE does not honour column quoting of reserved keywords as detailed in the documentation:


As more and more reserved keywords are created and there is no way of knowing what future reserved keywords will be, LOAD DATA INFILE MUST honour quoting of columns to ensure the addition of new keywords does not cause extra problems when upgrading versions of MySQL.

Will you re-open as this or will I need to re-report?
[7 Jan 2020 16:34] Andew Thornton
Apologies, it looks like it does honour encapsulating columns with `.
Problem solved.
[7 Jan 2020 16:39] MySQL Verification Team
Yes, you are right. You actually forgot to backquote it .....