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 36995f24c99 support exists subquery (#28310)
36995f24c99 is described below
commit 36995f24c9962d5cef5742c76f5a2f5d49894445
Author: Yunbo Ni <[email protected]>
AuthorDate: Mon Sep 4 17:17:06 2023 +0800
support exists subquery (#28310)
* feat: exists sub query test case
* feat: exists sub query
* fix: spotless
* fix: checkstyle
* fix: return type in mysql visitor
---
.../impl/SubqueryProjectionConverter.java | 9 ++++++++-
.../visitor/statement/MySQLStatementVisitor.java | 7 ++++++-
.../statement/OpenGaussStatementVisitor.java | 2 ++
.../statement/PostgreSQLStatementVisitor.java | 7 ++++++-
.../sql/parser/sql/common/enums/SubqueryType.java | 2 +-
.../test/resources/converter/select-sub-query.xml | 22 ++++++++++++++++++++++
6 files changed, 45 insertions(+), 4 deletions(-)
diff --git
a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/projection/impl/SubqueryProjectionConverter.java
b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/projection/impl/SubqueryProjectionConverter.java
index 52adc8d0e45..e7cd09880cf 100644
---
a/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/projection/impl/SubqueryProjectionConverter.java
+++
b/kernel/sql-federation/core/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/converter/segment/projection/impl/SubqueryProjectionConverter.java
@@ -22,12 +22,14 @@ import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.SubqueryType;
import
org.apache.shardingsphere.sqlfederation.compiler.converter.segment.SQLSegmentConverter;
import
org.apache.shardingsphere.sqlfederation.compiler.converter.statement.select.SelectStatementConverter;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.SubqueryProjectionSegment;
import java.util.ArrayList;
import java.util.Collection;
+import java.util.Collections;
import java.util.LinkedList;
import java.util.Optional;
@@ -42,7 +44,12 @@ public final class SubqueryProjectionConverter implements
SQLSegmentConverter<Su
return Optional.empty();
}
SqlNode sqlNode = new
SelectStatementConverter().convert(segment.getSubquery().getSelect());
- return segment.getAliasName().isPresent() ?
convertToSQLStatement(sqlNode, segment.getAliasName().get()) :
Optional.of(sqlNode);
+ if (segment.getAliasName().isPresent()) {
+ sqlNode = convertToSQLStatement(sqlNode,
segment.getAliasName().get()).get();
+ }
+ return
segment.getSubquery().getSubqueryType().equals(SubqueryType.EXISTS_SUBQUERY)
+ ? Optional.of(new SqlBasicCall(SqlStdOperatorTable.EXISTS,
Collections.singletonList(sqlNode), SqlParserPos.ZERO))
+ : Optional.of(sqlNode);
}
private Optional<SqlNode> convertToSQLStatement(final SqlNode sqlNode,
final String alias) {
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 f495ec032c8..264a1278ac0 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
@@ -152,6 +152,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.enums.CombineType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
import
org.apache.shardingsphere.sql.parser.sql.common.enums.ParameterMarkerType;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.SubqueryType;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dal.VariableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.constraint.ConstraintSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.engine.EngineSegment;
@@ -592,7 +593,11 @@ public abstract class MySQLStatementVisitor extends
MySQLStatementBaseVisitor<AS
int stopIndex = ctx.stop.getStopIndex();
if (null != ctx.subquery()) {
SubquerySegment subquerySegment = new
SubquerySegment(ctx.subquery().getStart().getStartIndex(),
ctx.subquery().getStop().getStopIndex(), (MySQLSelectStatement)
visit(ctx.subquery()));
- return null == ctx.EXISTS() ? new
SubqueryExpressionSegment(subquerySegment) : new
ExistsSubqueryExpression(startIndex, stopIndex, subquerySegment);
+ if (null != ctx.EXISTS()) {
+ subquerySegment.setSubqueryType(SubqueryType.EXISTS_SUBQUERY);
+ return new ExistsSubqueryExpression(startIndex, stopIndex,
subquerySegment);
+ }
+ return new SubqueryExpressionSegment(subquerySegment);
}
if (null != ctx.parameterMarker()) {
ParameterMarkerValue parameterMarker = (ParameterMarkerValue)
visit(ctx.parameterMarker());
diff --git
a/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
b/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
index f997564004f..a06f67b24ec 100644
---
a/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
+++
b/parser/sql/dialect/opengauss/src/main/java/org/apache/shardingsphere/sql/parser/opengauss/visitor/statement/OpenGaussStatementVisitor.java
@@ -116,6 +116,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.enums.CombineType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
import
org.apache.shardingsphere.sql.parser.sql.common.enums.ParameterMarkerType;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.SubqueryType;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.constraint.ConstraintSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.index.IndexNameSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.index.IndexSegment;
@@ -414,6 +415,7 @@ public abstract class OpenGaussStatementVisitor extends
OpenGaussStatementBaseVi
SubquerySegment subquerySegment = new
SubquerySegment(ctx.selectWithParens().getStart().getStartIndex(),
ctx.selectWithParens().getStop().getStopIndex(),
(OpenGaussSelectStatement) visit(ctx.selectWithParens()));
if (null != ctx.EXISTS()) {
+ subquerySegment.setSubqueryType(SubqueryType.EXISTS_SUBQUERY);
return new ExistsSubqueryExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), subquerySegment);
}
return new SubqueryExpressionSegment(subquerySegment);
diff --git
a/parser/sql/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
b/parser/sql/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
index 6789e0653e6..4cdde01be62 100644
---
a/parser/sql/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
+++
b/parser/sql/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/postgresql/visitor/statement/PostgreSQLStatementVisitor.java
@@ -116,6 +116,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.enums.CombineType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.JoinType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
import
org.apache.shardingsphere.sql.parser.sql.common.enums.ParameterMarkerType;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.SubqueryType;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.constraint.ConstraintSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.index.IndexNameSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.ddl.index.IndexSegment;
@@ -413,7 +414,11 @@ public abstract class PostgreSQLStatementVisitor extends
PostgreSQLStatementPars
private ExpressionSegment createSubqueryExpressionSegment(final
CExprContext ctx) {
SubquerySegment subquerySegment = new
SubquerySegment(ctx.selectWithParens().getStart().getStartIndex(),
ctx.selectWithParens().getStop().getStopIndex(),
(PostgreSQLSelectStatement) visit(ctx.selectWithParens()));
- return null == ctx.EXISTS() ? new
SubqueryExpressionSegment(subquerySegment) : new
ExistsSubqueryExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(),
subquerySegment);
+ if (null != ctx.EXISTS()) {
+ subquerySegment.setSubqueryType(SubqueryType.EXISTS_SUBQUERY);
+ return new ExistsSubqueryExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), subquerySegment);
+ }
+ return new SubqueryExpressionSegment(subquerySegment);
}
@Override
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/enums/SubqueryType.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/enums/SubqueryType.java
index e2b752cb1bc..383c9ede9d4 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/enums/SubqueryType.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/enums/SubqueryType.java
@@ -22,5 +22,5 @@ package org.apache.shardingsphere.sql.parser.sql.common.enums;
*/
public enum SubqueryType {
- PROJECTION_SUBQUERY, TABLE_SUBQUERY, JOIN_SUBQUERY, PREDICATE_SUBQUERY,
INSERT_SELECT_SUBQUERY
+ PROJECTION_SUBQUERY, TABLE_SUBQUERY, JOIN_SUBQUERY, PREDICATE_SUBQUERY,
INSERT_SELECT_SUBQUERY, EXISTS_SUBQUERY
}
diff --git
a/test/it/optimizer/src/test/resources/converter/select-sub-query.xml
b/test/it/optimizer/src/test/resources/converter/select-sub-query.xml
new file mode 100644
index 00000000000..8042876c366
--- /dev/null
+++ b/test/it/optimizer/src/test/resources/converter/select-sub-query.xml
@@ -0,0 +1,22 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ 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.
+ -->
+
+<sql-node-converter-test-cases>
+ <test-cases sql-case-id="select_with_exists_sub_query_with_project"
expected-sql="SELECT EXISTS (SELECT 1 FROM `t_order`)" db-types="MySQL" />
+ <test-cases sql-case-id="select_with_exists_sub_query_with_project"
expected-sql="SELECT EXISTS (SELECT 1 FROM "t_order")"
db-types="PostgreSQL,openGauss" />
+</sql-node-converter-test-cases>
\ No newline at end of file
