This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 349197cb387 Support parsing MySQL json table function (#31158)
349197cb387 is described below
commit 349197cb3871ec77ed87178bcfa9c25dfe7b9837
Author: LotusMoon <[email protected]>
AuthorDate: Tue May 7 17:16:27 2024 +0800
Support parsing MySQL json table function (#31158)
---
.../src/main/antlr4/imports/mysql/BaseRule.g4 | 21 +++++++-
.../src/main/antlr4/imports/mysql/DMLStatement.g4 | 6 ++-
.../visitor/statement/MySQLStatementVisitor.java | 23 ++++++++-
.../src/main/resources/case/dml/select-join.xml | 58 ++++++++++++++++++++++
.../resources/sql/supported/dml/select-join.xml | 1 +
5 files changed, 105 insertions(+), 4 deletions(-)
diff --git a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
index b4a7de26713..c69ef011b97 100644
--- a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
+++ b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/BaseRule.g4
@@ -959,16 +959,33 @@ aggregationFunction
;
jsonFunction
- : columnRef (JSON_SEPARATOR | JSON_UNQUOTED_SEPARATOR) path
+ : jsonTableFunction
| jsonFunctionName LP_ (expr? | expr (COMMA_ expr)*) RP_
+ | columnRef (JSON_SEPARATOR | JSON_UNQUOTED_SEPARATOR) path
;
+jsonTableFunction
+ : JSON_TABLE LP_ expr COMMA_ path jsonTableColumns RP_
+ ;
+
+jsonTableColumns
+ : COLUMNS LP_ jsonTableColumn (COMMA_ jsonTableColumn)* RP_
+ ;
+
+jsonTableColumn
+ : name FOR ORDINALITY
+ | name dataType PATH path (NULL | DEFAULT string_ | ERROR) ON (EMPTY |
ERROR)
+ | name dataType EXISTS PATH string_ path
+ | NESTED PATH? path COLUMNS
+ ;
+
+
jsonFunctionName
: JSON_ARRAY | JSON_ARRAY_APPEND | JSON_ARRAY_INSERT | JSON_CONTAINS
| JSON_CONTAINS_PATH | JSON_DEPTH | JSON_EXTRACT | JSON_INSERT | JSON_KEYS
| JSON_LENGTH | JSON_MERGE | JSON_MERGE_PATCH
| JSON_MERGE_PRESERVE | JSON_OBJECT | JSON_OVERLAPS | JSON_PRETTY |
JSON_QUOTE | JSON_REMOVE | JSON_REPLACE
| JSON_SCHEMA_VALID | JSON_SCHEMA_VALIDATION_REPORT | JSON_SEARCH |
JSON_SET | JSON_STORAGE_FREE | JSON_STORAGE_SIZE
- | JSON_TABLE | JSON_TYPE | JSON_UNQUOTE | JSON_VALID | JSON_VALUE | MEMBER
OF
+ | JSON_TYPE | JSON_UNQUOTE | JSON_VALID | JSON_VALUE | MEMBER OF
;
aggregationFunctionName
diff --git
a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
index c85b7aba130..fa784500ddd 100644
--- a/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
+++ b/parser/sql/dialect/mysql/src/main/antlr4/imports/mysql/DMLStatement.g4
@@ -288,7 +288,11 @@ tableReference
;
tableFactor
- : tableName partitionNames? (AS? alias)? indexHintList? | subquery AS?
alias (LP_ columnNames RP_)? | LATERAL subquery AS? alias (LP_ columnNames
RP_)? | LP_ tableReferences RP_
+ : tableName partitionNames? (AS? alias)? indexHintList?
+ | subquery AS? alias (LP_ columnNames RP_)?
+ | expr (AS? alias)?
+ | LATERAL subquery AS? alias (LP_ columnNames RP_)?
+ | LP_ tableReferences RP_
;
partitionNames
diff --git
a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
index b5bd83fd87b..e5bc2e2e403 100644
---
a/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
+++
b/parser/sql/dialect/mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/MySQLStatementVisitor.java
@@ -146,6 +146,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.ViewNam
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WeightStringFunctionContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WhereClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.WithClauseContext;
+import
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JsonTableFunctionContext;
import org.apache.shardingsphere.sql.parser.sql.common.enums.AggregationType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.CombineType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
@@ -215,6 +216,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.Parenthes
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WindowSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.DeleteMultiTableSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.FunctionTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.IndexHintSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.JoinTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
@@ -931,13 +933,24 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
} else if (null != ctx.JSON_SEPARATOR()) {
functionName = ctx.JSON_SEPARATOR().getText();
result = new FunctionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), functionName, getOriginalText(ctx));
- } else {
+ } else if (null != ctx.JSON_UNQUOTED_SEPARATOR()) {
functionName = ctx.JSON_UNQUOTED_SEPARATOR().getText();
result = new FunctionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), functionName, getOriginalText(ctx));
+ } else {
+ result = (FunctionSegment)
visitJsonTableFunction(ctx.jsonTableFunction());
}
return result;
}
+ @Override
+ public final ASTNode visitJsonTableFunction(final JsonTableFunctionContext
ctx) {
+ FunctionSegment result = new
FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(),
ctx.JSON_TABLE().getText(), ctx.getText());
+ result.getParameters().add((ExpressionSegment) visit(ctx.expr()));
+ result.getParameters().add(new
LiteralExpressionSegment(ctx.path().getStart().getStartIndex(),
ctx.path().getStop().getStopIndex(), ctx.path().getText()));
+ result.getParameters().add(new
LiteralExpressionSegment(ctx.jsonTableColumns().getStart().getStartIndex(),
ctx.jsonTableColumns().getStop().getStopIndex(),
ctx.jsonTableColumns().getText()));
+ return result;
+ }
+
private ASTNode createAggregationSegment(final AggregationFunctionContext
ctx, final String aggregationType) {
AggregationType type =
AggregationType.valueOf(aggregationType.toUpperCase());
if (null != ctx.distinct()) {
@@ -1846,6 +1859,14 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
}
return result;
}
+ if (null != ctx.expr()) {
+ ExpressionSegment exprSegment = (ExpressionSegment)
visit(ctx.expr());
+ FunctionTableSegment result = new
FunctionTableSegment(exprSegment.getStartIndex(), exprSegment.getStopIndex(),
exprSegment);
+ if (null != ctx.alias()) {
+ result.setAlias((AliasSegment) visit(ctx.alias()));
+ }
+ return result;
+ }
return visit(ctx.tableReferences());
}
diff --git a/test/it/parser/src/main/resources/case/dml/select-join.xml
b/test/it/parser/src/main/resources/case/dml/select-join.xml
index 5b36a96a053..8be0db91320 100644
--- a/test/it/parser/src/main/resources/case/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-join.xml
@@ -3291,4 +3291,62 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_from_join_with_json_table">
+ <projections start-index="7" stop-index="37">
+ <column-projection name="c1" start-index="7" stop-index="8"/>
+ <column-projection name="c2" start-index="11" stop-index="12"/>
+ <expression-projection text="JSON_EXTRACT(c3, '$.*')"
start-index="15" stop-index="37">
+ <expr>
+ <function function-name="JSON_EXTRACT" start-index="15"
stop-index="37" text="JSON_EXTRACT(c3, '$.*')">
+ <parameter>
+ <column name="c3" start-index="28"
stop-index="29"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="$.*" start-index="32"
stop-index="36"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from start-index="44" stop-index="252">
+ <join-table join-type="INNER">
+ <left>
+ <simple-table name="t1" start-index="44" stop-index="50"
alias="m"/>
+ </left>
+ <right>
+ <function-table start-index="57" stop-index="230"
table-alias="tt">
+ <table-function
text="JSON_TABLE(m.c3,'$.*'COLUMNS(atVARCHAR(10)PATH'$.a'DEFAULT'1'ONEMPTY,btVARCHAR(10)PATH'$.b'DEFAULT'2'ONEMPTY,ctVARCHAR(10)PATH'$.c'DEFAULT'3'ONEMPTY))"
function-name="JSON_TABLE">
+ <parameter>
+ <column name="c3" start-index="68"
stop-index="71">
+ <owner name="m" start-index="68"
stop-index="68"/>
+ </column>
+ </parameter>
+ <parameter>
+ <literal-expression value="'$.*'"
start-index="74" stop-index="78"/>
+ </parameter>
+ <parameter>
+ <literal-expression
value="COLUMNS(atVARCHAR(10)PATH'$.a'DEFAULT'1'ONEMPTY,btVARCHAR(10)PATH'$.b'DEFAULT'2'ONEMPTY,ctVARCHAR(10)PATH'$.c'DEFAULT'3'ONEMPTY)"
start-index="80" stop-index="229"/>
+ </parameter>
+ </table-function>
+ </function-table>
+ </right>
+ <on-condition>
+ <binary-operation-expression start-index="241"
stop-index="252">
+ <left>
+ <column name="c1" start-index="241"
stop-index="244">
+ <owner name="m" start-index="241"
stop-index="241"/>
+ </column>
+ </left>
+ <operator>></operator>
+ <right>
+ <column name="at" start-index="248"
stop-index="252">
+ <owner name="tt" start-index="248"
stop-index="249"/>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </on-condition>
+ </join-table>
+ </from>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
index 48937483c45..bb677c20b09 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-join.xml
@@ -60,4 +60,5 @@
<sql-case id="select_cross_join_sys_log_info_with_count" value="SELECT
[name], COUNT(l.database_id) AS 'vlf_count' FROM sys.databases AS s CROSS APPLY
sys.dm_db_log_info(s.database_id) AS l GROUP BY [name] HAVING
COUNT(l.database_id) > 100" db-types="SQLServer"/>
<sql-case id="select_from_sys_columns_inner_join_sys_types" value="SELECT
OBJECT_NAME(object_id) AS object_name,c.name AS
column_name,SCHEMA_NAME(t.schema_id) AS schema_name,TYPE_NAME(c.user_type_id)
AS user_type_name,c.max_length,c.precision,c.scale,c.is_nullable,c.is_computed
FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id =
t.user_type_id WHERE c.user_type_id =
TYPE_ID('<schema_name.data_type_name>')" db-types="SQLServer"/>
<sql-case id="select_cross_join_sys_dm_exec_requests" value="SELECT
session_id as SPID, command, a.text AS Query, start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as
estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP
DATABASE','RESTORE DATABASE')" db-types="SQLServer"/>
+ <sql-case id="select_from_join_with_json_table" value="SELECT c1, c2,
JSON_EXTRACT(c3, '$.*') FROM t1 AS m JOIN JSON_TABLE(m.c3, '$.*' COLUMNS(at
VARCHAR(10) PATH '$.a' DEFAULT '1' ON EMPTY, bt VARCHAR(10) PATH '$.b'DEFAULT
'2' ON EMPTY, ct VARCHAR(10) PATH '$.c' DEFAULT '3' ON EMPTY)) AS tt ON m.c1
> tt.at;" db-types="MySQL"/>
</sql-cases>
