Kanha Gupta

Apache ShardingSphere [GSoC 2023]— Final Report — Support for MySQL Queries

Kanha Gupta's photo
Kanha Gupta
·

3 min read

The following report summarizes the work done by me during Google Summer of Code 2023 with the Apache ShardingSphere

Final report on Gist : click here

About the project :

Apache ShardingSphere acts as a database proxy and provides support for various databases such as Mysql, postgreSQL, open gauss, sql server etc. It parses sql engine before sending the sql queries to databases for further processing. It contains sql parser engine to parse sql given by user & converts it into an AST node through the lexer & parser process & then into a sql statement to extract features which is core input of kernel processor Kerner processor converts AST node to SQL node by using the Federation engine present in its architecture. As federation engine have the responsibility of converting all types of ASTNode to SQLNodes, it needs optimization to successfully provide support for different sql queries for different types of databases to keep the conversion successful so that calcite can be used to implement sql optimization and finally send it to the database for processing. Right now Many of the sql queries don’t have support added yet & therefore if the user tries to use that sql, it throws a OptimizationSQLNodeConvertException.

Therefore This project proposes to add support of 36 SELECT MySQL queries and also optimize grammar wherever needed.

Contributor details

  • Name: Kanha Gupta
  • Github: kanha-gupta
  • Organization: Apache ShardingSphere (part of Apache Software Foundation)
  • Project Title: Enhance SQLNodeConverterEngine to support more MySQL SQL statements
  • Project listing on GSoC website

Coding Period

PRs and Issues related to GSoC project

  • ⊙ Project listing on Github
  • ⊙ Project listing on Jira
  • ✔ #24888: support for SELECT special functions
  • ✔ #25189: Trim functions support
  • ✔ #25228: Support for Extract, BitExpr Mod
  • ✔ #25268: Support for substring, dual, spatial function
  • ✔ #25318: Support for bitExpr with plus & minus interval
  • ✔ #25513: support for predicate with IN subquery
  • ✔ #25564: support for boolean primary SQL
  • ✔ #25650: support for EXPR with Is & Is not
  • ✔ #25594: support for NOT BETWEEN, NOT IN, COMPARISON SUBQUERY
  • ✔ #25684: support for NotSegment & NotConverter
  • ✔ #25773: support for OR sign
  • ✔ #25865: support for MOD, Vertical Bar
  • ✔ #25921: support for TABLE prefix operator
  • ✔ #25953: support for Operators which calcite dont support
  • ✔ #26038: support for WindowSegment
  • ✔ #26246: NotConverter enhancements
  • ✔ #26256: support for MySQL operators
  • ✔ #26330: support for Match expression
  • ✔ #27361: support for CollateExpression

Future scope

ShardingSphere supports query parsing for MySQL, Oracle, OpenGauss, postGreSQL, SQL92 and SQLServer. We can now work on adding support for more unsupported queries of the following Databases, optimizing parsing logic, and improving support of other queries such as UPDATE, DELETE, INSERT, EXPLAIN

Conclusion

I attribute my successful completion of this work to the invaluable guidance and unwavering support I received from my exceptional mentors: Mr. Zhengqiang Duan, Ms. Trista Pan, and Mr. Chuxin Chen. Their mentorship was instrumental in helping me navigate through every aspect of this project. I am profoundly grateful to them and to the ShardingSphere community for providing me this incredible opportunity. I extend my heartfelt thanks to everyone involved.

Thank you.