You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2026/05/14 10:31:04 UTC
(shardingsphere) branch master updated: Support Oracle Create TABLE sql statement parse (#38667)
This is an automated email from the ASF dual-hosted git repository.
strongduanmu 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 b88fdcf349a Support Oracle Create TABLE sql statement parse (#38667)
b88fdcf349a is described below
commit b88fdcf349ad7830799bbef5c33ea8be4f533863
Author: Claire <cl...@163.com>
AuthorDate: Thu May 14 18:30:57 2026 +0800
Support Oracle Create TABLE sql statement parse (#38667)
* good first issue
* update release-notes
* update according review suggestion
---
RELEASE-NOTES.md | 1 +
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 1 +
.../visitor/statement/OracleStatementVisitor.java | 3 +
.../src/main/resources/case/ddl/create-table.xml | 64 +++++++++++++++++++++-
.../resources/sql/supported/ddl/create-table.xml | 6 ++
5 files changed, 74 insertions(+), 1 deletion(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index c4da05de705..9c6818c23e7 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -29,6 +29,7 @@
1. SQL Parser: Support additional MySQL SELECT index hint and MATCH ... AGAINST WITH ROLLUP syntax - [#38233](https://github.com/apache/shardingsphere/pull/38233)
1. SQL Parser: Support MySQL CREATE EVENT and additional CREATE FUNCTION RETURN syntax - [#38237](https://github.com/apache/shardingsphere/pull/38237)
1. SQL Parser: Support MySQL subquery table projection alias visit to SQL statement - [#38320](https://github.com/apache/shardingsphere/pull/38320)
+1. SQL Parser: Support Oracle Create TABLE sql statement parse - [#38667](https://github.com/apache/shardingsphere/pull/38667)
1. SQL Parser: Enhance select window partition by order by, lead placeholder parse - [#38392](https://github.com/apache/shardingsphere/pull/38392)
1. SQL Parser: Support MariaDB cycleClause, groupConcatLimitClause, MEDIAN window function parse - [#38579](https://github.com/apache/shardingsphere/pull/38579)
1. SQL Parser: Support mysql, doris insert & replace rows statement parse - [#38585](https://github.com/apache/shardingsphere/pull/38585)
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 04a9f8c4b76..333dbeb4e15 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
@@ -689,6 +689,7 @@ columnProperty
: objectTypeColProperties
| xmlTypeColProperties
| lobStorageClause
+ | nestedTableColProperties
;
xmlTypeColProperties
diff --git a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
index 06a8a268833..ef07816714b 100644
--- a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
+++ b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/OracleStatementVisitor.java
@@ -1493,6 +1493,9 @@ public abstract class OracleStatementVisitor extends OracleStatementBaseVisitor<
if (null != ctx.specialDatatype()) {
result.setDataTypeName(((KeywordValue) visit(ctx.specialDatatype().dataTypeName())).getValue());
}
+ if (null != ctx.customDataType()) {
+ result.setDataTypeName(ctx.customDataType().getText());
+ }
result.setStartIndex(ctx.start.getStartIndex());
result.setStopIndex(ctx.stop.getStopIndex());
if (null != ctx.dataTypeLength()) {
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 4cfea986a44..b63af445aea 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
@@ -2135,6 +2135,64 @@
<referenced-table name="warehouse_table" start-index="89" stop-index="103" />
</column-definition>
</create-table>
+
+ <create-table sql-case-id="create_table_with_nested_table_store_as" db-types="Oracle">
+ <table name="people_reltab" start-index="13" stop-index="28">
+ <owner name="oe" start-index="13" stop-index="14" />
+ </table>
+ <column-definition type="NUMBER" primary-key="true" start-index="32" stop-index="83">
+ <column name="id" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="86" stop-index="108">
+ <column name="first_name" />
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="111" stop-index="132">
+ <column name="last_name" />
+ </column-definition>
+ <column-definition type="oe.phone_ntabtyp" start-index="135" stop-index="162">
+ <column name="phones_ntab" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_nested_table_mixed_column_properties_xmltype_first" db-types="Oracle">
+ <table name="t_nested_mix" start-index="13" stop-index="24" />
+ <column-definition type="XMLTYPE" start-index="27" stop-index="37">
+ <column name="doc" />
+ </column-definition>
+ <column-definition type="phone_ntabtyp" start-index="40" stop-index="64">
+ <column name="phones_ntab" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_nested_table_mixed_column_properties_nested_first" db-types="Oracle">
+ <table name="t_nested_mix2" start-index="13" stop-index="25" />
+ <column-definition type="XMLTYPE" start-index="28" stop-index="38">
+ <column name="doc" />
+ </column-definition>
+ <column-definition type="phone_ntabtyp" start-index="41" stop-index="65">
+ <column name="phones_ntab" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_nested_table_mixed_column_properties_lob_first" db-types="Oracle">
+ <table name="t_nested_lob_mix" start-index="13" stop-index="28" />
+ <column-definition type="CLOB" start-index="31" stop-index="38">
+ <column name="doc" />
+ </column-definition>
+ <column-definition type="phone_ntabtyp" start-index="41" stop-index="65">
+ <column name="phones_ntab" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_nested_table_mixed_column_properties_nested_first_lob" db-types="Oracle">
+ <table name="t_nested_lob_mix2" start-index="13" stop-index="29" />
+ <column-definition type="CLOB" start-index="32" stop-index="39">
+ <column name="doc" />
+ </column-definition>
+ <column-definition type="phone_ntabtyp" start-index="42" stop-index="66">
+ <column name="phones_ntab" />
+ </column-definition>
+ </create-table>
<create-table sql-case-id="create_table_with_select" db-types="Doris">
<table name="t_order_new" start-index="13" stop-index="23"/>
@@ -2190,7 +2248,11 @@
<create-table sql-case-id="create_table_with_select_without_query" db-types="Oracle">
<table name="t_order_new" start-index="13" stop-index="23"/>
</create-table>
-
+
+ <create-table sql-case-id="create_table_as_select_with_pivot" db-types="Oracle">
+ <table name="pivot_table" start-index="13" stop-index="23"/>
+ </create-table>
+
<create-table sql-case-id="create_table_organization_index_parallel_with_select">
<table name="admin_iot3" start-index="13" stop-index="22" />
<column-definition start-index="24" stop-index="36" primary-key="true">
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 0f5471d6ab6..dccb4ed914b 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
@@ -158,12 +158,18 @@
<sql-case id="create_table_with_partition_less_than" value="CREATE TABLE t_sales (order_id INTEGER NOT NULL, goods_name CHAR(20) NOT NULL, sales_date DATE NOT NULL, sales_volume INTEGER, sales_store CHAR(20), PRIMARY KEY ( order_id )) PARTITION BY RANGE (sales_date) (PARTITION season1 VALUES LESS THAN('2023-04-01 00:00:00'),PARTITION season2 VALUES LESS THAN('2023-07-01 00:00:00'),PARTITION season3 VALUES LESS THAN('2023-10-01 00:00:00'),PARTITION season4 VALUES LESS THAN(MAXVALUE))" [...]
<sql-case id="create_table_with_negative_data_type" value="CREATE TABLE T(COL1 NUMBER, COL2 NUMBER(3), COL3 NUMBER(3,2), COL4 NUMBER(6,-2))" db-types="Oracle" />
<sql-case id="create_table_with_ref_data_type" value="CREATE TABLE location_table (location_number NUMBER, building REF warehouse_typ SCOPE IS warehouse_table);" db-types="Oracle" />
+ <sql-case id="create_table_with_nested_table_store_as" value="CREATE TABLE oe.people_reltab ( id NUMBER(4) CONSTRAINT pk_people_reltab PRIMARY KEY, first_name VARCHAR2(20), last_name VARCHAR2(20), phones_ntab oe.phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab ((PRIMARY KEY (NESTED_TABLE_ID, location)));" db-types="Oracle" />
+ <sql-case id="create_table_with_nested_table_mixed_column_properties_xmltype_first" value="CREATE TABLE t_nested_mix (doc XMLTYPE, phones_ntab phone_ntabtyp) XMLTYPE COLUMN doc STORE AS CLOB NESTED TABLE phones_ntab STORE AS phone_store_ntab;" db-types="Oracle" />
+ <sql-case id="create_table_with_nested_table_mixed_column_properties_nested_first" value="CREATE TABLE t_nested_mix2 (doc XMLTYPE, phones_ntab phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab XMLTYPE COLUMN doc STORE AS CLOB;" db-types="Oracle" />
+ <sql-case id="create_table_with_nested_table_mixed_column_properties_lob_first" value="CREATE TABLE t_nested_lob_mix (doc CLOB, phones_ntab phone_ntabtyp) LOB (doc) STORE AS doc_lob_seg NESTED TABLE phones_ntab STORE AS phone_store_ntab;" db-types="Oracle" />
+ <sql-case id="create_table_with_nested_table_mixed_column_properties_nested_first_lob" value="CREATE TABLE t_nested_lob_mix2 (doc CLOB, phones_ntab phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab LOB (doc) STORE AS doc_lob_seg;" db-types="Oracle" />
<sql-case id="create_table_with_select" value="CREATE TABLE t_order_new AS SELECT * FROM t_order" db-types="Doris"/>
<sql-case id="create_table_with_comment_doris" value="CREATE TABLE t_comment (id INT) ENGINE=olap COMMENT 'doris table comment'" db-types="Doris" />
<sql-case id="create_table_with_unique_constraint_doris" value="CREATE TABLE t_unique (id INT, UNIQUE (id))" db-types="Doris" />
<sql-case id="create_table_with_foreign_key_constraint_doris" value="CREATE TABLE t_fk (id INT, CONSTRAINT fk_order FOREIGN KEY (id) REFERENCES ref_table (id))" db-types="Doris" />
<sql-case id="create_table_with_inline_reference_doris" value="CREATE TABLE t_col_fk (id INT REFERENCES ref_table(id))" db-types="Doris" />
<sql-case id="create_table_with_select_without_query" value="CREATE TABLE t_order_new AS SELECT * FROM t_order" db-types="Oracle"/>
+ <sql-case id="create_table_as_select_with_pivot" value="CREATE TABLE pivot_table AS SELECT * FROM (SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders) PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));" db-types="Oracle"/>
<sql-case id="create_table_organization_index_parallel_with_select" value="CREATE TABLE admin_iot3(i PRIMARY KEY, j, k, l) ORGANIZATION INDEX PARALLEL AS SELECT * FROM hr.jobs" db-types="Oracle" />
<sql-case id="create_table_partition_by_range" value="CREATE TABLE costs_demo (prod_id NUMBER(6), time_id DATE, unit_cost NUMBER(10,2), unit_price NUMBER(10,2))
PARTITION BY RANGE (time_id) (PARTITION costs_old VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS,