[SPARK-27060] DDL Commands are accepting Keywords like create, drop as tableName

• Type: Improvement
• Status: Resolved
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 2.3.2, 2.4.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Sachin Ramachandra Setty
• Created: 05/Mar/19 14:24
• Updated: 12/Dec/22 18:10
• Resolved: 11/Mar/19 05:26

Description

Seems to be a compatibility issue compared to other components such as hive and mySql.
DDL commands are successful even though the tableName is same as keyword.

Tested with columnNames as well and issue exists.

Whereas, Hive-Beeline is throwing ParseException and not accepting keywords as tableName or columnName and mySql is accepting keywords only as columnName.

Spark-Behaviour :

Connected to: Spark SQL (version 2.3.2.0101)
CLI_DBMS_APPID
Beeline version 1.2.1.spark_2.3.2.0101 by Apache Hive
0: jdbc:hive2://10.18.3.XXX:23040/default> create table create(id int);
+---------+--+
| Result  |
+---------+--+
+---------+--+
No rows selected (0.255 seconds)
0: jdbc:hive2://10.18.3.XXX:23040/default> create table drop(int int);
+---------+--+
| Result  |
+---------+--+
+---------+--+
No rows selected (0.257 seconds)
0: jdbc:hive2://10.18.3.XXX:23040/default> drop table drop;
+---------+--+
| Result  |
+---------+--+
+---------+--+
No rows selected (0.236 seconds)
0: jdbc:hive2://10.18.3.XXX:23040/default> drop table create;
+---------+--+
| Result  |
+---------+--+
+---------+--+
No rows selected (0.168 seconds)
0: jdbc:hive2://10.18.3.XXX:23040/default> create table tab1(float float);
+---------+--+
| Result  |
+---------+--+
+---------+--+
No rows selected (0.111 seconds)
0: jdbc:hive2://10.18.XXX:23040/default> create table double(double float);
+---------+--+
| Result  |
+---------+--+
+---------+--+
No rows selected (0.093 seconds)

Hive-Behaviour :

Connected to: Apache Hive (version 3.1.0)
Driver: Hive JDBC (version 3.1.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.0 by Apache Hive

0: jdbc:hive2://10.18.XXX:21066/> create table create(id int);
Error: Error while compiling statement: FAILED: ParseException line 1:13 cannot recognize input near 'create' '(' 'id' in table name (state=42000,code=40000)

0: jdbc:hive2://10.18.XXX:21066/> create table drop(id int);
Error: Error while compiling statement: FAILED: ParseException line 1:13 cannot recognize input near 'drop' '(' 'id' in table name (state=42000,code=40000)

0: jdbc:hive2://10.18XXX:21066/> create table tab1(float float);
Error: Error while compiling statement: FAILED: ParseException line 1:18 cannot recognize input near 'float' 'float' ')' in column name or constraint (state=42000,code=40000)

0: jdbc:hive2://10.18XXX:21066/> drop table create(id int);
Error: Error while compiling statement: FAILED: ParseException line 1:11 cannot recognize input near 'create' '(' 'id' in table name (state=42000,code=40000)

0: jdbc:hive2://10.18.XXX:21066/> drop table drop(id int);
Error: Error while compiling statement: FAILED: ParseException line 1:11 cannot recognize input near 'drop' '(' 'id' in table name (state=42000,code=40000)

mySql :
CREATE TABLE CREATE(ID integer);
Error: near "CREATE": syntax error

CREATE TABLE DROP(ID integer);
Error: near "DROP": syntax error

CREATE TABLE TAB1(FLOAT FLOAT);
Success

Issue Links

• duplicates: SPARK-26976 Forbid reserved keywords as identifiers when ANSI mode is on
• relates to: SPARK-26215 define reserved keywords after SQL standard