FlyingZC opened a new issue, #27031: URL: https://github.com/apache/shardingsphere/issues/27031
# 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 MATERIALIZED VIEW LOG ON employees WITH PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW emp_data PCTFREE 5 PCTUSED 60 TABLESPACE example STORAGE (INITIAL 50K) REFRESH FAST NEXT sysdate + 7 AS SELECT * FROM employees; ``` ```sql CREATE MATERIALIZED VIEW LOG ON inventories WITH (quantity_on_hand); CREATE MATERIALIZED VIEW warranty_orders REFRESH FAST AS SELECT order_id, line_item_id, product_id FROM order_items o WHERE EXISTS (SELECT * FROM inventories i WHERE o.product_id = i.product_id AND i.quantity_on_hand IS NOT NULL) UNION SELECT order_id, line_item_id, product_id FROM order_items WHERE quantity > 5; ``` ```sql CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE("CUST_ID") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE("CUST_ID") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON "SH"."COUNTRIES" WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COUNTRIES" ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID") INCLUDING NEW VALUES; ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD") INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1 REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND (SH.SALES.CUST_ID IN (1012, 1010, 1005)) GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID; CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2 REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT SH.SALES.PROD_ID C1, SH.CUSTOMERS.CUST_ID C2, SH.COUNTRIES.COUNTRY_NAME C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES". "AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.SALES, SH.CUSTOMERS, SH.COUNTRIES WHERE SH.CUSTOMERS.CUST_ID = SH.SALES.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID AND (SH.COUNTRIES.COUNTRY_NAME IN ('USA', 'Canada')) GROUP BY SH.SALES.PROD_ID, SH.CUSTOMERS.CUST_ID, SH.COUNTRIES.COUNTRY_NAME; CREATE MATERIALIZED VIEW SH.CUST_MV REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS (SELECT "CUST_MV$SUB2"."C1" "PROD_ID","CUST_MV$SUB2"."C2" "CUST_ID",SUM("CUST_MV$SUB2"."M3") "CNT",SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2" GROUP BY "CUST_MV$SUB2"."C1","CUST_MV$SUB2"."C2")UNION (SELECT "CUST_MV$SUB1"."C1" "PROD_ID","CUST_MV$SUB1"."C2" "CUST_ID",SUM("CUST_MV$SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" GROUP BY "CUST_MV$SUB1"."C1","CUST_MV$SUB1"."C2"); BEGIN DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ', 'SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs, countries cn WHERE s.cust_id = cs.cust_id AND cs.country_id = cn.country_id AND cn.country_name IN (''USA'',''Canada'') GROUP BY s.prod_id, s.cust_id UNION SELECT s.prod_id, s.cust_id, COUNT(*) cnt, SUM(s.amount_sold) sum_amount FROM sales s, customers cs WHERE s.cust_id = cs.cust_id AND s.cust_id IN (1005,1010,1012) GROUP BY s.prod_id, s.cust_id', '(SELECT "CUST_MV$SUB2"."C3" "PROD_ID","CUST_MV$SUB2"."C2" "CUST_ID", SUM("CUST_MV$SUB2"."M3") "CNT", SUM("CUST_MV$SUB2"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2" GROUP BY "CUST_MV$SUB2"."C3","CUST_MV$SUB2"."C2") UNION (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID", "CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1")',-1553577441) END; ``` ```sql CREATE MATERIALIZED VIEW LOG ON oe.categories_tab_sys WITH OBJECT ID; ``` ```sql CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID; ``` # 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]
