[GSOC 2023] Enhance SQLNodeConverterEngine to support more MySQL SQL statements

[strongduanmu]

Apache ShardingSphere

Apache ShardingSphere is positioned as a Database Plus, and aims at building a standard layer and ecosystem above heterogeneous databases. It focuses on how to reuse existing databases and their respective upper layer, rather than creating a new database. The goal is to minimize or eliminate the challenges caused by underlying databases fragmentation.

Page: https://shardingsphere.apache.org/
Github: https://github.com/apache/shardingsphere 

Background

The ShardingSphere SQL federation engine provides support for complex SQL statements, and it can well support cross-database join queries, subqueries, aggregation queries and other statements. An important part of SQL federation engine is to convert the SQL statement parsed by ShardingSphere into SqlNode, so that Calcite can be used to implement SQL optimization and federated query.

Task

This issue is to solve the MySQL exception that occurs during SQLNodeConverterEngine conversion. The specific case list is as follows.

• select_char
• select_extract
• select_from_dual
• select_from_with_table
• select_group_by_with_having_and_window
• select_not_between_with_single_table
• select_not_in_with_single_table
• select_substring
• select_trim
• select_weight_string
• select_where_with_bit_expr_with_ampersand
• select_where_with_bit_expr_with_caret
• select_where_with_bit_expr_with_div
• select_where_with_bit_expr_with_minus_interval
• select_where_with_bit_expr_with_mod
• select_where_with_bit_expr_with_mod_sign
• select_where_with_bit_expr_with_plus_interval
• select_where_with_bit_expr_with_signed_left_shift
• select_where_with_bit_expr_with_signed_right_shift
• select_where_with_bit_expr_with_vertical_bar
• select_where_with_boolean_primary_with_comparison_subquery
• select_where_with_boolean_primary_with_is
• select_where_with_boolean_primary_with_is_not
• select_where_with_boolean_primary_with_null_safe
• select_where_with_expr_with_and_sign
• select_where_with_expr_with_is
• select_where_with_expr_with_is_not
• select_where_with_expr_with_not
• select_where_with_expr_with_not_sign
• select_where_with_expr_with_or_sign
• select_where_with_expr_with_xor
• select_where_with_predicate_with_in_subquery
• select_where_with_predicate_with_regexp
• select_where_with_predicate_with_sounds_like
• select_where_with_simple_expr_with_collate
• select_where_with_simple_expr_with_match
• select_where_with_simple_expr_with_not
• select_where_with_simple_expr_with_odbc_escape_syntax
• select_where_with_simple_expr_with_row
• select_where_with_simple_expr_with_tilde
• select_where_with_simple_expr_with_variable
• select_window_function
• select_with_assignment_operator
• select_with_assignment_operator_and_keyword
• select_with_case_expression
• select_with_collate_with_marker
• select_with_date_format_function
• select_with_exists_sub_query_with_project
• select_with_function_name
• select_with_json_value_return_type
• select_with_match_against
• select_with_regexp
• select_with_schema_name_in_column_projection
• select_with_schema_name_in_shorthand_projection
• select_with_spatial_function
• select_with_trim_expr
• select_with_trim_expr_from_expr

You need to compare the difference between actual and expected, and then correct the logic in SQLNodeConverterEngine so that actual can be consistent with expected.

After you make changes, remember to add case to SUPPORTED_SQL_CASE_IDS to ensure it can be tested.
 
Notice, these issues can be a good example.

• https://github.com/apache/shardingsphere/pull/14492

Relevant Skills

1. Master JAVA language
2. Have a basic understanding of Antlr g4 file
3. Be familiar with MySQL and Calcite SqlNode

Targets files

SQLNodeConverterEngineIT - https://github.com/apache/shardingsphere/blob/master/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java 

[kanha-gupta]

@strongduanmu Hi ! I'll take this up for GSOC :)
Any docs or codebase to understand the implementation logic for every query better ?

[kanha-gupta]

@strongduanmu Hey ! I have written to request a proposal review from you for this Task
I would like to know what could be the possible reformations & corrections required
(I have mailed you the proposal)

[strongduanmu]

Hi @kanha-gupta, there are already many candidates who want to claim this task, so you need to complete some basic tasks first, so that the ShardingSphere community can choose suitable participants.

[kanha-gupta]

@strongduanmu Sure ! I am excited to do the basic tasks. Can you share their links ?
Also I've added support for one of these tasks listed for GSOC coding period(in my local environment). are they countable too ?

[kanha-gupta]

@strongduanmu That case test was successful in SQLNodeConverterEngineIT

[kanha-gupta]

The issue is completed.
Thank you for the great mentorship
Here is the link to my final report : https://gist.github.com/kanha-gupta/c62ef45c0e427a682c1e3c2801dd211a
Thank you :)

[strongduanmu]

@kanha-gupta Thank you for your feedback, I will close this issue.