[CARBONDATA-4128] Merge SQL command fails with different case for column name and also if the command is input with different case

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 2.1.0
• Component/s: data-query
• Environment: Spark 2.4.5
• Assignee: Unassigned
• Reporter: Chetan Bhat
• Created: 10/Feb/21 15:45
• Updated: 19/Feb/21 08:24

Description

Steps:-

 

Issue 1 : Merge command fails for case insensitive column name.

drop table if exists A;
drop table if exists B;
CREATE TABLE A(id Int, name string, description string,address string, note string) stored as carbondata tblproperties('long_string_columns'='description,note','table_blocksize'='1','SORT_SCOPE'='global_sort','table_page_size_inmb'='1');
insert into A select 1,"name11111","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into A select 2,"name11112","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into A select 3,"name11113","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into A select 4,"name11114","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into A select 5,"name11115","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";

CREATE TABLE B(id Int, name string, description string,address string, note string) stored as carbondata tblproperties('long_string_columns'='description,note','table_blocksize'='1','SORT_SCOPE'='global_sort','table_page_size_inmb'='1');
insert into B select 1,"name11111","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into B select 2,"name11112","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into B select 3,"name11113","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into B select 6,"name11114","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
insert into B select 7,"name11115","asasfdfdfdsf","tutyutyuty","6867898980909099-0-0-0878676565454545465768798";
--merge
MERGE INTO A USING B ON A.id=B.id WHEN MATCHED THEN DELETE;

 

Issue :- Merge SQL command fails with different case for column name

0: jdbc:hive2://linux-63:22550/> MERGE INTO A USING B ON A.id=B.id WHEN MATCHED THEN DELETE;
Error: org.apache.spark.sql.AnalysisException: == Spark Parser: org.apache.spark.sql.hive.FISqlParser ==

mismatched input 'MERGE' expecting {'(', 'SELECT', 'FROM', 'ADD', 'DESC', 'EMPOWER', 'WITH', 'VALUES', 'CREATE', 'TABLE', 'INSERT', 'DELETE', 'DESCRIBE', 'EXPLAIN', 'SHOW', 'USE', 'DROP', 'ALTER', 'MAP', 'SET', 'RESET', 'START', 'COMMIT', 'ROLLBACK', 'REDUCE', 'REFRESH', 'CLEAR', 'CACHE', 'UNCACHE', 'DFS', 'TRUNCATE', 'ANALYZE', 'LIST', 'REVOKE', 'GRANT', 'LOCK', 'UNLOCK', 'MSCK', 'EXPORT', 'IMPORT', 'LOAD', 'HEALTHCHECK'}(line 1, pos 0)

== SQL ==
MERGE INTO A USING B ON A.id=B.id WHEN MATCHED THEN DELETE
^^^

== Carbon Parser: org.apache.spark.sql.parser.CarbonExtensionSpark2SqlParser ==
[1.1] failure: identifier matching regex (?i)EXPLAIN expected

MERGE INTO A USING B ON A.id=B.id WHEN MATCHED THEN DELETE
^;
== Antlr Parser: org.apache.spark.sql.parser.CarbonAntlrParser ==
org.apache.spark.sql.parser.CarbonSqlBaseParser$ValueExpressionDefaultContext cannot be cast to org.apache.spark.sql.parser.CarbonSqlBaseParser$ComparisonContext; (state=,code=0)
0: jdbc:hive2://linux-63:22550/>

 

Issue 2 : merge into command is not working as case sensitive and fails as mentioned below.

0: jdbc:hive2://linux1:22550/> merge into a using b on a.ID=b.ID when matched then delete;
Error: org.apache.carbondata.common.exceptions.sql.MalformedCarbonCommandException: Parse failed! (state=,code=0)
0: jdbc:hive2://linux1:22550/> merge into a using b on A.ID=B.ID when matched then delete;
Error: org.apache.carbondata.common.exceptions.sql.MalformedCarbonCommandException: Parse failed! (state=,code=0)
0: jdbc:hive2://linux1:22550/> merge into A using B on A.ID=B.ID when matched then delete;
Error: org.apache.carbondata.common.exceptions.sql.MalformedCarbonCommandException: Parse failed! (state=,code=0)