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/11/13 01:04:15 UTC
[shardingsphere] branch master updated: Update Oracle DML SLELECT regression functions statement parse (#22117)
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 cefe3c2c957 Update Oracle DML SLELECT regression functions statement parse (#22117)
cefe3c2c957 is described below
commit cefe3c2c957395b7bbfdcfe47a53da1ffbff04e7
Author: Zichao <57...@users.noreply.github.com>
AuthorDate: Sun Nov 13 14:04:00 2022 +1300
Update Oracle DML SLELECT regression functions statement parse (#22117)
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 1 +
.../src/main/antlr4/imports/oracle/Keyword.g4 | 38 +++++++++++++++++++-
.../src/main/antlr4/imports/oracle/Number.g4 | 29 ++++++++++++++++
test/parser/src/main/resources/case/dml/select.xml | 40 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
5 files changed, 108 insertions(+), 1 deletion(-)
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index ff988708393..2c0b78d8200 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -516,6 +516,7 @@ aggregationFunction
aggregationFunctionName
: MAX | MIN | SUM | COUNT | AVG | GROUPING | LISTAGG | PERCENT_RANK | PERCENTILE_CONT | PERCENTILE_DISC | CUME_DIST | RANK
+ | REGR_SLOPE | REGR_INTERCEPT | REGR_COUNT | REGR_R2 | REGR_AVGX | REGR_AVGY | REGR_SXX | REGR_SYY | REGR_SXY
;
listaggOverflowClause
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
index 66ac288266e..25fe9765600 100644
--- a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Keyword.g4
@@ -17,7 +17,7 @@
lexer grammar Keyword;
-import Alphabet;
+import Alphabet, Number;
WS
: [ \t\r\n] + ->skip
@@ -603,3 +603,39 @@ RANK
ROWID
: R O W I D
;
+
+REGR_SLOPE
+ : R E G R UL_ S L O P E
+ ;
+
+REGR_INTERCEPT
+ : R E G R UL_ I N T E R C E P T
+ ;
+
+REGR_COUNT
+ : R E G R UL_ C O U N T
+ ;
+
+REGR_R2
+ : R E G R UL_ R TWO_
+ ;
+
+REGR_AVGX
+ : R E G R UL_ A V G X
+ ;
+
+REGR_AVGY
+ : R E G R UL_ A V G Y
+ ;
+
+REGR_SXX
+ : R E G R UL_ S X X
+ ;
+
+REGR_SYY
+ : R E G R UL_ S Y Y
+ ;
+
+REGR_SXY
+ : R E G R UL_ S X Y
+ ;
diff --git a/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Number.g4 b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Number.g4
new file mode 100644
index 00000000000..5d2af80bb8d
--- /dev/null
+++ b/sql-parser/dialect/oracle/src/main/antlr4/imports/oracle/Number.g4
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+lexer grammar Number;
+
+fragment ZERO_: [0];
+fragment ONE_: [1];
+fragment TWO_: [2];
+fragment THREE_: [3];
+fragment FOUR_: [4];
+fragment FIVE_: [5];
+fragment SIX_: [6];
+fragment SEVEN_: [7];
+fragment EIGHT_: [8];
+fragment NINE_: [9];
diff --git a/test/parser/src/main/resources/case/dml/select.xml b/test/parser/src/main/resources/case/dml/select.xml
index 89a382352ce..5607b691d7f 100644
--- a/test/parser/src/main/resources/case/dml/select.xml
+++ b/test/parser/src/main/resources/case/dml/select.xml
@@ -4573,4 +4573,44 @@
<column-item name="ROWID" start-index="76" stop-index="80" />
</order-by>
</select>
+
+ <select sql-case-id="select_linear_regression_function">
+ <projections start-index="7" stop-index="465">
+ <column-projection name="job_id" start-index="7" stop-index="12" />
+ <column-projection name="employee_id" start-index="15" stop-index="28" alias="ID" />
+ <column-projection name="salary" start-index="31" stop-index="36" />
+ <expression-projection text="REGR_SLOPE(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)" start-index="39" stop-index="108" alias="slope" />
+ <expression-projection text="REGR_INTERCEPT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)" start-index="111" stop-index="185" alias="intcpt" />
+ <expression-projection text="REGR_R2(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)" start-index="188" stop-index="253" alias="rsqr" />
+ <expression-projection text="REGR_COUNT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)" start-index="256" stop-index="325" alias="count" />
+ <expression-projection text="REGR_AVGX(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)" start-index="328" stop-index="395" alias="avgx"/>
+ <expression-projection text="REGR_AVGY(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id)" start-index="398" stop-index="465" alias="avgy" />
+ </projections>
+ <from>
+ <simple-table name="employees" start-index="472" stop-index="480" />
+ </from>
+ <where start-index="482" stop-index="512">
+ <expr>
+ <in-expression start-index="488" stop-index="512">
+ <left>
+ <column name="department_id" start-index="488" stop-index="500" />
+ </left>
+ <right>
+ <list-expression start-index="505" stop-index="512">
+ <items>
+ <literal-expression value="50" start-index="506" stop-index="507" />
+ </items>
+ <items>
+ <literal-expression value="80" start-index="510" stop-index="511" />
+ </items>
+ </list-expression>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="job_id" start-index="523" stop-index="528" />
+ <column-item name="employee_id" start-index="531" stop-index="541" />
+ </order-by>
+ </select>
</sql-parser-test-cases>
diff --git a/test/parser/src/main/resources/sql/supported/dml/select.xml b/test/parser/src/main/resources/sql/supported/dml/select.xml
index 74adfa43f64..5c7d9e2f948 100644
--- a/test/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/parser/src/main/resources/sql/supported/dml/select.xml
@@ -141,4 +141,5 @@
<sql-case id="select_aggregate_cume_dist" value="SELECT CUME_DIST(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct) 'Cume-Dist of 15500' FROM employees;" db-types="Oracle" />
<sql-case id="select_aggregate_rank" value="SELECT RANK(15500) WITHIN GROUP (ORDER BY salary DESC) 'Rank of 15500' FROM employees;" db-types="Oracle" />
<sql-case id="select_rowid" value="SELECT ROWID FROM employees WHERE ROWIDTOCHAR(ROWID) LIKE '%JAAB%' ORDER BY ROWID;" db-types="Oracle" />
+ <sql-case id="select_linear_regression_function" value="SELECT job_id, employee_id ID, salary, REGR_SLOPE(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) slope, REGR_INTERCEPT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) intcpt, REGR_R2(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) rsqr, REGR_COUNT(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) count, REGR_AVGX(SYSDATE-hire_date, salary) OVER (PARTITION BY job_id) avgx, REGR_AVGY(SYSDATE-hire_date, salar [...]
</sql-cases>