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 8c6a42f7600 Fix create table hashSubpartitionQuantity rule (#29252)
8c6a42f7600 is described below
commit 8c6a42f7600e2ff46ad8cd7364aca62ab8f88f80
Author: niu niu <[email protected]>
AuthorDate: Fri Dec 1 17:10:47 2023 +0800
Fix create table hashSubpartitionQuantity rule (#29252)
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 4 -
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 11 +-
.../src/main/resources/case/ddl/create-table.xml | 150 ++++++++++++++++++---
.../resources/sql/supported/ddl/create-table.xml | 89 +++++++++++-
4 files changed, 226 insertions(+), 28 deletions(-)
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index a52a18f899b..5d986a80e44 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -1024,10 +1024,6 @@ matchNone
: 'Default does not match anything'
;
-hashSubpartitionQuantity
- : NUMBER
- ;
-
odciParameters
: STRING_
;
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 12dc0eb8550..54b8ef27824 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
@@ -883,7 +883,7 @@ heapOrgTableClause
;
indexOrgTableClause
- : (mappingTableClause | PCTTHRESHOLD NUMBER_ | prefixCompression)*
indexOrgOverflowClause?
+ : (mappingTableClause | PCTTHRESHOLD INTEGER_ | prefixCompression)*
indexOrgOverflowClause?
;
externalTableClause
@@ -1114,10 +1114,13 @@ individualHashSubparts
: SUBPARTITION subpartitionName? readOnlyClause? indexingClause?
partitioningStorageClause?
;
+hashSubpartitionQuantity
+ : SUBPARTITIONS INTEGER_ (STORE IN LP_ tablespaceName (COMMA_
tablespaceName)* RP_)?
+ ;
+
rangePartitionDesc
- : PARTITION partitionName? rangeValuesClause tablePartitionDescription
- ((LP_? rangeSubpartitionDesc (COMMA_ rangeSubpartitionDesc)* |
listSubpartitionDesc (COMMA_ listSubpartitionDesc)* | individualHashSubparts
(COMMA_ individualHashSubparts)* RP_?)
- | hashSubpartitionQuantity)?
+ : PARTITION partitionName? rangeValuesClause tablePartitionDescription
(LP_ (rangeSubpartitionDesc (COMMA_ rangeSubpartitionDesc)*
+ | listSubpartitionDesc (COMMA_ listSubpartitionDesc)* |
individualHashSubparts (COMMA_ individualHashSubparts)*) RP_ |
hashSubpartitionQuantity)?
;
compositeListPartitions
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 5b4c2b5bba8..5e91333654b 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
@@ -2082,34 +2082,53 @@
</column-definition>
</create-table>
- <create-table
sql-case-id="create_table_partition_by_list_subpartition_by_hash">
+ <create-table
sql-case-id="create_table_partition_by_list_subpartition_by_hash1">
<table name="car_rentals" start-index="13" stop-index="23" />
- <column-definition type="NUMBER" start-index="30" stop-index="50">
+ <column-definition type="NUMBER" start-index="30" stop-index="47">
<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 type="NUMBER" start-index="55" stop-index="82">
+ <column name="customer_id" start-index="55" stop-index="65" />
</column-definition>
- <column-definition type="VARCHAR2" start-index="93" stop-index="134">
- <column name="confirmation_number" start-index="93"
stop-index="111" />
+ <column-definition type="VARCHAR2" start-index="90" stop-index="131">
+ <column name="confirmation_number" start-index="90"
stop-index="108" />
</column-definition>
- <column-definition type="NUMBER" start-index="142" stop-index="156">
- <column name="car_id" start-index="142" stop-index="147" />
+ <column-definition type="NUMBER" start-index="139" stop-index="153">
+ <column name="car_id" start-index="139" stop-index="144" />
</column-definition>
- <column-definition type="VARCHAR2" start-index="164" stop-index="184">
- <column name="car_type" start-index="164" stop-index="171" />
+ <column-definition type="VARCHAR2" start-index="161" stop-index="181">
+ <column name="car_type" start-index="161" stop-index="168" />
</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 type="VARCHAR2" start-index="189" stop-index="230">
+ <column name="requested_car_type" start-index="189"
stop-index="206" />
</column-definition>
- <column-definition type="DATE" start-index="241" stop-index="270">
- <column name="reservation_date" start-index="241" stop-index="256"
/>
+ <column-definition type="DATE" start-index="238" stop-index="267">
+ <column name="reservation_date" start-index="238" stop-index="253"
/>
</column-definition>
- <column-definition type="DATE" start-index="278" stop-index="303">
- <column name="start_date" start-index="278" stop-index="287" />
+ <column-definition type="DATE" start-index="275" stop-index="300">
+ <column name="start_date" start-index="275" stop-index="284" />
</column-definition>
- <column-definition type="DATE" start-index="311" stop-index="323">
- <column name="end_date" start-index="311" stop-index="318" />
+ <column-definition type="DATE" start-index="308" stop-index="320">
+ <column name="end_date" start-index="308" stop-index="315" />
+ </column-definition>
+ </create-table>
+
+ <create-table
sql-case-id="create_table_partition_by_list_subpartition_by_hash2">
+ <table name="credit_card_accounts" start-index="13" stop-index="32" />
+ <column-definition type="NUMBER" start-index="39" stop-index="72">
+ <column name="account_number" start-index="39" stop-index="52" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="79" stop-index="105">
+ <column name="customer_id" start-index="79" stop-index="89" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="112" stop-index="147">
+ <column name="customer_region" start-index="112" stop-index="126"
/>
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="154" stop-index="183">
+ <column name="is_active" start-index="154" stop-index="162" />
+ </column-definition>
+ <column-definition type="DATE" start-index="190" stop-index="214">
+ <column name="date_opened" start-index="190" stop-index="200" />
</column-definition>
</create-table>
@@ -2137,4 +2156,99 @@
<column name="end_of_day_balance" start-index="240"
stop-index="257" />
</column-definition>
</create-table>
+
+ <create-table
sql-case-id="create_table_partition_by_range_subpartition_by_list">
+ <table name="call_detail_records" start-index="13" stop-index="31" />
+ <column-definition type="NUMBER" start-index="38" stop-index="46">
+ <column name="id" start-index="38" stop-index="39" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="55" stop-index="78">
+ <column name="from_number" start-index="55" stop-index="65" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="87" stop-index="108">
+ <column name="to_number" start-index="87" stop-index="95" />
+ </column-definition>
+ <column-definition type="DATE" start-index="117" stop-index="133">
+ <column name="date_of_call" start-index="117" stop-index="128" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="142" stop-index="161">
+ <column name="distance" start-index="142" stop-index="149" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="170" stop-index="197">
+ <column name="call_duration_in_s" start-index="170"
stop-index="187" />
+ </column-definition>
+ </create-table>
+
+ <create-table
sql-case-id="create_table_partition_by_range_subpartition_by_hash">
+ <table name="composite_sales" start-index="13" stop-index="27" />
+ <column-definition type="NUMBER" start-index="35" stop-index="51">
+ <column name="prod_id" start-index="35" stop-index="41" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="60" stop-index="73">
+ <column name="cust_id" start-index="60" stop-index="66" />
+ </column-definition>
+ <column-definition type="DATE" start-index="82" stop-index="93">
+ <column name="time_id" start-index="82" stop-index="88" />
+ </column-definition>
+ <column-definition type="CHAR" start-index="102" stop-index="119">
+ <column name="channel_id" start-index="102" stop-index="111" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="128" stop-index="145">
+ <column name="promo_id" start-index="128" stop-index="135" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="154" stop-index="176">
+ <column name="quantity_sold" start-index="154" stop-index="166" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="185" stop-index="208">
+ <column name="amount_sold" start-index="185" stop-index="195" />
+ </column-definition>
+ </create-table>
+
+ <create-table
sql-case-id="create_table_partition_by_list_subpartition_by_list">
+ <table name="current_inventory" start-index="13" stop-index="29" />
+ <column-definition type="NUMBER" start-index="36" stop-index="54">
+ <column name="warehouse_id" start-index="36" stop-index="47" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="61" stop-index="89">
+ <column name="warehouse_region" start-index="61" stop-index="76" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="96" stop-index="112">
+ <column name="product_id" start-index="96" stop-index="105" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="119" stop-index="147">
+ <column name="product_category" start-index="119" stop-index="134"
/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="154" stop-index="175">
+ <column name="amount_in_stock" start-index="154" stop-index="168"
/>
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="182" stop-index="211">
+ <column name="unit_of_shipping" start-index="182" stop-index="197"
/>
+ </column-definition>
+ <column-definition type="NUMBER" start-index="218" stop-index="241">
+ <column name="products_per_unit" start-index="218"
stop-index="234" />
+ </column-definition>
+ <column-definition type="DATE" start-index="248" stop-index="264">
+ <column name="last_updated" start-index="248" stop-index="259" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_organization_index">
+ <table name="admin_docindex" start-index="13" stop-index="26" />
+ <column-definition type="char" start-index="33" stop-index="46">
+ <column name="token" start-index="33" stop-index="37" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="53" stop-index="65">
+ <column name="doc_id" start-index="53" stop-index="58" />
+ </column-definition>
+ <column-definition type="NUMBER" start-index="72" stop-index="93">
+ <column name="token_frequency" start-index="72" stop-index="86" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="100" stop-index="127">
+ <column name="token_offsets" start-index="100" stop-index="112" />
+ </column-definition>
+ <constraint-definition constraint-name="pk_admin_docindex"
start-index="134" stop-index="189">
+ <primary-key-column name="token" start-index="176"
stop-index="180" />
+ <primary-key-column name="doc_id" start-index="183"
stop-index="188" />
+ </constraint-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 8ebd8c69d22..29baf0104c4 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,8 +157,8 @@
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,
+ <sql-case id="create_table_partition_by_list_subpartition_by_hash1"
value="CREATE TABLE car_rentals
+ (id NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
confirmation_number VARCHAR2(12) NOT NULL,
car_id NUMBER,
@@ -176,6 +176,18 @@
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_partition_by_list_subpartition_by_hash2"
value="CREATE TABLE credit_card_accounts
+ (account_number NUMBER(16) NOT NULL,
+ customer_id NUMBER NOT NULL,
+ customer_region VARCHAR2(2) NOT NULL,
+ is_active VARCHAR2(1) NOT NULL,
+ date_opened DATE NOT NULL)
+ PARTITION BY LIST (customer_region)
+ SUBPARTITION BY HASH (customer_id)
+ SUBPARTITIONS 16
+ (PARTITION emea VALUES ('EU','ME','AF'),
+ PARTITION amer VALUES ('NA','LA'),
+ PARTITION apac VALUES ('SA','AU','NZ','IN','CH')) PARALLEL"
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,
@@ -195,4 +207,77 @@
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-case id="create_table_partition_by_range_subpartition_by_list"
value="CREATE TABLE call_detail_records
+ (id NUMBER,
+ from_number VARCHAR2(20),
+ to_number VARCHAR2(20),
+ date_of_call DATE,
+ distance VARCHAR2(1),
+ call_duration_in_s NUMBER(4)
+ ) PARTITION BY RANGE(date_of_call)
+ INTERVAL (NUMTODSINTERVAL(1,'DAY'))
+ SUBPARTITION BY LIST(distance)
+ SUBPARTITION TEMPLATE
+ (SUBPARTITION local VALUES('L') TABLESPACE tbs1,
+ SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2,
+ SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3,
+ SUBPARTITION international VALUES ('I') TABLESPACE tbs4)(PARTITION p0
VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy'))) PARALLEL"
db-types="Oracle" />
+ <sql-case id="create_table_partition_by_range_subpartition_by_hash"
value="CREATE TABLE composite_sales
+ ( prod_id NUMBER(6),
+ cust_id NUMBER,
+ time_id DATE,
+ channel_id CHAR(1),
+ promo_id NUMBER(6),
+ quantity_sold NUMBER(3),
+ amount_sold NUMBER(10,2)
+ )
+ PARTITION BY RANGE (time_id)
+ SUBPARTITION BY HASH (channel_id)
+ (PARTITION SALES_Q1_1998 VALUES LESS THAN
(TO_DATE('01-APR-1998','DD-MON-YYYY')),
+ PARTITION SALES_Q2_1998 VALUES LESS THAN
(TO_DATE('01-JUL-1998','DD-MON-YYYY')),
+ PARTITION SALES_Q3_1998 VALUES LESS THAN
(TO_DATE('01-OCT-1998','DD-MON-YYYY')),
+ PARTITION SALES_Q4_1998 VALUES LESS THAN
(TO_DATE('01-JAN-1999','DD-MON-YYYY')),
+ PARTITION SALES_Q1_1999 VALUES LESS THAN
(TO_DATE('01-APR-1999','DD-MON-YYYY')),
+ PARTITION SALES_Q2_1999 VALUES LESS THAN
(TO_DATE('01-JUL-1999','DD-MON-YYYY')),
+ PARTITION SALES_Q3_1999 VALUES LESS THAN
(TO_DATE('01-OCT-1999','DD-MON-YYYY')),
+ PARTITION SALES_Q4_1999 VALUES LESS THAN
(TO_DATE('01-JAN-2000','DD-MON-YYYY')),
+ PARTITION SALES_Q1_2000 VALUES LESS THAN
(TO_DATE('01-APR-2000','DD-MON-YYYY')),
+ PARTITION SALES_Q2_2000 VALUES LESS THAN
(TO_DATE('01-JUL-2000','DD-MON-YYYY')) SUBPARTITIONS 8,
+ PARTITION SALES_Q3_2000 VALUES LESS THAN
(TO_DATE('01-OCT-2000','DD-MON-YYYY'))
+ (SUBPARTITION ch_c,
+ SUBPARTITION ch_i,
+ SUBPARTITION ch_p,
+ SUBPARTITION ch_s,
+ SUBPARTITION ch_t),
+ PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) SUBPARTITIONS 4)"
db-types="Oracle" />
+ <sql-case id="create_table_partition_by_list_subpartition_by_list"
value="CREATE TABLE current_inventory
+ (warehouse_id NUMBER,
+ warehouse_region VARCHAR2(2),
+ product_id NUMBER,
+ product_category VARCHAR2(12),
+ amount_in_stock NUMBER,
+ unit_of_shipping VARCHAR2(20),
+ products_per_unit NUMBER,
+ last_updated DATE)
+ PARTITION BY LIST (warehouse_region)
+ SUBPARTITION BY LIST (product_category)
+ SUBPARTITION TEMPLATE (SUBPARTITION perishable VALUES
('DAIRY','PRODUCE','MEAT','BREAD'),
+ SUBPARTITION non_perishable VALUES ('CANNED','PACKAGED'),
+ SUBPARTITION durable VALUES ('TOYS','KITCHENWARE'))
+ (PARTITION p_northwest VALUES ('OR', 'WA'),
+ PARTITION p_southwest VALUES ('AZ', 'UT', 'NM'),
+ PARTITION p_northeast VALUES ('NY', 'VM', 'NJ'),
+ PARTITION p_southeast VALUES ('FL', 'GA'),
+ PARTITION p_northcentral VALUES ('SD', 'WI'),
+ PARTITION p_southcentral VALUES ('OK', 'TX'))" db-types="Oracle" />
+ <sql-case id="create_table_organization_index" value="CREATE TABLE
admin_docindex
+ (token char(20),
+ doc_id NUMBER,
+ token_frequency NUMBER,
+ token_offsets VARCHAR2(2000),
+ CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
+ ORGANIZATION INDEX
+ TABLESPACE admin_tbs
+ PCTTHRESHOLD 20
+ OVERFLOW TABLESPACE admin_tbs2" db-types="Oracle" />
</sql-cases>
