Support more query statements to convert between SQLStatement and SqlNode

[strongduanmu]

This issue is a subtask of #13091.

At present, we have carried out a parameterized unit test for SQLStatement convert in the shardingsphere-optimize-test module, but there are still many scenarios that do not support. We need to support more query statements and support more database dialects.

Considering the importance of this task, I would like to invite @LeeGuoPing to participate in this work.

In order to find out the query statements that are not currently supported, we can delete the SUPPORTED_SQL_CASE_IDS condition, let all scenarios execute the test, and then be able to find our target. Due to the relatively large workload, I suggest starting with MySQL support first.

[LeeGuoPing]

please assign it to me

[strongduanmu]

We can refer this convert logic——https://github.com/ApsaraDB/galaxysql/blob/main/polardbx-optimizer/src/main/java/com/alibaba/polardbx/optimizer/parse/visitor/FastSqlToCalciteNodeVisitor.java.

[strongduanmu]

Ref #19580.

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[kanha-gupta]

@strongduanmu @LeeGuoPing

Hey ! I would like to work on this. While adding support for new Sql queries, are there any Documents or Code available to refer to ? to understand the required logic & implementation purposes. I am currently checking up previous PRs & Would start adding up more ASAP.

[strongduanmu]

Hi @kanha-gupta, SQLNodeConverterEngine can be used as an entry point to understand this issue. At present, the federated query engine relies on SQLNodeConverterEngine to realize the conversion between ShardingSphere SQLStatement and Calcite SqlNode, so as to take advantage of some capabilities of Calcite.

[kanha-gupta]

@strongduanmu Thanks ! So to add support for more SQL queries, is Antlr parsed Generated code is used ?

[strongduanmu]

@kanha-gupta Yes, the responsibility of SQLNodeConverterEngine is to convert the SQLStatement parsed and visited by ANTLR into Calcite SqlNode.

You can refer to the SQLNodeConverterEngineIT class and delete the SUPPORTED_SQL_CASE_IDS parameter, so that you can run all the cases. At this time, there will be a lot of SQL that does not support conversion. These are our goals. This PR is a good example - https://github.com/apache/shardingsphere/pull/13891.

[kanha-gupta]

Okay I'll look into it. Thanks alot for guidance :)

[strongduanmu]

This task is so large that no one can actually complete it, so I will close this issue and replace it with more smaller issues.