You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/09/01 10:13:16 UTC
[shardingsphere] branch master updated: add json function support to mysql parser (#20709)
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 2919c2f909b add json function support to mysql parser (#20709)
2919c2f909b is described below
commit 2919c2f909b8f3e7bfb57020ebb5605bbd21a882
Author: tianhao960 <ti...@users.noreply.github.com>
AuthorDate: Thu Sep 1 18:13:07 2022 +0800
add json function support to mysql parser (#20709)
* add json function support to mysql parser #1
* add visitor for json function, add unit test #2
---
.../src/main/antlr4/imports/mysql/BaseRule.g4 | 17 +++-
.../src/main/antlr4/imports/mysql/MySQLKeyword.g4 | 112 +++++++++++++++++++++
.../statement/impl/MySQLStatementSQLVisitor.java | 21 +++-
.../main/resources/case/dml/select-expression.xml | 18 +++-
.../sql/supported/dml/select-expression.xml | 1 +
5 files changed, 164 insertions(+), 5 deletions(-)
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/BaseRule.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/BaseRule.g4
index 472c393ac78..d8c9d8cc685 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/BaseRule.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/BaseRule.g4
@@ -907,13 +907,26 @@ columnRefList
;
functionCall
- : aggregationFunction | specialFunction | regularFunction
+ : aggregationFunction | specialFunction | regularFunction | jsonFunction
;
aggregationFunction
: aggregationFunctionName LP_ distinct? (expr (COMMA_ expr)* | ASTERISK_)? collateClause? RP_ overClause?
;
-
+
+jsonFunction
+ : columnRef (JSON_SEPARATOR | JSON_UNQUOTED_SEPARATOR) path
+ | jsonFunctionName LP_ (expr? | expr (COMMA_ expr)*) RP_
+ ;
+
+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
+ ;
+
aggregationFunctionName
: MAX | MIN | SUM | COUNT | AVG | BIT_XOR
;
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/MySQLKeyword.g4 b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/MySQLKeyword.g4
index 9085b257d16..32fe5e3deb4 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/MySQLKeyword.g4
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/MySQLKeyword.g4
@@ -2846,3 +2846,115 @@ YEAR_MONTH
ZEROFILL
: Z E R O F I L L
;
+
+JSON_ARRAY
+ : J S O N UL_ A R R A Y
+ ;
+
+JSON_ARRAY_APPEND
+ : J S O N UL_ A R R A Y UL_ A P P E N D
+ ;
+
+JSON_ARRAY_INSERT
+ : J S O N UL_ A R R A Y UL_ I N S E R T
+ ;
+
+JSON_CONTAINS
+ : J S O N UL_ C O N T A I N S
+ ;
+
+JSON_CONTAINS_PATH
+ : J S O N UL_ C O N T A I N S UL_ P A T H
+ ;
+JSON_DEPTH
+ : J S O N UL_ D E P T H
+ ;
+
+JSON_EXTRACT
+ : J S O N UL_ E X T R A C T
+ ;
+
+JSON_INSERT
+ : J S O N UL_ I N S E R T
+ ;
+
+JSON_KEYS
+ : J S O N UL_ K E Y S
+ ;
+
+JSON_LENGTH
+ : J S O N UL_ L E N G T H
+ ;
+
+JSON_MERGE
+ : J S O N UL_ M E R G E
+ ;
+
+JSON_MERGE_PATCH
+ : J S O N UL_ M E R G E UL_ P A T C H
+ ;
+
+JSON_MERGE_PRESERVE
+ : J S O N UL_ M E R G E UL_ P R E S E R V E
+ ;
+
+JSON_OBJECT
+ : J S O N UL_ O B J E C T
+ ;
+
+JSON_OVERLAPS
+ : J S O N UL_ O V E R L A P S
+ ;
+
+JSON_PRETTY
+ : J S O N UL_ P R E T T Y
+ ;
+
+JSON_QUOTE
+ : J S O N UL_ Q U O T E
+ ;
+
+JSON_REMOVE
+ : J S O N UL_ R E M O V E
+ ;
+
+JSON_REPLACE
+ : J S O N UL_ R E P L A C E
+ ;
+
+JSON_SCHEMA_VALID
+ : J S O N UL_ S C H E M A UL_ V A L I D
+ ;
+
+JSON_SCHEMA_VALIDATION_REPORT
+ : J S O N UL_ S C H E M A UL_ V A L I D A T I O N UL_ R E P O R T
+ ;
+
+JSON_SEARCH
+ : J S O N UL_ S E A R C H
+ ;
+
+JSON_SET
+ : J S O N UL_ S E T
+ ;
+
+JSON_STORAGE_FREE
+ : J S O N UL_ S T O R A G E UL_ F R E E
+ ;
+
+JSON_STORAGE_SIZE
+ : J S O N UL_ S T O R A G E UL_ S I Z E
+ ;
+
+JSON_TYPE
+ : J S O N UL_ T Y P E
+ ;
+
+JSON_UNQUOTE
+ : J S O N UL_ U N Q U O T E
+ ;
+
+JSON_VALID
+ : J S O N UL_ V A L I D
+ ;
+
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
index bd0ee26535e..6b36118b0eb 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
@@ -71,6 +71,8 @@ import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.InsertV
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.IntervalExpressionContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JoinSpecificationContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JoinedTableContext;
+import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JsonFunctionContext;
+import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.JsonFunctionNameContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.LimitClauseContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.LimitOffsetContext;
import org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser.LimitRowCountContext;
@@ -760,7 +762,10 @@ public abstract class MySQLStatementSQLVisitor extends MySQLStatementBaseVisitor
if (null != ctx.regularFunction()) {
return visit(ctx.regularFunction());
}
- throw new IllegalStateException("FunctionCallContext must have aggregationFunction, regularFunction or specialFunction.");
+ if (null != ctx.jsonFunction()) {
+ return visit(ctx.jsonFunction());
+ }
+ throw new IllegalStateException("FunctionCallContext must have aggregationFunction, regularFunction, specialFunction or jsonFunction.");
}
@Override
@@ -771,6 +776,20 @@ public abstract class MySQLStatementSQLVisitor extends MySQLStatementBaseVisitor
: new ExpressionProjectionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), getOriginalText(ctx));
}
+ @Override
+ public final ASTNode visitJsonFunction(final JsonFunctionContext ctx) {
+ JsonFunctionNameContext functionNameContext = ctx.jsonFunctionName();
+ String functionName;
+ if (null != functionNameContext) {
+ functionName = functionNameContext.getText();
+ } else if (null != ctx.JSON_SEPARATOR()) {
+ functionName = ctx.JSON_SEPARATOR().getText();
+ } else {
+ functionName = ctx.JSON_UNQUOTED_SEPARATOR().getText();
+ }
+ return new FunctionSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), functionName, getOriginalText(ctx));
+ }
+
private ASTNode createAggregationSegment(final AggregationFunctionContext ctx, final String aggregationType) {
AggregationType type = AggregationType.valueOf(aggregationType.toUpperCase());
String innerExpression = ctx.start.getInputStream().getText(new Interval(ctx.LP_().getSymbol().getStartIndex(), ctx.stop.getStopIndex()));
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-expression.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-expression.xml
index 7f746d69cb2..fe1bb74733d 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-expression.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-expression.xml
@@ -1627,7 +1627,7 @@
</projections>
<where start-index="22" stop-index="44">
<expr>
- <common-expression text="order_id ->"$[1]"" start-index="28" stop-index="44"/>
+ <function function-name="->" text="order_id ->"$[1]"" start-index="28" stop-index="44"></function>
</expr>
</where>
</select>
@@ -1641,7 +1641,21 @@
</projections>
<where start-index="22" stop-index="46">
<expr>
- <common-expression text="order_id ->> "$[1]"" start-index="28" stop-index="46"/>
+ <function function-name="->>" text="order_id ->> "$[1]"" start-index="28" stop-index="46"></function>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_where_with_simple_expr_with_json_contains">
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20"/>
+ </from>
+ <projections distinct-row="false" start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7"/>
+ </projections>
+ <where start-index="22" stop-index="76">
+ <expr>
+ <function function-name="JSON_CONTAINS" text="JSON_CONTAINS(order_msg -> '$[*].code', 'x', '$')" start-index="28" stop-index="76"></function>
</expr>
</where>
</select>
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-expression.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-expression.xml
index c6b3f2ef68f..83b8486391f 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-expression.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-expression.xml
@@ -75,6 +75,7 @@
<sql-case id="select_where_with_simple_expr_with_odbc_escape_syntax" value="SELECT * FROM t_order WHERE {ts ?}" db-types="MySQL" />
<sql-case id="select_where_with_simple_expr_with_json_extract_sign" value="SELECT * FROM t_order WHERE order_id ->"$[1]"" db-types="MySQL" />
<sql-case id="select_where_with_simple_expr_with_json_unquote_extract_sign" value="SELECT * FROM t_order WHERE order_id ->> "$[1]"" db-types="MySQL" />
+ <sql-case id="select_where_with_simple_expr_with_json_contains" value="SELECT * FROM t_order WHERE JSON_CONTAINS(order_msg -> '$[*].code', 'x', '$') " db-types="MySQL" />
<sql-case id="select_where_with_simple_expr_with_match" value="SELECT * FROM t_order WHERE MATCH (order_id) AGAINST (? IN NATURAL LANGUAGE MODE)" db-types="MySQL" />
<sql-case id="select_where_with_simple_expr_with_case" value="SELECT * FROM t_order WHERE CASE WHEN order_id > ? THEN ? ELSE ? END" db-types="MySQL" />
<sql-case id="select_where_with_expr_with_not_with_order_by" value="SELECT last_name, job_id, salary, department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30) ORDER BY last_name" db-types="Oracle" />