This is an automated email from the ASF dual-hosted git repository.
panjuan 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 4a68756 optimize postgresql subquery parse (#12440)
4a68756 is described below
commit 4a687565e9381d7a7a9d8b6d64b4baeb7921afa0
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Wed Sep 15 12:12:32 2021 +0800
optimize postgresql subquery parse (#12440)
---
.../impl/PostgreSQLStatementSQLVisitor.java | 63 +++++++++++-----------
.../main/resources/case/dml/select-sub-query.xml | 19 +------
.../sql/supported/dml/select-sub-query.xml | 9 ++--
3 files changed, 36 insertions(+), 55 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
index dea6217..f43eda3 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/impl/PostgreSQLStatementSQLVisitor.java
@@ -108,6 +108,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.Column
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.InsertColumnsSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BetweenExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExistsSubqueryExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.InExpression;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ListExpression;
@@ -137,6 +138,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.li
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.HavingSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.LockSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.predicate.WhereSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasAvailable;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.DataTypeLengthSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.DataTypeSegment;
@@ -320,11 +322,23 @@ public abstract class PostgreSQLStatementSQLVisitor
extends PostgreSQLStatementB
if (null != ctx.funcExpr()) {
return visit(ctx.funcExpr());
}
+ if (null != ctx.selectWithParens()) {
+ return createSubqueryExpressionSegment(ctx);
+ }
super.visitCExpr(ctx);
String text = ctx.start.getInputStream().getText(new
Interval(ctx.start.getStartIndex(), ctx.stop.getStopIndex()));
return new CommonExpressionSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), text);
}
+ private ExpressionSegment createSubqueryExpressionSegment(final
CExprContext ctx) {
+ SubquerySegment subquerySegment = new
SubquerySegment(ctx.selectWithParens().getStart().getStartIndex(),
+ ctx.selectWithParens().getStop().getStopIndex(),
(PostgreSQLSelectStatement) visit(ctx.selectWithParens()));
+ if (null != ctx.EXISTS()) {
+ return new ExistsSubqueryExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), subquerySegment);
+ }
+ return new SubqueryExpressionSegment(subquerySegment);
+ }
+
@Override
public ASTNode visitFuncExpr(final FuncExprContext ctx) {
calculateParameterCount(getTargetRuleContextFromParseTree(ctx,
CExprContext.class));
@@ -858,43 +872,28 @@ public abstract class PostgreSQLStatementSQLVisitor
extends PostgreSQLStatementB
return shorthandProjection;
}
AExprContext expr = ctx.aExpr();
- if (1 == expr.getChildCount() && null != expr.cExpr()) {
+ ProjectionSegment result = new
ExpressionProjectionSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(),
expr.getText());
+ if (null != expr.cExpr()) {
ASTNode projection = visit(expr.cExpr());
- AliasSegment alias = null != ctx.identifier()
- ? new AliasSegment(ctx.identifier().start.getStartIndex(),
ctx.identifier().stop.getStopIndex(), new
IdentifierValue(ctx.identifier().getText())) : null;
if (projection instanceof ColumnSegment) {
- ColumnProjectionSegment result = new
ColumnProjectionSegment((ColumnSegment) projection);
- result.setAlias(alias);
- return result;
+ result = new ColumnProjectionSegment((ColumnSegment)
projection);
}
- }
- if (null != expr.cExpr() && null != expr.cExpr().funcExpr()) {
- visit(expr.cExpr().funcExpr());
- ProjectionSegment projection =
generateProjectFromFuncExpr(expr.cExpr().funcExpr());
- AliasSegment alias = null != ctx.identifier()
- ? new AliasSegment(ctx.identifier().start.getStartIndex(),
ctx.identifier().stop.getStopIndex(), new
IdentifierValue(ctx.identifier().getText())) : null;
- if (projection instanceof AggregationProjectionSegment) {
- ((AggregationProjectionSegment) projection).setAlias(alias);
+ if (null != expr.cExpr().funcExpr()) {
+ result = generateProjectFromFuncExpr(expr.cExpr().funcExpr());
}
- if (projection instanceof AggregationDistinctProjectionSegment) {
- ((AggregationDistinctProjectionSegment)
projection).setAlias(alias);
+ if (projection instanceof SubqueryExpressionSegment) {
+ SubqueryExpressionSegment subqueryExpression =
(SubqueryExpressionSegment) projection;
+ String text = ctx.start.getInputStream().getText(new
Interval(subqueryExpression.getStartIndex(),
subqueryExpression.getStopIndex()));
+ result = new
SubqueryProjectionSegment(subqueryExpression.getSubquery(), text);
}
- return projection;
- }
- if (null != expr.cExpr() && null != expr.cExpr().selectWithParens()) {
- PostgreSQLSelectStatement select = (PostgreSQLSelectStatement)
visit(expr.cExpr().selectWithParens());
- SubquerySegment subquery = new
SubquerySegment(expr.cExpr().selectWithParens().start.getStartIndex(),
expr.cExpr().selectWithParens().stop.getStopIndex(), select);
- String text = ctx.start.getInputStream().getText(new
Interval(subquery.getStartIndex(), subquery.getStopIndex()));
- SubqueryProjectionSegment projection = new
SubqueryProjectionSegment(subquery, text);
- AliasSegment alias = null != ctx.identifier()
- ? new AliasSegment(ctx.identifier().start.getStartIndex(),
ctx.identifier().stop.getStopIndex(), new
IdentifierValue(ctx.identifier().getText())) : null;
- projection.setAlias(alias);
- return projection;
- }
- ExpressionProjectionSegment result = new
ExpressionProjectionSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(),
expr.getText());
- if (null != ctx.identifier()) {
- AliasSegment alias = new
AliasSegment(ctx.identifier().start.getStartIndex(),
ctx.identifier().stop.getStopIndex(), new
IdentifierValue(ctx.identifier().getText()));
- result.setAlias(alias);
+ if (projection instanceof ExistsSubqueryExpression) {
+ ExistsSubqueryExpression existsSubqueryExpression =
(ExistsSubqueryExpression) projection;
+ String text = ctx.start.getInputStream().getText(new
Interval(existsSubqueryExpression.getStartIndex(),
existsSubqueryExpression.getStopIndex()));
+ result = new
SubqueryProjectionSegment(existsSubqueryExpression.getSubquery(), text);
+ }
+ }
+ if (result instanceof AliasAvailable && null != ctx.identifier()) {
+ ((AliasAvailable) result).setAlias(new
AliasSegment(ctx.identifier().start.getStartIndex(),
ctx.identifier().stop.getStopIndex(), new
IdentifierValue(ctx.identifier().getText())));
}
return result;
}
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-sub-query.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-sub-query.xml
index 9620e91..5fed181 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-sub-query.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-sub-query.xml
@@ -222,7 +222,7 @@
</where>
</select>
- <select sql-case-id="select_with_exists_sub_query_with_project_for_mysql">
+ <select sql-case-id="select_with_exists_sub_query_with_project">
<projections start-index="7" stop-index="36">
<subquery-projection start-index="14" stop-index="36" text="EXISTS
(SELECT 1 FROM t_order)" literal-text="EXISTS (SELECT 1 FROM t_order)">
<subquery start-index="15" stop-index="35">
@@ -238,21 +238,4 @@
</subquery-projection>
</projections>
</select>
-
- <select
sql-case-id="select_with_exists_sub_query_with_project_for_postgresql">
- <projections start-index="7" stop-index="36">
- <subquery-projection start-index="14" stop-index="36"
text="(SELECT 1 FROM t_order)" literal-text="(SELECT 1 FROM t_order)">
- <subquery start-index="15" stop-index="35">
- <select>
- <from>
- <simple-table name="t_order" start-index="29"
stop-index="35" />
- </from>
- <projections start-index="22" stop-index="22">
- <expression-projection start-index="22"
stop-index="22" text="1"/>
- </projections>
- </select>
- </subquery>
- </subquery-projection>
- </projections>
- </select>
</sql-parser-test-cases>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-sub-query.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-sub-query.xml
index 0e50916..24ec921 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-sub-query.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-sub-query.xml
@@ -19,9 +19,8 @@
<sql-cases>
<sql-case id="select_sub_query_with_project" value="SELECT order_id,
(SELECT 1) AS num FROM t_order" db-types="MySQL, PostgreSQL, SQLServer"/>
<sql-case id="select_sub_query_with_table" value="SELECT t.* FROM (SELECT
* FROM t_order WHERE order_id IN (?, ?)) t" />
- <sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL" />
- <sql-case id="select_with_in_subquery" value="SELECT * FROM t_order WHERE
user_id IN (SELECT user_id FROM t_order_item WHERE id IN (10, 11))"
db-types="MySQL" />
- <sql-case id="select_with_between_subquery" value="SELECT * FROM t_order
WHERE user_id BETWEEN (SELECT user_id FROM t_order_item WHERE order_id = 10)
AND ?" db-types="MySQL" />
- <sql-case id="select_with_exists_sub_query_with_project_for_mysql"
value="SELECT EXISTS (SELECT 1 FROM t_order)" db-types="MySQL" />
- <sql-case id="select_with_exists_sub_query_with_project_for_postgresql"
value="SELECT EXISTS (SELECT 1 FROM t_order)" db-types="PostgreSQL" />
+ <sql-case id="select_with_equal_subquery" value="SELECT * FROM t_order
WHERE user_id = (SELECT user_id FROM t_order_item WHERE id = 10)"
db-types="MySQL, PostgreSQL" />
+ <sql-case id="select_with_in_subquery" value="SELECT * FROM t_order WHERE
user_id IN (SELECT user_id FROM t_order_item WHERE id IN (10, 11))"
db-types="MySQL, PostgreSQL" />
+ <sql-case id="select_with_between_subquery" value="SELECT * FROM t_order
WHERE user_id BETWEEN (SELECT user_id FROM t_order_item WHERE order_id = 10)
AND ?" db-types="MySQL, PostgreSQL" />
+ <sql-case id="select_with_exists_sub_query_with_project" value="SELECT
EXISTS (SELECT 1 FROM t_order)" db-types="MySQL, PostgreSQL" />
</sql-cases>
