FlyingZC opened a new issue, #27083: URL: https://github.com/apache/shardingsphere/issues/27083
# 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 admin_iot3(i PRIMARY KEY, j, k, l) ORGANIZATION INDEX PARALLEL AS SELECT * FROM hr.jobs; ``` ```sql 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, PARTITION costs_q1_2003 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')), PARTITION costs_q2_2003 VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')), PARTITION costs_recent VALUES LESS THAN (MAXVALUE)); ``` ```sql CREATE TABLE audit_trail ( value1 NUMBER, value2 VARCHAR2(32), inserter VARCHAR2(30) DEFAULT USER); ``` ```sql 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; ``` ```sql 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'))); ``` # 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]
