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 3f2b1a37b76 Improve the parsing of methods in MySQL (#32678)
3f2b1a37b76 is described below
commit 3f2b1a37b7601646bdc635cc3eb7a2eef02f2c5d
Author: lushaorong <[email protected]>
AuthorDate: Sun Sep 1 09:03:15 2024 +0800
Improve the parsing of methods in MySQL (#32678)
* Improve the parsing of methods in MySQL
* Improve the parsing of methods in Doris
---
.../src/main/antlr4/imports/mysql/BaseRule.g4 | 24 +-
.../resources/case/dml/select-special-function.xml | 303 +++++++++++++++++++++
.../sql/supported/dml/select-special-function.xml | 27 +-
3 files changed, 343 insertions(+), 11 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 555ddf2aa2d..faad6fba9b4 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
@@ -927,7 +927,7 @@ simpleExpr
| EXISTS? subquery
| LBE_ identifier expr RBE_
| identifier (JSON_SEPARATOR | JSON_UNQUOTED_SEPARATOR) string_
- | path (RETURNING dataType)? onEmptyError?
+ | path (RETURNING dataType)? onEmpty? onError?
| matchExpression
| caseExpression
| intervalExpression
@@ -937,8 +937,12 @@ path
: string_
;
-onEmptyError
- : (NULL | ERROR | DEFAULT literals) ON (EMPTY | ERROR)
+onEmpty
+ : (NULL | ERROR | DEFAULT literals) ON EMPTY
+ ;
+
+onError
+ : (NULL | ERROR | DEFAULT literals) ON ERROR
;
columnRef
@@ -977,11 +981,19 @@ jsonTableColumns
jsonTableColumn
: name FOR ORDINALITY
- | name dataType PATH path (NULL | DEFAULT string_ | ERROR) ON (EMPTY |
ERROR)
+ | name dataType PATH path jsonTableColumnOnEmpty? jsonTableColumnOnError?
| name dataType EXISTS PATH string_ path
| NESTED PATH? path COLUMNS
;
+jsonTableColumnOnEmpty
+ : (NULL | DEFAULT string_ | ERROR) ON EMPTY
+ ;
+
+jsonTableColumnOnError
+ : (NULL | DEFAULT string_ | ERROR) ON ERROR
+ ;
+
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
@@ -997,7 +1009,7 @@ aggregationFunctionName
distinct
: DISTINCT
;
-
+
overClause
: OVER (windowSpecification | identifier)
;
@@ -1062,7 +1074,7 @@ windowFunction
| funcName = (FIRST_VALUE | LAST_VALUE) LP_ expr RP_ nullTreatment?
windowingClause
| funcName = NTH_VALUE LP_ expr COMMA_ simpleExpr RP_ (FROM (FIRST |
LAST))? nullTreatment? windowingClause
;
-
+
windowingClause
: OVER (windowName=identifier | windowSpecification)
;
diff --git
a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
index d61745a2516..137f081f7d7 100644
--- a/test/it/parser/src/main/resources/case/dml/select-special-function.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-special-function.xml
@@ -3223,4 +3223,307 @@
</expression-projection>
</projections>
</select>
+
+ <select sql-case-id="select_json_objectagg">
+ <projections start-index="7" stop-index="38">
+ <expression-projection start-index="7" stop-index="38"
text="JSON_OBJECTAGG(attribute, value)">
+ <expr>
+ <function function-name="JSON_OBJECTAGG"
text="JSON_OBJECTAGG(attribute, value)" start-index="7" stop-index="38">
+ <parameter>
+ <column name="attribute" start-index="22"
stop-index="30" />
+ </parameter>
+ <parameter>
+ <column name="value" start-index="33"
stop-index="37" />
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t" start-index="45" stop-index="45" />
+ </from>
+ <group-by>
+ <column-item name="id" start-index="56" stop-index="57" />
+ </group-by>
+ </select>
+
+ <select sql-case-id="select_json_overlaps">
+ <projections start-index="7" stop-index="43">
+ <expression-projection start-index="7" stop-index="43"
text="JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]")">
+ <expr>
+ <function function-name="JSON_OVERLAPS"
text="JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]")"
start-index="7" stop-index="43">
+ <parameter>
+ <literal-expression value="[1,3,5,7]"
start-index="21" stop-index="31"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="[2,5,7]"
start-index="34" stop-index="42"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_pretty">
+ <projections start-index="7" stop-index="24">
+ <expression-projection start-index="7" stop-index="24"
text="JSON_PRETTY('123')">
+ <expr>
+ <function function-name="JSON_PRETTY"
text="JSON_PRETTY('123')" start-index="7" stop-index="24">
+ <parameter>
+ <literal-expression value="123" start-index="19"
stop-index="23"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_quote">
+ <projections start-index="7" stop-index="21">
+ <expression-projection start-index="7" stop-index="21"
text="JSON_QUOTE('a')">
+ <expr>
+ <function function-name="JSON_QUOTE"
text="JSON_QUOTE('a')" start-index="7" stop-index="21">
+ <parameter>
+ <literal-expression value="a" start-index="18"
stop-index="20"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_remove">
+ <projections start-index="7" stop-index="44">
+ <expression-projection start-index="7" stop-index="44"
text="JSON_REMOVE('["a", "b", "d"]', '$[0]')">
+ <expr>
+ <function function-name="JSON_REMOVE"
text="JSON_REMOVE('["a", "b", "d"]', '$[0]')"
start-index="7" stop-index="44">
+ <parameter>
+ <literal-expression value="["a",
"b", "d"]" start-index="19" stop-index="35"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="$[0]" start-index="38"
stop-index="43"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_replace">
+ <projections start-index="7" stop-index="52">
+ <expression-projection start-index="7" stop-index="52"
text="JSON_REPLACE('{ "a": 1, "b": "2"}', '$.a',
10)">
+ <expr>
+ <function function-name="JSON_REPLACE"
text="JSON_REPLACE('{ "a": 1, "b": "2"}', '$.a',
10)" start-index="7" stop-index="52">
+ <parameter>
+ <literal-expression value="{ "a": 1,
"b": "2"}" start-index="20" stop-index="40"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="$.a" start-index="43"
stop-index="47"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="10" start-index="50"
stop-index="51"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_schema_valid">
+ <projections start-index="7" stop-index="98">
+ <expression-projection start-index="7" stop-index="98"
text="JSON_SCHEMA_VALID('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')">
+ <expr>
+ <function function-name="JSON_SCHEMA_VALID"
text="JSON_SCHEMA_VALID('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')"
start-index="7" stop-index="98">
+ <parameter>
+ <literal-expression
value="{"type":"object","required":["name","value"]}"
start-index="25" stop-index="71"/>
+ </parameter>
+ <parameter>
+ <literal-expression
value="{"name":"a","value":10}" start-index="73"
stop-index="97"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_schema_validation_report">
+ <projections start-index="7" stop-index="110">
+ <expression-projection start-index="7" stop-index="110"
text="JSON_SCHEMA_VALIDATION_REPORT('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')">
+ <expr>
+ <function function-name="JSON_SCHEMA_VALIDATION_REPORT"
text="JSON_SCHEMA_VALIDATION_REPORT('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')"
start-index="7" stop-index="110">
+ <parameter>
+ <literal-expression
value="{"type":"object","required":["name","value"]}"
start-index="37" stop-index="83"/>
+ </parameter>
+ <parameter>
+ <literal-expression
value="{"name":"a","value":10}" start-index="85"
stop-index="109"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_search">
+ <projections start-index="7" stop-index="54">
+ <expression-projection start-index="7" stop-index="54"
text="JSON_SEARCH('["abc",{"x":"abc"}]', 'one',
'abc')">
+ <expr>
+ <function function-name="JSON_SEARCH"
text="JSON_SEARCH('["abc",{"x":"abc"}]', 'one',
'abc')" start-index="7" stop-index="54">
+ <parameter>
+ <literal-expression
value="["abc",{"x":"abc"}]" start-index="19"
stop-index="39"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="one" start-index="42"
stop-index="46"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="abc" start-index="49"
stop-index="53"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_set">
+ <projections start-index="7" stop-index="44">
+ <expression-projection start-index="7" stop-index="44"
text="JSON_SET('{"a":1,"b":[2,3]}','$.c',10)">
+ <expr>
+ <function start-index="7" stop-index="44"
function-name="JSON_SET"
text="JSON_SET('{"a":1,"b":[2,3]}','$.c',10)">
+ <parameter>
+ <literal-expression
value="{"a":1,"b":[2,3]}" start-index="16" stop-index="34"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="$.c" start-index="36"
stop-index="40"/>
+ </parameter>
+ <parameter>
+ <literal-expression value="10" start-index="42"
stop-index="43"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_storage_free">
+ <projections start-index="7" stop-index="33">
+ <expression-projection start-index="7" stop-index="33"
text="JSON_STORAGE_FREE(json_col)">
+ <expr>
+ <function start-index="7" stop-index="33"
function-name="JSON_STORAGE_FREE" text="JSON_STORAGE_FREE(json_col)">
+ <parameter>
+ <column name="json_col" start-index="25"
stop-index="32"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t" start-index="40" stop-index="40" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_json_storage_size">
+ <projections start-index="7" stop-index="33">
+ <expression-projection start-index="7" stop-index="33"
text="JSON_STORAGE_SIZE(json_col)">
+ <expr>
+ <function start-index="7" stop-index="33"
function-name="JSON_STORAGE_SIZE" text="JSON_STORAGE_SIZE(json_col)">
+ <parameter>
+ <column name="json_col" start-index="25"
stop-index="32"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ <from>
+ <simple-table name="t" start-index="40" stop-index="40" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_json_table">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7"/>
+ </projections>
+ <from start-index="14" stop-index="100">
+ <function-table start-index="14" stop-index="100" table-alias="t">
+ <table-function function-name="JSON_TABLE"
text="JSON_TABLE('[{"name": 2}]','$[*]' COLUMNS( name INT PATH
'$.name' error on empty))">
+ <parameter>
+ <literal-expression value="[{"name": 2}]"
start-index="25" stop-index="39" />
+ </parameter>
+ <parameter>
+ <literal-expression value="$[*]" start-index="41"
stop-index="46" />
+ </parameter>
+ <parameter>
+ <literal-expression value="COLUMNS( name INT PATH
'$.name' error on empty)" start-index="48" stop-index="94" />
+ </parameter>
+ </table-function>
+ </function-table>
+ </from>
+ </select>
+
+ <select sql-case-id="select_json_type">
+ <projections start-index="7" stop-index="26">
+ <expression-projection start-index="7" stop-index="26"
text="JSON_TYPE('[1,2,3]')">
+ <expr>
+ <function start-index="7" stop-index="26"
function-name="JSON_TYPE" text="JSON_TYPE('[1,2,3]')">
+ <parameter>
+ <literal-expression value="[1,2,3]"
start-index="17" stop-index="25"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_unquote">
+ <projections start-index="7" stop-index="27">
+ <expression-projection start-index="7" stop-index="27"
text="JSON_UNQUOTE('"abc"')">
+ <expr>
+ <function start-index="7" stop-index="27"
function-name="JSON_UNQUOTE" text="JSON_UNQUOTE('"abc"')">
+ <parameter>
+ <literal-expression value=""abc""
start-index="20" stop-index="26"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_valid">
+ <projections start-index="7" stop-index="28">
+ <expression-projection start-index="7" stop-index="28"
text="JSON_VALID('{"a": 1}')">
+ <expr>
+ <function start-index="7" stop-index="28"
function-name="JSON_VALID" text="JSON_VALID('{"a": 1}')">
+ <parameter>
+ <literal-expression value="{"a": 1}"
start-index="18" stop-index="27"/>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_json_value">
+ <projections start-index="7" stop-index="105">
+ <expression-projection start-index="7" stop-index="105"
text="JSON_VALUE('{"price": "49.95"}', '$.price' RETURNING
DECIMAL(4,1) null on empty default 0 on error)">
+ <expr>
+ <function start-index="7" stop-index="105"
function-name="JSON_VALUE" text="JSON_VALUE('{"price":
"49.95"}', '$.price' RETURNING DECIMAL(4,1) null on empty default 0
on error)">
+ <parameter>
+ <literal-expression value="{"price":
"49.95"}" start-index="18" stop-index="37" />
+ </parameter>
+ <parameter>
+ <list-expression start-index="40" stop-index="104"
>
+ <items>
+ <literal-expression value="'$.price'"
start-index="40" stop-index="48" />
+ </items>
+ <items>
+ <literal-expression value="RETURNING"
start-index="50" stop-index="58" />
+ </items>
+ <items>
+ <data-type value="DECIMAL"
start-index="60" stop-index="71" />
+ </items>
+ </list-expression>
+ </parameter>
+ </function>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
index f6c87df7859..ca523024888 100644
---
a/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
+++
b/test/it/parser/src/main/resources/sql/supported/dml/select-special-function.xml
@@ -180,15 +180,32 @@
<sql-case id="select_roles_graphml" value="SELECT ROLES_GRAPHML()"
db-types="MySQL" />
<sql-case id="select_round" value="SELECT ROUND(1.58)" db-types="MySQL" />
<sql-case id="select_row_count" value="SELECT ROW_COUNT()"
db-types="MySQL" />
- <sql-case id="select_md5" value="SELECT MD5('testing')" db-types="MySQL" />
+ <sql-case id="select_md5" value="SELECT MD5('testing')"
db-types="MySQL,Doris" />
<sql-case id="select_member_of" value="SELECT 'ab' MEMBER OF('[23,
"abc", 17, "ab", 10]')" db-types="MySQL" />
<sql-case id="select_microsecond" value="SELECT
MICROSECOND('12:00:00.123456')" db-types="MySQL" />
<sql-case id="select_mid" value="SELECT MID('foobarbar' from 4)"
db-types="MySQL" />
- <sql-case id="select_minute" value="SELECT MINUTE('2008-02-03 10:05:03')"
db-types="MySQL" />
- <sql-case id="select_mod" value="SELECT MOD(234, 10)" db-types="MySQL" />
- <sql-case id="select_month" value="SELECT MONTH('2008-02-03')"
db-types="MySQL" />
- <sql-case id="select_monthname" value="SELECT MONTHNAME('2008-02-03')"
db-types="MySQL" />
+ <sql-case id="select_minute" value="SELECT MINUTE('2008-02-03 10:05:03')"
db-types="MySQL,Doris" />
+ <sql-case id="select_mod" value="SELECT MOD(234, 10)"
db-types="MySQL,Doris" />
+ <sql-case id="select_month" value="SELECT MONTH('2008-02-03')"
db-types="MySQL,Doris" />
+ <sql-case id="select_monthname" value="SELECT MONTHNAME('2008-02-03')"
db-types="MySQL,Doris" />
<sql-case id="select_multilinestring" value="SELECT
MultiLineString(ST_GeomFromText('LineString(1 1, 2
2)'),ST_GeomFromText('LineString(1 1, 2 2)'))" db-types="MySQL" />
<sql-case id="select_multipoint" value="SELECT
MultiPoint(point(1,1),point(1,1))" db-types="MySQL" />
<sql-case id="select_multipolygon" value="SELECT
MultiPolygon(ST_GeomFromText('Polygon((0 0, 1 0, 1 1, 0 1, 0
0))'),ST_GeomFromText('Polygon((0 0, 1 0, 1 1, 0 1, 0 0))'))" db-types="MySQL"
/>
+ <sql-case id="select_json_objectagg" value="SELECT
JSON_OBJECTAGG(attribute, value) FROM t GROUP BY id" db-types="MySQL" />
+ <sql-case id="select_json_overlaps" value="SELECT
JSON_OVERLAPS("[1,3,5,7]", "[2,5,7]")" db-types="MySQL" />
+ <sql-case id="select_json_pretty" value="SELECT JSON_PRETTY('123')"
db-types="MySQL" />
+ <sql-case id="select_json_quote" value="SELECT JSON_QUOTE('a')"
db-types="MySQL,Doris" />
+ <sql-case id="select_json_remove" value="SELECT
JSON_REMOVE('["a", "b", "d"]', '$[0]')"
db-types="MySQL" />
+ <sql-case id="select_json_replace" value="SELECT JSON_REPLACE('{
"a": 1, "b": "2"}', '$.a', 10)" db-types="MySQL"
/>
+ <sql-case id="select_json_schema_valid" value="SELECT
JSON_SCHEMA_VALID('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')"
db-types="MySQL" />
+ <sql-case id="select_json_schema_validation_report" value="SELECT
JSON_SCHEMA_VALIDATION_REPORT('{"type":"object","required":["name","value"]}','{"name":"a","value":10}')"
db-types="MySQL" />
+ <sql-case id="select_json_search" value="SELECT
JSON_SEARCH('["abc",{"x":"abc"}]', 'one', 'abc')"
db-types="MySQL" />
+ <sql-case id="select_json_set" value="SELECT
JSON_SET('{"a":1,"b":[2,3]}','$.c',10)" db-types="MySQL" />
+ <sql-case id="select_json_storage_free" value="SELECT
JSON_STORAGE_FREE(json_col) FROM t" db-types="MySQL" />
+ <sql-case id="select_json_storage_size" value="SELECT
JSON_STORAGE_SIZE(json_col) FROM t" db-types="MySQL" />
+ <sql-case id="select_json_table" value="SELECT * FROM
JSON_TABLE('[{"name": 2}]','$[*]' COLUMNS( name INT PATH '$.name'
error on empty)) as t" db-types="MySQL" />
+ <sql-case id="select_json_type" value="SELECT JSON_TYPE('[1,2,3]')"
db-types="MySQL" />
+ <sql-case id="select_json_unquote" value="SELECT
JSON_UNQUOTE('"abc"')" db-types="MySQL" />
+ <sql-case id="select_json_valid" value="SELECT JSON_VALID('{"a":
1}')" db-types="MySQL" />
+ <sql-case id="select_json_value" value="SELECT
JSON_VALUE('{"price": "49.95"}', '$.price' RETURNING
DECIMAL(4,1) null on empty default 0 on error)" db-types="MySQL" />
</sql-cases>
