Support parsing Oracle EXPLAIN PLAN sql

[FlyingZC]

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:

EXPLAIN PLAN FOR
  SELECT t.long_description time,
     p.long_description product,
     cu.long_description customer,
     ch.long_description channel,
     f.sales sales
  FROM time_calendar_view t,
     product_primary_view p,
     customer_shipments_view cu,
     channel_primary_view ch,
     units_cube_view f
  WHERE t.level_name = 'CALENDAR_YEAR'
     AND p.level_name = 'TOTAL'
     AND cu.level_name = 'TOTAL'
     AND ch.level_name = 'TOTAL'
     AND t.dim_key = f.TIME
     AND p.dim_key = f.product
     AND cu.dim_key = f.customer
     AND ch.dim_key = f.channel
  ORDER BY t.end_date;

EXPLAIN PLAN FOR
SELECT  t.calendar_month_desc, SUM(s.amount_sold)
FROM  sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

EXPLAIN PLAN FOR
SELECT SUM(amount_sold)
FROM sales
WHERE TO_CHAR(time_id,'yyyy') = '2000';

EXPLAIN PLAN FOR
SELECT SUM(quantity_sold)
FROM sales
WHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');

EXPLAIN PLAN FOR
  SELECT *
  FROM   sh.customers
  WHERE  cust_city='Los Angeles'
  AND    cust_state_province='CA';

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

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[kanha-gupta]

Hi @FlyingZC is there a way to generate expected statement XML or what we have to do it manually ?

[FlyingZC]

Hi @FlyingZC is there a way to generate expected statement XML or what we have to do it manually ?

Hi, you can refer to the structure of the parsed SQL statement to write the expected Parse XMLs.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.