FlyingZC opened a new issue, #27056: URL: https://github.com/apache/shardingsphere/issues/27056
# 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 OR REPLACE FUNCTION f1 ( p1 NUMBER ) RETURN NUMBER PARALLEL_ENABLE IS BEGIN RETURN p1 * 2; END; ``` ```sql CREATE OR REPLACE FUNCTION f1 ( b IN PLS_INTEGER ) RETURN PLS_INTEGER IS BEGIN RETURN CASE WHEN b > 0 THEN 1 WHEN b <= 0 THEN -1 ELSE NULL END; END f1; ``` ```sql CREATE OR REPLACE FUNCTION f1 ( p1 NUMBER ) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN p1 * 2; END; ``` ```sql CREATE OR REPLACE FUNCTION function_for_position_between (col NUMBER, lower_pos NUMBER, upper_pos NUMBER, indexctx IN SYS.ODCIIndexCtx, scanctx IN OUT position_im, scanflg IN NUMBER) RETURN NUMBER AS rid ROWID; storage_tab_name VARCHAR2(65); lower_bound_stmt VARCHAR2(2000); upper_bound_stmt VARCHAR2(2000); col_val_stmt VARCHAR2(2000); lower_bound NUMBER; upper_bound NUMBER; column_value NUMBER; BEGIN IF (indexctx.IndexInfo IS NOT NULL) THEN storage_tab_name := indexctx.IndexInfo.INDEXSCHEMA || '.' || indexctx.IndexInfo.INDEXNAME || '_STORAGE_TAB'; IF (scanctx IS NULL) THEN /* This is the first call. Open a cursor for future calls. First, do some error checking */ IF (lower_pos > upper_pos) THEN RAISE_APPLICATION_ERROR(-20101, 'Upper Position must be greater than or equal to Lower Position'); END IF; IF (lower_pos <= 0) THEN RAISE_APPLICATION_ERROR(-20101, 'Both Positions must be greater than zero'); END IF; /* Obtain the upper and lower value bounds for the range we're interested in. */ upper_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' || storage_tab_name || ') */ DISTINCT ' || 'col_val FROM ' || storage_tab_name || ' ORDER BY ' || 'col_val DESC) WHERE rownum <= ' || lower_pos; EXECUTE IMMEDIATE upper_bound_stmt INTO upper_bound; IF (lower_pos != upper_pos) THEN lower_bound_stmt := 'Select MIN(col_val) FROM (Select /*+ INDEX_DESC(' || storage_tab_name || ') */ DISTINCT ' || 'col_val FROM ' || storage_tab_name || ' WHERE col_val < ' || upper_bound || ' ORDER BY ' || 'col_val DESC) WHERE rownum <= ' || (upper_pos - lower_pos); EXECUTE IMMEDIATE lower_bound_stmt INTO lower_bound; ELSE lower_bound := upper_bound; END IF; IF (lower_bound IS NULL) THEN lower_bound := upper_bound; END IF; /* Store the lower and upper bounds for future function invocations for the positions. */ scanctx := position_im(0, 0, lower_bound, upper_bound); END IF; /* Fetch the column value corresponding to the rowid, and see if it falls within the determined range. */ col_val_stmt := 'Select col_val FROM ' || storage_tab_name || ' WHERE base_rowid = ''' || indexctx.Rid || ''''; EXECUTE IMMEDIATE col_val_stmt INTO column_value; IF (column_value <= scanctx.upper_bound AND column_value >= scanctx.lower_bound AND scanflg = ODCICONST.RegularCall) THEN RETURN 1; ELSE RETURN 0; END IF; ELSE RAISE_APPLICATION_ERROR(-20101, 'A column that has a domain index of' || 'Position indextype must be the first argument'); END IF; END; ``` ```sql CREATE OR REPLACE FUNCTION f (n INTEGER) RETURN INTEGER IS BEGIN IF n = 0 THEN RETURN 1; ELSIF n = 1 THEN RETURN n; END IF; END; ``` # 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]
