You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by zh...@apache.org on 2026/08/22 12:43:09 UTC
(shardingsphere) branch master updated: Support parsing more Oracle CREATE TABLE SQL (PREPROCESSOR, LDRTRIM, hash partition STORE IN) (#39559)
This is an automated email from the ASF dual-hosted git repository.
terrymanu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 5e64296dd28 Support parsing more Oracle CREATE TABLE SQL (PREPROCESSOR, LDRTRIM, hash partition STORE IN) (#39559)
5e64296dd28 is described below
commit 5e64296dd28bf23d10b62b9de7eae443619de6ed
Author: Liang Zhang <zh...@apache.org>
AuthorDate: Sat Aug 22 20:43:03 2026 +0800
Support parsing more Oracle CREATE TABLE SQL (PREPROCESSOR, LDRTRIM, hash partition STORE IN) (#39559)
- support PREPROCESSOR and LDRTRIM access parameters for external tables
- accept date format string after field datatype in external table field lists
- require literal parentheses in STORE IN clause of hashPartitionsByQuantity, aligning with Oracle syntax
- add 4 parser IT cases for the CREATE TABLE statements from issue #27108
Fixes #27108
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 4 +-
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 9 ++-
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 8 ++
.../src/main/resources/case/ddl/create-table.xml | 92 ++++++++++++++++++++++
.../resources/sql/supported/ddl/create-table.xml | 69 ++++++++++++++++
5 files changed, 178 insertions(+), 4 deletions(-)
diff --git a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index d410abd8fcc..50a54026a24 100644
--- a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -82,7 +82,7 @@ identifier
;
unreservedWord
- : SELECT | INSERT | UPDATE | DELETE | CREATE | ALTER | DROP | TRUNCATE | SCHEMA | GRANT | REVOKE | ADD | SET | TABLE | COLUMN | INDEX | CONSTRAINT | PRIMARY | UNIQUE | FOREIGN | KEY | POSITION | PRECISION | FUNCTION | CONSTRUCTOR | RESULT | TRIGGER | MAP | PROCEDURE | SPECIFICATION | VIEW | INTO | VALUES | WITH | UNION | DISTINCT | CASE | WHEN | CAST | TRIM | SUBSTRING | FROM | NATURAL | JOIN | FULL | INNER | OUTER | LEFT | RIGHT | CROSS | USING | WHERE | AS | ON | IF | ELSE | ELSIF [...]
+ : SELECT | INSERT | UPDATE | DELETE | CREATE | ALTER | DROP | TRUNCATE | SCHEMA | GRANT | REVOKE | ADD | SET | TABLE | COLUMN | INDEX | CONSTRAINT | PRIMARY | UNIQUE | FOREIGN | KEY | POSITION | PRECISION | FUNCTION | CONSTRUCTOR | RESULT | TRIGGER | MAP | PROCEDURE | SPECIFICATION | VIEW | INTO | VALUES | WITH | UNION | DISTINCT | CASE | WHEN | CAST | TRIM | SUBSTRING | FROM | NATURAL | JOIN | FULL | INNER | OUTER | LEFT | RIGHT | CROSS | USING | WHERE | AS | ON | IF | ELSE | ELSIF [...]
| BEQUEATH | BINARY | ESCAPE | MOD | XOR | ROW | ROWS | UNKNOWN | ALWAYS | CASCADE | CHECK | CONVERSION | GENERATED | PRIVILEGES | READ | WRITE | REFERENCES | START | TRANSACTION | USER | ROLE | VISIBLE | INVISIBLE | EXECUTE | USE | DEBUG | UNDER | FLASHBACK | ARCHIVE | REFRESH | QUERY | REWRITE | KEEP | SEQUENCE | INHERIT | TRANSLATE | MERGE | AT | BITMAP | CACHE | NOCACHE | CHECKPOINT | CONNECT | CONSTRAINTS | CYCLE | NOCYCLE | DBTIMEZONE | ENCRYPT | DECRYPT | DEFERRABLE | DEFERRED [...]
| SINGLE_K | SINGLE_M | SINGLE_G | SINGLE_T | SINGLE_P | SINGLE_E | SINGLE_H | V1
;
@@ -1976,5 +1976,5 @@ optionallyEnclosedBySpec
;
lrtrimSpec
- : LTRIM | RTRIM | LRTRIM
+ : LTRIM | RTRIM | LRTRIM | LDRTRIM
;
diff --git a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4 b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
index 00f7d02d875..bde067ccdcc 100644
--- a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
+++ b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
@@ -1111,6 +1111,7 @@ recordFormatOption
: recordsSpec
| dataEndianSpec
| characterSetSpec
+ | preprocessSpec
| badfileSpec
| discardfileSpec
| logfileSpec
@@ -1129,6 +1130,10 @@ characterSetSpec
: CHARACTERSET identifier
;
+preprocessSpec
+ : PREPROCESSOR externalFileName
+ ;
+
badfileSpec
: BADFILE externalFileName
;
@@ -1154,7 +1159,7 @@ fieldList
;
fieldDefinition
- : columnName positionSpecification? fieldDataType?
+ : columnName positionSpecification? fieldDataType? (STRING_ | DOUBLE_QUOTED_TEXT)?
;
fieldDataType
@@ -1320,7 +1325,7 @@ hashPartitions
;
hashPartitionsByQuantity
- : PARTITIONS INTEGER_ (STORE IN (tablespaceName (COMMA_ tablespaceName)*))? (tableCompression | indexCompression)? (OVERFLOW STORE IN (tablespaceName (COMMA_ tablespaceName)*))?
+ : PARTITIONS INTEGER_ (STORE IN LP_ tablespaceName (COMMA_ tablespaceName)* RP_)? (tableCompression | indexCompression)? (OVERFLOW STORE IN LP_ tablespaceName (COMMA_ tablespaceName)* RP_)?
;
indexCompression
diff --git a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4 b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index 0723a65aad4..f3a6baa5e45 100644
--- a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++ b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -3800,6 +3800,14 @@ CHARACTERSET
: C H A R A C T E R S E T
;
+PREPROCESSOR
+ : P R E P R O C E S S O R
+ ;
+
+LDRTRIM
+ : L D R T R I M
+ ;
+
DENSE_RANKM
:D E N S E UL_ R A N K M
;
diff --git a/test/it/parser/src/main/resources/case/ddl/create-table.xml b/test/it/parser/src/main/resources/case/ddl/create-table.xml
index bb13c86d219..5cebbaeea97 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-table.xml
@@ -1359,6 +1359,15 @@
<column name="column3" />
</column-definition>
</create-table>
+ <create-table sql-case-id="create_table_partition_by_hash_with_store_in">
+ <table name="scubagear" start-index="13" stop-index="21" />
+ <column-definition type="NUMBER" start-index="29" stop-index="37">
+ <column name="id" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="46" stop-index="63">
+ <column name="name" />
+ </column-definition>
+ </create-table>
<create-table sql-case-id="create_table_with_xmltype_table_oracle">
<table name="xwarehouses" start-index="13" stop-index="23" />
@@ -2922,6 +2931,29 @@
<primary-key-column name="doc_id" start-index="183" stop-index="188" />
</constraint-definition>
</create-table>
+ <create-table sql-case-id="create_table_organization_index_overflow_with_hash_partitions_store_in">
+ <table name="sales" start-index="13" stop-index="17" />
+ <column-definition type="NUMBER" start-index="19" stop-index="35">
+ <column name="acct_no" start-index="19" stop-index="25" />
+ </column-definition>
+ <column-definition type="CHAR" start-index="57" stop-index="74">
+ <column name="acct_name" start-index="57" stop-index="65" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="96" stop-index="119">
+ <column name="amount_of_sale" start-index="96" stop-index="109" />
+ </column-definition>
+ <column-definition type="INTEGER" start-index="141" stop-index="155">
+ <column name="week_no" start-index="141" stop-index="147" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="177" stop-index="203">
+ <column name="sale_details" start-index="177" stop-index="188" />
+ </column-definition>
+ <constraint-definition start-index="219" stop-index="259">
+ <primary-key-column name="acct_no" start-index="232" stop-index="238" />
+ <primary-key-column name="acct_name" start-index="241" stop-index="249" />
+ <primary-key-column name="week_no" start-index="252" stop-index="258" />
+ </constraint-definition>
+ </create-table>
<create-table sql-case-id="create_table_ref_type1">
<table name="departments_t" start-index="13" stop-index="25" />
@@ -3303,6 +3335,66 @@
<column name="loc" start-index="66" stop-index="68"/>
</column-definition>
</create-table>
+ <create-table sql-case-id="create_table_external_with_preprocessor_and_ldrtrim" >
+ <table name="sales_transactions_ext" start-index="13" stop-index="34" />
+ <column-definition type="NUMBER" start-index="37" stop-index="50">
+ <column name="PROD_ID" start-index="37" stop-index="43"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="54" stop-index="67">
+ <column name="CUST_ID" start-index="54" stop-index="60"/>
+ </column-definition>
+ <column-definition type="DATE" start-index="71" stop-index="82">
+ <column name="TIME_ID" start-index="71" stop-index="77"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="86" stop-index="100">
+ <column name="CHANNEL_ID" start-index="86" stop-index="95"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="104" stop-index="118">
+ <column name="PROMO_ID" start-index="104" stop-index="111"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="122" stop-index="141">
+ <column name="QUANTITY_SOLD" start-index="122" stop-index="134"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="145" stop-index="168">
+ <column name="AMOUNT_SOLD" start-index="145" stop-index="155"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="172" stop-index="193">
+ <column name="UNIT_COST" start-index="172" stop-index="180"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="197" stop-index="219">
+ <column name="UNIT_PRICE" start-index="197" stop-index="206"/>
+ </column-definition>
+ </create-table>
+ <create-table sql-case-id="create_table_external_with_field_date_format" >
+ <table name="sales_transactions_ext" start-index="13" stop-index="34" />
+ <column-definition type="NUMBER" start-index="37" stop-index="50">
+ <column name="PROD_ID" start-index="37" stop-index="43"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="53" stop-index="66">
+ <column name="CUST_ID" start-index="53" stop-index="59"/>
+ </column-definition>
+ <column-definition type="DATE" start-index="70" stop-index="81">
+ <column name="TIME_ID" start-index="70" stop-index="76"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="84" stop-index="100">
+ <column name="CHANNEL_ID" start-index="84" stop-index="93"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="104" stop-index="118">
+ <column name="PROMO_ID" start-index="104" stop-index="111"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="121" stop-index="140">
+ <column name="QUANTITY_SOLD" start-index="121" stop-index="133"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="144" stop-index="167">
+ <column name="AMOUNT_SOLD" start-index="144" stop-index="154"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="170" stop-index="191">
+ <column name="UNIT_COST" start-index="170" stop-index="178"/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="195" stop-index="217">
+ <column name="UNIT_PRICE" start-index="195" stop-index="204"/>
+ </column-definition>
+ </create-table>
<create-table sql-case-id="create_table_external_oracle_datapump" >
<table name="dept_xt" start-index="13" stop-index="19" />
diff --git a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
index 9c9a068a510..e40bafce651 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
@@ -109,6 +109,12 @@
<sql-case id="create_local_temporary_table" value="CREATE LOCAL TEMPORARY TABLE t_order (order_id INTEGER, user_id INTEGER, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10))" db-types="PostgreSQL,openGauss" />
<sql-case id="create_table_with_range_partition" value="CREATE TABLE t_order (order_id INTEGER, user_id INTEGER, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10)) PARTITION BY RANGE (order_id)" db-types="PostgreSQL,openGauss" />
<sql-case id="create_table_with_partition_oracle" value="CREATE TABLE t_order (order_id NUMBER(10), user_id NUMBER(10), status VARCHAR2(10), column1 VARCHAR2(10), column2 VARCHAR2(10), column3 VARCHAR2(10)) PARTITION BY HASH (order_id) PARTITIONS 2" db-types="Oracle" />
+ <sql-case id="create_table_partition_by_hash_with_store_in" value="CREATE TABLE scubagear
+ (id NUMBER,
+ name VARCHAR2 (60))
+ PARTITION BY HASH (id)
+ PARTITIONS 4
+ STORE IN (gear1, gear2, gear3, gear4)" db-types="Oracle" />
<sql-case id="create_table_with_xmltype_table_oracle" value="CREATE TABLE xwarehouses OF XMLTYPE XMLSCHEMA "http://www.example.com/xwarehouses.xsd" ELEMENT "Warehouse" ;" db-types="Oracle" />
<sql-case id="create_table_with_xmltype_column_oracle" value="CREATE TABLE xwarehouses (warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS CLOB;" db-types="Oracle" />
<sql-case id="create_table_with_xmltype_column_clob_oracle" value="CREATE TABLE xwarehouses (warehouse_id NUMBER, warehouse_spec XMLTYPE) XMLTYPE warehouse_spec STORE AS SECUREFILE CLOB;" db-types="Oracle" />
@@ -325,6 +331,19 @@
TABLESPACE admin_tbs
PCTTHRESHOLD 20
OVERFLOW TABLESPACE admin_tbs2" db-types="Oracle" />
+ <sql-case id="create_table_organization_index_overflow_with_hash_partitions_store_in" value="CREATE TABLE sales(acct_no NUMBER(5),
+ acct_name CHAR(30),
+ amount_of_sale NUMBER(6),
+ week_no INTEGER,
+ sale_details VARCHAR2(1000),
+ PRIMARY KEY (acct_no, acct_name, week_no))
+ ORGANIZATION INDEX
+ INCLUDING week_no
+ OVERFLOW
+ PARTITION BY HASH (week_no)
+ PARTITIONS 16
+ STORE IN (ts1, ts2, ts3, ts4)
+ OVERFLOW STORE IN (ts3, ts6, ts9)" db-types="Oracle" />
<sql-case id="create_table_ref_type1" value="CREATE TABLE departments_t (d_no NUMBER, mgr_ref REF employees_typ SCOPE IS employees_obj_t)" db-types="Oracle" />
<sql-case id="create_table_ref_type2" value="CREATE TABLE departments_t (d_no NUMBER, mgr_ref REF employees_typ CONSTRAINT mgr_in_emp REFERENCES employees_obj_t)" db-types="Oracle" />
<sql-case id="create_table_partition_by_range_subpartitions" value="CREATE TABLE customers_part
@@ -381,6 +400,56 @@
<sql-case id="create_table_with_fields_varcharc_varrawc" value="CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), resume CHAR(2000), picture RAW (2000)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (FIELDS (first_name VARCHARC(5,12), last_name VARCHARC(2,20), resume VARCHARC(4,10000), picture VARRAWC(4,100000))) LOCATION ('info.dat'))" db-types="Oracle"/>
<sql-case id="create_table_external_with_access_parameters" value="CREATE TABLE dept_external (deptno NUMBER(6), dname VARCHAR2(20), loc VARCHAR2(25)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY admin ACCESS PARAMETERS (RECORDS DELIMITED BY newline BADFILE 'ulcase1.bad' DISCARDFILE 'ulcase1.dis' LOGFILE 'ulcase1.log' SKIP 20 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (deptno INTEGER EXTERNAL(6), dname CHAR(20), loc CHAR(25))) LOCATION ('ulcase1 [...]
<sql-case id="create_table_external_oracle_datapump" value="CREATE TABLE dept_xt (dept_no INT, dept_name CHAR(20), location CHAR(20)) ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY load_dir ACCESS PARAMETERS (LOGFILE log_dir:deptxt) LOCATION ('dept_dmp'))" db-types="Oracle"/>
+ <sql-case id="create_table_external_with_preprocessor_and_ldrtrim" value="CREATE TABLE sales_transactions_ext
+(PROD_ID NUMBER,
+ CUST_ID NUMBER,
+ TIME_ID DATE,
+ CHANNEL_ID CHAR,
+ PROMO_ID NUMBER,
+ QUANTITY_SOLD NUMBER,
+ AMOUNT_SOLD NUMBER(10,2),
+ UNIT_COST NUMBER(10,2),
+ UNIT_PRICE NUMBER(10,2))
+ORGANIZATION external
+(TYPE oracle_loader
+ DEFAULT DIRECTORY data_file_dir
+ ACCESS PARAMETERS
+ (RECORDS DELIMITED BY NEWLINE
+ CHARACTERSET AL32UTF8
+ PREPROCESSOR exec_file_dir:'zcat'
+ BADFILE log_file_dir:'sh_sales.bad_xt'
+ LOGFILE log_file_dir:'sh_sales.log_xt'
+ FIELDS TERMINATED BY "|" LDRTRIM
+ ( PROD_ID,
+ CUST_ID,
+ TIME_ID,
+ CHANNEL_ID,
+ PROMO_ID,
+ QUANTITY_SOLD,
+ AMOUNT_SOLD,
+ UNIT_COST,
+ UNIT_PRICE))
+ location ('sh_sales.dat.gz')
+)REJECT LIMIT UNLIMITED" db-types="Oracle" />
+ <sql-case id="create_table_external_with_field_date_format" value="CREATE TABLE sales_transactions_ext
+(PROD_ID NUMBER, CUST_ID NUMBER,
+ TIME_ID DATE, CHANNEL_ID NUMBER,
+ PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,
+ AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),
+ UNIT_PRICE NUMBER(10,2))
+ORGANIZATION external (TYPE oracle_loader
+ DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS
+ (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
+ PREPROCESSOR EXECDIR:'zcat'
+ BADFILE log_file_dir:'sh_sales.bad_xt'
+ LOGFILE log_file_dir:'sh_sales.log_xt'
+ FIELDS TERMINATED BY "|" LDRTRIM
+ ( PROD_ID, CUST_ID,
+ TIME_ID DATE(10) "YYYY-MM-DD",
+ CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
+ UNIT_COST, UNIT_PRICE))
+ location ('sh_sales.gz')
+)REJECT LIMIT UNLIMITED" db-types="Oracle" />
<sql-case id="create_table_organization_external" value="CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir LOCATION ('info.dat'));" db-types="Oracle"/>
<sql-case id="create_table_with_fields_terminated_by" value="CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth INT) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (FIELDS TERMINATED BY "|") LOCATION ('info.dat'));" db-types="Oracle"/>
<sql-case id="create_table_with_records_variable_fields_terminated" value="CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (RECORDS VARIABLE 2 FIELDS TERMINATED BY ',' (first_name CHAR(7), last_name CHAR(8), year_of_birth CHAR(4))) LOCATION ('info.dat'));" db-types="Oracle"/>