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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
