This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang 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 7bfc3d49bb7 Fix oracle create table listPartitionDesc and
subpartitionTemplate syntax (#29222)
7bfc3d49bb7 is described below
commit 7bfc3d49bb718fd014e6fc187243fe52ef7393b7
Author: niu niu <[email protected]>
AuthorDate: Tue Nov 28 08:07:14 2023 +0800
Fix oracle create table listPartitionDesc and subpartitionTemplate syntax
(#29222)
---
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 10 ++--
.../src/main/resources/case/ddl/create-table.xml | 56 ++++++++++++++++++++++
.../resources/sql/supported/ddl/create-table.xml | 38 +++++++++++++++
3 files changed, 99 insertions(+), 5 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
index d1dcc2d7cda..12dc0eb8550 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
@@ -1094,13 +1094,12 @@ subpartitionByList
;
subpartitionByHash
- : SUBPARTITION BY HASH columnNames (SUBPARTITIONS NUMBER_ (STORE IN LP_
tablespaceName (COMMA_ tablespaceName)? RP_)? | subpartitionTemplate)?
+ : SUBPARTITION BY HASH columnNames (SUBPARTITIONS INTEGER_ (STORE IN LP_
tablespaceName (COMMA_ tablespaceName)? RP_)? | subpartitionTemplate)?
;
subpartitionTemplate
- : SUBPARTITION TEMPLATE
- (LP_? rangeSubpartitionDesc (COMMA_ rangeSubpartitionDesc)* |
listSubpartitionDesc (COMMA_ listSubpartitionDesc)* | individualHashSubparts
(COMMA_ individualHashSubparts)* RP_?)
- | hashSubpartitionQuantity
+ : SUBPARTITION TEMPLATE (LP_ (rangeSubpartitionDesc (COMMA_
rangeSubpartitionDesc)*
+ | listSubpartitionDesc (COMMA_ listSubpartitionDesc)* |
individualHashSubparts (COMMA_ individualHashSubparts)*) RP_) |
hashSubpartitionQuantity
;
rangeSubpartitionDesc
@@ -1129,7 +1128,8 @@ compositeListPartitions
;
listPartitionDesc
- : PARTITIONSET partitionSetName listValuesClause (TABLESPACE SET
tablespaceSetName)? lobStorageClause? (SUBPARTITIONS STORE IN LP_?
tablespaceSetName (COMMA_ tablespaceSetName)* RP_?)?
+ : PARTITION partitionName? listValuesClause tablePartitionDescription (LP_
(rangeSubpartitionDesc (COMMA_ rangeSubpartitionDesc)*
+ | listSubpartitionDesc (COMMA_ listSubpartitionDesc)* |
individualHashSubparts (COMMA_ individualHashSubparts)*) RP_ |
hashSubpartsByQuantity)?
;
compositeHashPartitions
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 02d5c30eb61..5b4c2b5bba8 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
@@ -2081,4 +2081,60 @@
<column name="inserter" start-index="62" stop-index="69" />
</column-definition>
</create-table>
+
+ <create-table
sql-case-id="create_table_partition_by_list_subpartition_by_hash">
+ <table name="car_rentals" start-index="13" stop-index="23" />
+ <column-definition type="NUMBER" start-index="30" stop-index="50">
+ <column name="id" start-index="30" stop-index="31" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="58" stop-index="85">
+ <column name="customer_id" start-index="58" stop-index="68" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="93" stop-index="134">
+ <column name="confirmation_number" start-index="93"
stop-index="111" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="142" stop-index="156">
+ <column name="car_id" start-index="142" stop-index="147" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="164" stop-index="184">
+ <column name="car_type" start-index="164" stop-index="171" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="192" stop-index="233">
+ <column name="requested_car_type" start-index="192"
stop-index="209" />
+ </column-definition>
+ <column-definition type="DATE" start-index="241" stop-index="270">
+ <column name="reservation_date" start-index="241" stop-index="256"
/>
+ </column-definition>
+ <column-definition type="DATE" start-index="278" stop-index="303">
+ <column name="start_date" start-index="278" stop-index="287" />
+ </column-definition>
+ <column-definition type="DATE" start-index="311" stop-index="323">
+ <column name="end_date" start-index="311" stop-index="318" />
+ </column-definition>
+ </create-table>
+
+ <create-table
sql-case-id="create_table_subpartition_by_range_subpartition_template">
+ <table name="account_balance_history" start-index="13" stop-index="35"
/>
+ <column-definition type="NUMBER" start-index="42" stop-index="59">
+ <column name="id" start-index="42" stop-index="43" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="67" stop-index="96">
+ <column name="account_number" start-index="67" stop-index="80" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="104" stop-index="133">
+ <column name="customer_id" start-index="104" stop-index="114" />
+ </column-definition>
+ <column-definition type="DATE" start-index="141" stop-index="172">
+ <column name="transaction_date" start-index="141" stop-index="156"
/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="180" stop-index="204">
+ <column name="amount_credited" start-index="180" stop-index="194"
/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="212" stop-index="232">
+ <column name="amount_debited" start-index="212" stop-index="225" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="240" stop-index="273">
+ <column name="end_of_day_balance" start-index="240"
stop-index="257" />
+ </column-definition>
+ </create-table>
</sql-parser-test-cases>
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 c2dbb1f21ac..8ebd8c69d22 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
@@ -157,4 +157,42 @@
PARTITION costs_q2_2003 VALUES LESS THAN (TO_DATE('01-JUN-2003',
'DD-MON-YYYY')),
PARTITION costs_recent VALUES LESS THAN (MAXVALUE))" db-types="Oracle" />
<sql-case id="create_table_default_user" value="CREATE TABLE audit_trail
(value1 NUMBER, value2 VARCHAR2(32), inserter VARCHAR2(30) DEFAULT USER)"
db-types="Oracle" />
+ <sql-case id="create_table_partition_by_list_subpartition_by_hash"
value="CREATE TABLE car_rentals
+ (id NUMBER NOT NULL,
+ customer_id NUMBER NOT NULL,
+ confirmation_number VARCHAR2(12) NOT NULL,
+ car_id NUMBER,
+ car_type VARCHAR2(10),
+ requested_car_type VARCHAR2(10) NOT NULL,
+ reservation_date DATE NOT NULL,
+ start_date DATE NOT NULL,
+ end_date DATE,
+ country as (substr(confirmation_number,9,2))
+ ) PARTITION BY LIST (country)
+ SUBPARTITION BY HASH (customer_id)
+ SUBPARTITIONS 16
+ (PARTITION north_america VALUES ('US','CA','MX'),
+ PARTITION south_america VALUES ('BR','AR','PE'),
+ PARTITION europe VALUES ('GB','DE','NL','BE','FR','ES','IT','CH'),
+ PARTITION apac VALUES ('NZ','AU','IN','CN')
+ ) ENABLE ROW MOVEMENT" db-types="Oracle" />
+ <sql-case id="create_table_subpartition_by_range_subpartition_template"
value="CREATE TABLE account_balance_history
+ (id NUMBER NOT NULL,
+ account_number NUMBER NOT NULL,
+ customer_id NUMBER NOT NULL,
+ transaction_date DATE NOT NULL,
+ amount_credited NUMBER,
+ amount_debited NUMBER,
+ end_of_day_balance NUMBER NOT NULL
+ ) PARTITION BY RANGE(transaction_date)
+ INTERVAL (NUMTODSINTERVAL(7,'DAY'))
+ SUBPARTITION BY RANGE(end_of_day_balance)
+ SUBPARTITION TEMPLATE
+ (SUBPARTITION unacceptable VALUES LESS THAN (-1000),
+ SUBPARTITION credit VALUES LESS THAN (0),
+ SUBPARTITION low VALUES LESS THAN (500),
+ SUBPARTITION normal VALUES LESS THAN (5000),
+ SUBPARTITION high VALUES LESS THAN (20000),
+ SUBPARTITION extraordinary VALUES LESS THAN (MAXVALUE)
+ ) (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')))"
db-types="Oracle" />
</sql-cases>
