You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "FlyingZC (via GitHub)" <gi...@apache.org> on 2023/07/13 06:30:39 UTC
[GitHub] [shardingsphere] FlyingZC opened a new issue, #27087: Support parsing Oracle CREATE TABLE sql
FlyingZC opened a new issue, #27087:
URL: https://github.com/apache/shardingsphere/issues/27087
# Background
Hi community,
This issue is for #26878.
ShardingSphere parser engine helps users parse a SQL to get the AST (Abstract Syntax Tree) and visit this tree to get SQLStatement (Java Object). Currently, we are planning to enhance the support for Oracle SQL parsing in ShardingSphere.
More details:
https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/
# Task
This issue is to support more oracle sql parse, as follows:
```sql
CREATE TABLE dept2 AS SELECT * FROM departments;
ALTER TABLE dept2 PARALLEL 2;
CREATE TABLE emp_range_did PARTITION BY RANGE(department_id)
(PARTITION emp_p1 VALUES LESS THAN (150),
PARTITION emp_p5 VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM employees;
ALTER TABLE emp_range_did PARALLEL 2;
EXPLAIN PLAN FOR
SELECT /*+ PQ_DISTRIBUTE(d NONE PARTITION) ORDERED */ e.last_name,
d.department_name
FROM emp_range_did e , dept2 d
WHERE e.department_id = d.department_id ;
```
```sql
CREATE TABLE dept_80
PARALLEL
AS SELECT * FROM employees
WHERE department_id = 80;
```
```sql
CREATE TABLE doc_list_tab (docno NUMBER PRIMARY KEY, doc_ref SYS.DBURIType);
Table created.
-- Insert a DBUri that targets the row with employee_id=177
INSERT INTO doc_list_tab VALUES(1001, (SELECT sys_DburiGen(rowid, employee_id)
FROM employees WHERE employee_id=177));
1 row created.
-- Insert a DBUri that targets the last_name column of table employees
INSERT INTO doc_list_tab VALUES(1002,
(SELECT sys_DburiGen(employee_id, last_name)
FROM employees WHERE employee_id=177));
1 row created.
SELECT * FROM doc_list_tab;
```
```sql
CREATE TABLE dept_80
AS SELECT * FROM employees
WHERE department_id = 80;
```
```sql
CREATE TABLE customers
( id NUMBER
, name VARCHAR2(50)
, email VARCHAR2(100)
, region VARCHAR2(4)
, credit_rating VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (credit_rating)
SUBPARTITION TEMPLATE
( SUBPARTITION poor VALUES ('P')
, SUBPARTITION mediocre VALUES ('C')
, SUBPARTITION good VALUES ('G')
, SUBPARTITION excellent VALUES ('E')
)
(PARTITION americas VALUES ('AMER')
, PARTITION emea VALUES ('EMEA')
, PARTITION apac VALUES ('APAC')
);
```
# Process
1. First confirm that this is a correct oracle sql syntax, if not please ignore;
2. Compare SQL definitions in Oficial SQL Doc and ShardingSphere SQL Doc;
3. If there is any difference in ShardingSphere SQL Doc, please correct them by referring to the Official SQL Doc;
4. Run mvn install the current_file_module;
5. Check whether there are any exceptions. If indeed, please fix them. (Especially xxxVisitor.class);
6. Add new corresponding SQL case in SQL Cases and expected parsed result in Expected Statment XML;
7. Run SQLParserParameterizedTest to make sure no exceptions.
# Relevant Skills
1. Master JAVA language
2. Have a basic understanding of Antlr `g4` file
3. Be familiar with Oracle SQLs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing Oracle CREATE TABLE sql [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #27087:
URL: https://github.com/apache/shardingsphere/issues/27087#issuecomment-5328931513
Hi @FlyingZC, thanks for reporting this. The Oracle SQL parsing listed in this issue is already supported on the current master branch, so I am closing it as completed.
Verified against the latest `master` (to be released in 5.5.4):
- `CREATE TABLE ... AS SELECT`, `CREATE TABLE ... PARALLEL AS SELECT`, and `CREATE TABLE ... PARTITION BY RANGE (...) (...) AS SELECT ...` are all supported by `createRelationalTableClause` / `tableProperties` in `OracleDDLStatement.g4`, with existing cases such as `create_table_with_as_sub_query` and `create_table_parallel_with_as_sub_query`.
- `ALTER TABLE ... PARALLEL 2` is supported by `parallelClause` (see #30228).
- `PARTITION BY LIST ... SUBPARTITION BY LIST ... SUBPARTITION TEMPLATE` is covered by existing cases like `create_table_partition_by_range_subpartition_by_list` (see #28077).
- `EXPLAIN PLAN FOR SELECT` with optimizer hints is covered by the `explain_for_select_*` cases.
- `SYS.DBURIType` column definitions and scalar subqueries inside `INSERT ... VALUES` also parse and convert to `SQLStatement` without errors.
I re-ran every SQL snippet from this issue through the current Oracle parser and statement visitor; all of them parse successfully. Feel free to reopen if anything still fails on a 5.5.4 snapshot.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing Oracle CREATE TABLE sql [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #27087: Support parsing Oracle CREATE TABLE sql
URL: https://github.com/apache/shardingsphere/issues/27087
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support parsing Oracle CREATE TABLE sql [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27087:
URL: https://github.com/apache/shardingsphere/issues/27087#issuecomment-1784213105
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #27087: Support parsing Oracle CREATE TABLE sql
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #27087:
URL: https://github.com/apache/shardingsphere/issues/27087#issuecomment-1676080751
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org