apache / apache/shardingsphere

Support MySQL/Oracle insert statement convert to SqlInsert to improve sql federation support

Open
#27,376 5 comments 0 reactions 1 assignee Claimed by @boyjoy1127 View on GitHub
feature: SQL federation type: enhancement
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

## Background

On the master branch, [SQLNodeConverterEngine](https://github.com/apache/shardingsphere/blob/master/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/SQLNodeConverterEngine.java) already supports the conversion of most SelectStatement statements, which provides a good foundation for sql federation. In order to improve the support of sql federation for INSERT statements and complex statements such as insert select, we need to support the conversion of InsertStatement in SQLNodeConverterEngine.

MySQL insert statement doc - https://dev.mysql.com/doc/refman/8.0/en/insert.html

```
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
SET assignment_list
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ SELECT ...
| TABLE table_name
| VALUES row_constructor_list
}
[ON DUPLICATE KEY UPDATE assignment_list]

value:
{expr | DEFAULT}

value_list:
value [, value] ...

row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]

assignment:
col_name =
value
| [row_alias.]col_name
| [tbl_name.]col_name
| [row_alias.]col_alias

assignment_list:
assignment [, assignment] ...
```

Oracle insert statement doc - https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423

## Task

- [ ] add InsertStatementConverter implement, you can refer - https://github.com/polardb/polardbx-sql/blob/cdd976850bdfc3264dff279198b6b06eb8641fbe/polardbx-optimizer/src/main/java/com/alibaba/polardbx/optimizer/parse/visitor/FastSqlToCalciteNodeVisitor.java#L1001
- [ ] add InsertStatementConverter call logic in SQLNodeConverterEngine
- [ ] add insert statement convert test case in `resources/converter` directory

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.