You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/10/22 11:15:52 UTC
[shardingsphere] branch master updated: support join query convert
to sql statement (#13229)
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 d6d2dd0 support join query convert to sql statement (#13229)
d6d2dd0 is described below
commit d6d2dd0d2b964a2043fa3f320522ca87f79095c5
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Fri Oct 22 19:14:55 2021 +0800
support join query convert to sql statement (#13229)
* support join query convert to sql statement
* optimize parameter count
* fix checkstyle
* optimize code
---
.../infra/optimize/ShardingSphereOptimizer.java | 4 +-
.../infra/optimize/context/OptimizerContext.java | 2 +-
...vertEngine.java => SQLNodeConverterEngine.java} | 4 +-
.../ConverterContext.java} | 15 +++----
.../converter/segment/from/TableConverter.java | 13 +++++-
.../segment/from/impl/JoinTableConverter.java | 11 ++++-
.../segment/from/impl/SimpleTableConverter.java | 8 ++--
.../segment/groupby/GroupByConverter.java | 10 ++++-
.../segment/limit/AbstractLimitConverter.java | 49 ----------------------
.../segment/limit/PaginationValueSQLConverter.java | 14 +++++++
.../converter/segment/limit/RowCountConverter.java | 30 -------------
.../segment/orderby/OrderByConverter.java | 2 +-
.../orderby/item/ColumnOrderByItemConverter.java | 12 +++++-
.../statement/SelectStatementConverter.java | 48 ++++++++++++++++-----
.../SQLNodeConvertEngineParameterizedTest.java | 9 ++--
.../sql/supported/dml/select-relation.xml | 4 +-
16 files changed, 116 insertions(+), 119 deletions(-)
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/ShardingSphereOptimizer.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/ShardingSphereOptimizer.java
index 8649c6b..4eca44d 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/ShardingSphereOptimizer.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/ShardingSphereOptimizer.java
@@ -37,7 +37,7 @@ import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.Pair;
import org.apache.shardingsphere.infra.exception.ShardingSphereException;
import org.apache.shardingsphere.infra.optimize.context.OptimizerContext;
-import org.apache.shardingsphere.infra.optimize.converter.SQLNodeConvertEngine;
+import org.apache.shardingsphere.infra.optimize.converter.SQLNodeConverterEngine;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import java.util.ArrayList;
@@ -61,7 +61,7 @@ public final class ShardingSphereOptimizer {
*/
public RelNode optimize(final String schemaName, final SQLStatement sqlStatement) {
try {
- SqlNode sqlNode = SQLNodeConvertEngine.convertToSQLNode(sqlStatement);
+ SqlNode sqlNode = SQLNodeConverterEngine.convertToSQLNode(sqlStatement);
SqlNode validNode = context.getPlannerContexts().get(schemaName).getValidator().validate(sqlNode);
RelDataType resultType = context.getPlannerContexts().get(schemaName).getValidator().getValidatedNodeType(sqlNode);
RelNode queryPlan = context.getPlannerContexts().get(schemaName).getConverter().convertQuery(validNode, false, true).rel;
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/context/OptimizerContext.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/context/OptimizerContext.java
index 1415919..f1b4321 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/context/OptimizerContext.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/context/OptimizerContext.java
@@ -26,7 +26,7 @@ import org.apache.shardingsphere.infra.optimize.metadata.FederationMetaData;
import java.util.Map;
/**
- * Optimize context.
+ * Optimizer context.
*/
@RequiredArgsConstructor
@Getter
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/SQLNodeConvertEngine.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/SQLNodeConverterEngine.java
similarity index 96%
rename from shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/SQLNodeConvertEngine.java
rename to shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/SQLNodeConverterEngine.java
index 878921f..dee4a28 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/SQLNodeConvertEngine.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/SQLNodeConverterEngine.java
@@ -27,10 +27,10 @@ import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
/**
- * SQL node convert engine.
+ * SQL node converter engine.
*/
@NoArgsConstructor(access = AccessLevel.PRIVATE)
-public final class SQLNodeConvertEngine {
+public final class SQLNodeConverterEngine {
/**
* Convert SQL statement to SQL node.
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/OffsetConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/context/ConverterContext.java
similarity index 70%
rename from shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/OffsetConverter.java
rename to shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/context/ConverterContext.java
index 8eb77ab..50ba252 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/OffsetConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/context/ConverterContext.java
@@ -15,16 +15,17 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.infra.optimize.converter.segment.limit;
+package org.apache.shardingsphere.infra.optimize.converter.context;
-import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
+import lombok.Getter;
+
+import java.util.concurrent.atomic.AtomicInteger;
/**
- * Offset converter.
+ * Converter context.
*/
-public final class OffsetConverter extends AbstractLimitConverter {
+@Getter
+public final class ConverterContext {
- public OffsetConverter() {
- super(LimitSegment::getOffset);
- }
+ private final AtomicInteger parameterCount = new AtomicInteger();
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/TableConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/TableConverter.java
index 8ef0033..c7421e0 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/TableConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/TableConverter.java
@@ -21,6 +21,8 @@ import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlJoin;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlOrderBy;
+import org.apache.calcite.sql.SqlSelect;
import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.from.impl.JoinTableConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.from.impl.SimpleTableConverter;
@@ -56,8 +58,17 @@ public final class TableConverter implements SQLSegmentConverter<TableSegment, S
} else if (sqlNode instanceof SqlJoin) {
return new JoinTableConverter().convertToSQLSegment((SqlJoin) sqlNode).map(optional -> optional);
} else if (sqlNode instanceof SqlBasicCall) {
- return new SubqueryTableConverter().convertToSQLSegment((SqlBasicCall) sqlNode).map(optional -> optional);
+ Optional<SqlNode> existSubquery = ((SqlBasicCall) sqlNode).getOperandList().stream().filter(this::containsSqlSelect).findAny();
+ if (existSubquery.isPresent()) {
+ return new SubqueryTableConverter().convertToSQLSegment((SqlBasicCall) sqlNode).map(optional -> optional);
+ } else {
+ return new SimpleTableConverter().convertToSQLSegment(sqlNode).map(optional -> optional);
+ }
}
throw new UnsupportedOperationException("Unsupported sql node type: " + sqlNode.getClass());
}
+
+ private boolean containsSqlSelect(final SqlNode sqlNode) {
+ return sqlNode instanceof SqlSelect || sqlNode instanceof SqlOrderBy;
+ }
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/JoinTableConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/JoinTableConverter.java
index ac6b7b7..a5e2ab4 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/JoinTableConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/JoinTableConverter.java
@@ -27,6 +27,7 @@ import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConv
import org.apache.shardingsphere.infra.optimize.converter.segment.expression.ExpressionConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.from.TableConverter;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.JoinTableSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
import java.util.Optional;
@@ -55,7 +56,15 @@ public final class JoinTableConverter implements SQLSegmentConverter<JoinTableSe
@Override
public Optional<JoinTableSegment> convertToSQLSegment(final SqlJoin sqlJoin) {
- return Optional.empty();
+ TableSegment left = new TableConverter().convertToSQLSegment(sqlJoin.getLeft()).orElseThrow(IllegalStateException::new);
+ TableSegment right = new TableConverter().convertToSQLSegment(sqlJoin.getRight()).orElseThrow(IllegalStateException::new);
+ JoinTableSegment result = new JoinTableSegment();
+ result.setStartIndex(getStartIndex(sqlJoin));
+ result.setStartIndex(getStopIndex(sqlJoin));
+ result.setLeft(left);
+ result.setRight(right);
+ new ExpressionConverter().convertToSQLSegment(sqlJoin.getCondition()).ifPresent(result::setCondition);
+ return Optional.of(result);
}
private SqlLiteral convertJoinType(final String joinType) {
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SimpleTableConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SimpleTableConverter.java
index 300baed..b7601f8 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SimpleTableConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SimpleTableConverter.java
@@ -17,13 +17,13 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.from.impl;
-import com.google.common.collect.ImmutableList;
import org.apache.calcite.sql.SqlBasicCall;
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.infra.optimize.converter.segment.SQLSegmentConverter;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableNameSegment;
import org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
@@ -51,8 +51,10 @@ public final class SimpleTableConverter implements SQLSegmentConverter<SimpleTab
if (sqlNode instanceof SqlBasicCall) {
SqlBasicCall sqlBasicCall = (SqlBasicCall) sqlNode;
if (sqlBasicCall.getOperator().equals(SqlStdOperatorTable.AS)) {
- ImmutableList<String> names = ((SqlIdentifier) sqlBasicCall.getOperandList().get(0)).names;
- SimpleTableSegment tableSegment = new SimpleTableSegment(new TableNameSegment(getStartIndex(sqlNode), getStopIndex(sqlNode), new IdentifierValue(names.get(0))));
+ String name = sqlBasicCall.getOperandList().get(0).toString();
+ SimpleTableSegment tableSegment = new SimpleTableSegment(new TableNameSegment(getStartIndex(sqlNode), getStopIndex(sqlNode), new IdentifierValue(name)));
+ SqlNode alias = sqlBasicCall.getOperandList().get(1);
+ tableSegment.setAlias(new AliasSegment(getStartIndex(alias), getStopIndex(alias), new IdentifierValue(alias.toString())));
return Optional.of(tableSegment);
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/groupby/GroupByConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/groupby/GroupByConverter.java
index 8603830..92ea31e 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/groupby/GroupByConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/groupby/GroupByConverter.java
@@ -22,7 +22,9 @@ import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.orderby.item.OrderByItemConverterUtil;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.GroupBySegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.OrderByItemSegment;
+import java.util.Collection;
import java.util.Optional;
/**
@@ -37,7 +39,11 @@ public final class GroupByConverter implements SQLSegmentConverter<GroupBySegmen
}
@Override
- public Optional<GroupBySegment> convertToSQLSegment(final SqlNodeList sqlNode) {
- return Optional.empty();
+ public Optional<GroupBySegment> convertToSQLSegment(final SqlNodeList sqlNodeList) {
+ if (null == sqlNodeList || 0 == sqlNodeList.size()) {
+ return Optional.empty();
+ }
+ Collection<OrderByItemSegment> orderByItems = OrderByItemConverterUtil.convertToSQLSegment(sqlNodeList);
+ return Optional.of(new GroupBySegment(getStartIndex(sqlNodeList), getStopIndex(sqlNodeList), orderByItems));
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/AbstractLimitConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/AbstractLimitConverter.java
deleted file mode 100644
index d366969..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/AbstractLimitConverter.java
+++ /dev/null
@@ -1,49 +0,0 @@
-/*
- * 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.
- */
-
-package org.apache.shardingsphere.infra.optimize.converter.segment.limit;
-
-import lombok.RequiredArgsConstructor;
-import org.apache.calcite.sql.SqlNode;
-import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
-import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.PaginationValueSegment;
-import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
-
-import java.util.Optional;
-import java.util.function.Function;
-
-/**
- * Abstract limit converter.
- */
-@RequiredArgsConstructor
-public abstract class AbstractLimitConverter implements SQLSegmentConverter<LimitSegment, SqlNode> {
-
- private final Function<LimitSegment, Optional<PaginationValueSegment>> function;
-
- @Override
- public final Optional<SqlNode> convertToSQLNode(final LimitSegment segment) {
- if (null == segment) {
- return Optional.empty();
- }
- return function.apply(segment).flatMap(optional -> new PaginationValueSQLConverter().convertToSQLNode(optional));
- }
-
- @Override
- public Optional<LimitSegment> convertToSQLSegment(final SqlNode sqlNode) {
- return Optional.empty();
- }
-}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/PaginationValueSQLConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/PaginationValueSQLConverter.java
index 090d8d8..8c5dbd8 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/PaginationValueSQLConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/PaginationValueSQLConverter.java
@@ -17,13 +17,17 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.limit;
+import lombok.RequiredArgsConstructor;
import org.apache.calcite.sql.SqlDynamicParam;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.shardingsphere.infra.optimize.converter.context.ConverterContext;
import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.NumberLiteralPaginationValueSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.PaginationValueSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.NumberLiteralLimitValueSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.ParameterMarkerLimitValueSegment;
import java.util.Optional;
@@ -31,8 +35,11 @@ import java.util.Optional;
/**
* Pagination value converter.
*/
+@RequiredArgsConstructor
public final class PaginationValueSQLConverter implements SQLSegmentConverter<PaginationValueSegment, SqlNode> {
+ private final ConverterContext context;
+
@Override
public Optional<SqlNode> convertToSQLNode(final PaginationValueSegment segment) {
return Optional.of(segment instanceof NumberLiteralPaginationValueSegment
@@ -49,6 +56,13 @@ public final class PaginationValueSQLConverter implements SQLSegmentConverter<Pa
@Override
public Optional<PaginationValueSegment> convertToSQLSegment(final SqlNode sqlNode) {
+ if (sqlNode instanceof SqlNumericLiteral) {
+ return Optional.of(new NumberLiteralLimitValueSegment(getStartIndex(sqlNode), getStopIndex(sqlNode), ((SqlNumericLiteral) sqlNode).getValueAs(Long.class)));
+ }
+ if (sqlNode instanceof SqlDynamicParam) {
+ context.getParameterCount().incrementAndGet();
+ return Optional.of(new ParameterMarkerLimitValueSegment(getStartIndex(sqlNode), getStopIndex(sqlNode), ((SqlDynamicParam) sqlNode).getIndex()));
+ }
return Optional.empty();
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/RowCountConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/RowCountConverter.java
deleted file mode 100644
index 98f677a..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/limit/RowCountConverter.java
+++ /dev/null
@@ -1,30 +0,0 @@
-/*
- * 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.
- */
-
-package org.apache.shardingsphere.infra.optimize.converter.segment.limit;
-
-import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
-
-/**
- * Row count converter.
- */
-public final class RowCountConverter extends AbstractLimitConverter {
-
- public RowCountConverter() {
- super(LimitSegment::getRowCount);
- }
-}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/OrderByConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/OrderByConverter.java
index 56196c6..7aac7ba 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/OrderByConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/OrderByConverter.java
@@ -39,7 +39,7 @@ public final class OrderByConverter implements SQLSegmentConverter<OrderBySegmen
@Override
public Optional<OrderBySegment> convertToSQLSegment(final SqlNodeList sqlNodeList) {
- if (null == sqlNodeList) {
+ if (null == sqlNodeList || 0 == sqlNodeList.size()) {
return Optional.empty();
}
Collection<OrderByItemSegment> orderByItems = OrderByItemConverterUtil.convertToSQLSegment(sqlNodeList);
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/item/ColumnOrderByItemConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/item/ColumnOrderByItemConverter.java
index dd9c97b..a634802 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/item/ColumnOrderByItemConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/orderby/item/ColumnOrderByItemConverter.java
@@ -27,6 +27,7 @@ import org.apache.shardingsphere.infra.optimize.converter.segment.expression.imp
import org.apache.shardingsphere.sql.parser.sql.common.constant.OrderDirection;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ColumnOrderByItemSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment;
import org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
import java.util.Objects;
@@ -52,7 +53,14 @@ public final class ColumnOrderByItemConverter implements SQLSegmentConverter<Col
return Optional.empty();
}
SqlIdentifier sqlIdentifier = (SqlIdentifier) sqlNode;
- ColumnSegment column = new ColumnSegment(getStartIndex(sqlIdentifier), getStopIndex(sqlIdentifier), new IdentifierValue(sqlIdentifier.names.get(0)));
- return Optional.of(new ColumnOrderByItemSegment(column, OrderDirection.ASC));
+ if (sqlIdentifier.names.size() > 1) {
+ SqlIdentifier column = sqlIdentifier.getComponent(1);
+ SqlIdentifier owner = sqlIdentifier.getComponent(0);
+ ColumnSegment columnSegment = new ColumnSegment(getStartIndex(sqlIdentifier), getStopIndex(sqlIdentifier), new IdentifierValue(column.toString()));
+ columnSegment.setOwner(new OwnerSegment(getStartIndex(owner), getStopIndex(owner), new IdentifierValue(owner.toString())));
+ return Optional.of(new ColumnOrderByItemSegment(columnSegment, OrderDirection.ASC));
+ }
+ ColumnSegment columnSegment = new ColumnSegment(getStartIndex(sqlIdentifier), getStopIndex(sqlIdentifier), new IdentifierValue(sqlIdentifier.names.get(0)));
+ return Optional.of(new ColumnOrderByItemSegment(columnSegment, OrderDirection.ASC));
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverter.java
index 9abfd0b..968e494 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverter.java
@@ -22,21 +22,26 @@ import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlOrderBy;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.shardingsphere.infra.optimize.converter.context.ConverterContext;
import org.apache.shardingsphere.infra.optimize.converter.segment.from.TableConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.groupby.GroupByConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.groupby.HavingConverter;
-import org.apache.shardingsphere.infra.optimize.converter.segment.limit.OffsetConverter;
-import org.apache.shardingsphere.infra.optimize.converter.segment.limit.RowCountConverter;
+import org.apache.shardingsphere.infra.optimize.converter.segment.limit.PaginationValueSQLConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.orderby.OrderByConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.projection.DistinctConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.projection.ProjectionsConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.where.WhereConverter;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.SQLSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionsSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.PaginationValueSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment;
import org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import org.apache.shardingsphere.sql.parser.sql.dialect.handler.dml.SelectStatementHandler;
import org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLSelectStatement;
+import java.util.Comparator;
+import java.util.LinkedList;
+import java.util.List;
import java.util.Optional;
/**
@@ -44,6 +49,8 @@ import java.util.Optional;
*/
public final class SelectStatementConverter implements SQLStatementConverter<SelectStatement, SqlNode> {
+ private static final int LIMIT_SEGMENT_LENGTH = 6;
+
@Override
public SqlNode convertToSQLNode(final SelectStatement selectStatement) {
SqlNodeList distinct = new DistinctConverter().convertToSQLNode(selectStatement.getProjections()).orElse(null);
@@ -54,15 +61,15 @@ public final class SelectStatementConverter implements SQLStatementConverter<Sel
SqlNode having = selectStatement.getHaving().flatMap(optional -> new HavingConverter().convertToSQLNode(optional)).orElse(null);
SqlNodeList orderBy = selectStatement.getOrderBy().flatMap(optional -> new OrderByConverter().convertToSQLNode(optional)).orElse(SqlNodeList.EMPTY);
Optional<LimitSegment> limit = SelectStatementHandler.getLimitSegment(selectStatement);
- SqlNode offset = limit.flatMap(optional -> new OffsetConverter().convertToSQLNode(optional)).orElse(null);
- SqlNode rowCount = limit.flatMap(optional -> new RowCountConverter().convertToSQLNode(optional)).orElse(null);
+ ConverterContext context = new ConverterContext();
SqlSelect sqlSelect = new SqlSelect(SqlParserPos.ZERO, distinct, projection, from,
where, groupBy, having, SqlNodeList.EMPTY, null, null, null, SqlNodeList.EMPTY);
- return containsOrderBy(orderBy, offset, rowCount) ? new SqlOrderBy(SqlParserPos.ZERO, sqlSelect, orderBy, offset, rowCount) : sqlSelect;
- }
-
- private boolean containsOrderBy(final SqlNodeList orderBy, final SqlNode offset, final SqlNode rowCount) {
- return (null != orderBy && !orderBy.isEmpty()) || null != offset || null != rowCount;
+ if (limit.isPresent()) {
+ SqlNode offset = limit.get().getOffset().flatMap(optional -> new PaginationValueSQLConverter(context).convertToSQLNode(optional)).orElse(null);
+ SqlNode rowCount = limit.get().getRowCount().flatMap(optional -> new PaginationValueSQLConverter(context).convertToSQLNode(optional)).orElse(null);
+ return new SqlOrderBy(SqlParserPos.ZERO, sqlSelect, orderBy, offset, rowCount);
+ }
+ return !orderBy.isEmpty() ? new SqlOrderBy(SqlParserPos.ZERO, sqlSelect, orderBy, null, null) : sqlSelect;
}
@Override
@@ -77,12 +84,31 @@ public final class SelectStatementConverter implements SQLStatementConverter<Sel
new WhereConverter().convertToSQLSegment(sqlSelect.getWhere()).ifPresent(result::setWhere);
new GroupByConverter().convertToSQLSegment(sqlSelect.getGroup()).ifPresent(result::setGroupBy);
new HavingConverter().convertToSQLSegment(sqlSelect.getHaving()).ifPresent(result::setHaving);
+ ConverterContext context = new ConverterContext();
if (sqlNode instanceof SqlOrderBy) {
SqlOrderBy sqlOrderBy = (SqlOrderBy) sqlNode;
new OrderByConverter().convertToSQLSegment(sqlOrderBy.orderList).ifPresent(result::setOrderBy);
- Optional.ofNullable(sqlOrderBy.offset).flatMap(optional -> new OffsetConverter().convertToSQLSegment(optional)).ifPresent(result::setLimit);
- Optional.ofNullable(sqlOrderBy.fetch).flatMap(optional -> new RowCountConverter().convertToSQLSegment(optional)).ifPresent(result::setLimit);
+ createLimitSegment(sqlOrderBy, context).ifPresent(result::setLimit);
}
+ result.setParameterCount(context.getParameterCount().get());
return result;
}
+
+ private Optional<LimitSegment> createLimitSegment(final SqlOrderBy sqlOrderBy, final ConverterContext context) {
+ if (null == sqlOrderBy.offset && null == sqlOrderBy.fetch) {
+ return Optional.empty();
+ }
+ Optional<PaginationValueSegment> offset = Optional.ofNullable(sqlOrderBy.offset).flatMap(optional -> new PaginationValueSQLConverter(context).convertToSQLSegment(optional));
+ Optional<PaginationValueSegment> rowCount = Optional.ofNullable(sqlOrderBy.fetch).flatMap(optional -> new PaginationValueSQLConverter(context).convertToSQLSegment(optional));
+ List<Integer> startIndexes = new LinkedList<>();
+ List<Integer> stopIndexes = new LinkedList<>();
+ offset.map(SQLSegment::getStartIndex).ifPresent(startIndexes::add);
+ rowCount.map(SQLSegment::getStartIndex).ifPresent(startIndexes::add);
+ offset.map(SQLSegment::getStopIndex).ifPresent(stopIndexes::add);
+ rowCount.map(SQLSegment::getStopIndex).ifPresent(stopIndexes::add);
+ // FIXME Now sqlNode position returned by the CalCite parser does not contain LIMIT and requires manual calculation
+ int startIndex = startIndexes.stream().min(Comparator.naturalOrder()).orElse(0) - LIMIT_SEGMENT_LENGTH;
+ int stopIndex = stopIndexes.stream().max(Comparator.naturalOrder()).orElse(0);
+ return Optional.of(new LimitSegment(startIndex, stopIndex, offset.orElse(null), rowCount.orElse(null)));
+ }
}
diff --git a/shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java b/shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java
index 58cc854..ccf55a3 100644
--- a/shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java
+++ b/shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java
@@ -31,7 +31,7 @@ import org.apache.calcite.util.Litmus;
import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.database.type.DatabaseTypeRegistry;
import org.apache.shardingsphere.infra.optimize.context.parser.dialect.OptimizerSQLDialectBuilderFactory;
-import org.apache.shardingsphere.infra.optimize.converter.SQLNodeConvertEngine;
+import org.apache.shardingsphere.infra.optimize.converter.SQLNodeConverterEngine;
import org.apache.shardingsphere.sql.parser.api.SQLParserEngine;
import org.apache.shardingsphere.sql.parser.api.SQLVisitorEngine;
import org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement;
@@ -43,7 +43,6 @@ import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.SQLPar
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.domain.statement.SQLParserTestCase;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.sql.SQLCaseType;
import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.sql.loader.SQLCasesLoader;
-import org.junit.Ignore;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized;
@@ -120,18 +119,18 @@ public final class SQLNodeConvertEngineParameterizedTest {
String databaseType = "H2".equals(this.databaseType) ? "MySQL" : this.databaseType;
String sql = SQL_CASES_LOADER.getCaseValue(sqlCaseId, sqlCaseType, SQL_PARSER_TEST_CASES_REGISTRY.get(sqlCaseId).getParameters());
SQLStatement sqlStatement = parseSQLStatement(databaseType, sql);
- SqlNode actual = SQLNodeConvertEngine.convertToSQLNode(sqlStatement);
+ SqlNode actual = SQLNodeConverterEngine.convertToSQLNode(sqlStatement);
SqlNode expected = parseSqlNode(databaseType, sql);
assertTrue(actual.equalsDeep(expected, Litmus.THROW));
}
- @Ignore
+ @Test
public void assertConvertToSQLStatement() {
SQLParserTestCase expected = SQL_PARSER_TEST_CASES_REGISTRY.get(sqlCaseId);
String databaseType = "H2".equals(this.databaseType) ? "MySQL" : this.databaseType;
String sql = SQL_CASES_LOADER.getCaseValue(sqlCaseId, sqlCaseType, SQL_PARSER_TEST_CASES_REGISTRY.get(sqlCaseId).getParameters());
SqlNode sqlNode = parseSqlNode(databaseType, sql);
- SQLStatement actual = SQLNodeConvertEngine.convertToSQLStatement(sqlNode);
+ SQLStatement actual = SQLNodeConverterEngine.convertToSQLStatement(sqlNode);
SQLStatementAssert.assertIs(new SQLCaseAssertContext(SQL_CASES_LOADER, sqlCaseId, sqlCaseType), actual, expected);
}
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-relation.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-relation.xml
index 2c83782..9c2fd29 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-relation.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-relation.xml
@@ -20,8 +20,8 @@
<sql-case id="select_inner_join_related_with_alias" value="SELECT i.* FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = ?" />
<sql-case id="select_inner_join_related_with_name" value="SELECT t_order_item.* FROM t_order JOIN t_order_item ON t_order.order_id = t_order_item.order_id WHERE t_order.order_id = ?" />
<sql-case id="select_join_using" value="SELECT i.* FROM t_order o JOIN t_order_item i USING(order_id) WHERE o.order_id = ?" db-types="MySQL,PostgreSQL" />
- <sql-case id="select_left_outer_join_related_with_alias" value="SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name" db-types="Oracle" />
- <sql-case id="select_right_outer_join_related_with_alias" value="SELECT d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name" db-types="Oracle" />
+ <sql-case id="select_left_outer_join_related_with_alias" value="SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name" db-types="MySQL, Oracle" />
+ <sql-case id="select_right_outer_join_related_with_alias" value="SELECT d.department_id, e.last_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name" db-types="MySQL, Oracle" />
<sql-case id="select_full_outer_join_related_with_alias" value="SELECT d.department_id AS d_dept_id, e.department_id AS e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id, e.last_name" db-types="Oracle" />
<sql-case id="select_full_outer_join_using_related_with_alias" value="SELECT department_id AS d_e_dept_id, e.last_name FROM departments d FULL OUTER JOIN employees e USING (department_id) ORDER BY department_id, e.last_name" db-types="Oracle" />
<sql-case id="select_cross_apply_join_related_with_alias" value="SELECT d.department_name, v.employee_id, v.last_name FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id" db-types="Oracle" />