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/20 09:37:37 UTC
[shardingsphere] branch master updated: Move sql node convert unit
test to independent optimize test module (#13172)
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 a83e29f Move sql node convert unit test to independent optimize test module (#13172)
a83e29f is described below
commit a83e29f9958d5532a9ccf321b1e81fa4147bdcf6
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Wed Oct 20 17:36:54 2021 +0800
Move sql node convert unit test to independent optimize test module (#13172)
* add some case for sql node convert to sql statement
* add optimize test module
* add supported sql convert case
* fix checkstyle
* optimize code
* optimize code
* fix checkstyle
---
.../segment/expression/impl/ColumnConverter.java | 9 +-
.../converter/segment/from/TableConverter.java | 9 +
.../segment/from/impl/JoinTableConverter.java | 12 +-
.../segment/projection/ProjectionsConverter.java | 31 ++-
.../projection/impl/ColumnProjectionConverter.java | 2 +-
.../impl/ExpressionProjectionConverter.java | 5 +
.../statement/SelectStatementConverter.java | 20 +-
.../parameterized/jaxb/SQLNodeConvertCase.java | 43 ----
.../parameterized/jaxb/SQLNodeConvertCases.java | 40 ---
.../jaxb/SQLNodeConvertCasesRegistry.java | 47 ----
.../loader/SQLNodeConvertCasesLoader.java | 146 -----------
.../test/resources/converter/supported/select.xml | 34 ---
shardingsphere-test/pom.xml | 1 +
.../shardingsphere-optimize-test/pom.xml | 71 ++++++
.../SQLNodeConvertEngineParameterizedTest.java | 80 ++++--
.../main/resources/case/dml/select-pagination.xml | 27 ++
.../main/resources/case/dml/select-sub-query.xml | 281 +++++++++++++++++++++
.../sql/supported/dml/select-pagination.xml | 2 +
.../sql/supported/dml/select-sub-query.xml | 6 +
.../main/resources/sql/supported/dml/select.xml | 2 +-
20 files changed, 517 insertions(+), 351 deletions(-)
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ColumnConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ColumnConverter.java
index 9eed5b7..fc43323 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ColumnConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ColumnConverter.java
@@ -17,7 +17,6 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl;
-import com.google.common.collect.ImmutableList;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParserPos;
@@ -25,6 +24,7 @@ import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConv
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment;
+import java.util.Arrays;
import java.util.Optional;
/**
@@ -36,10 +36,9 @@ public final class ColumnConverter implements SQLSegmentConverter<ColumnSegment,
public Optional<SqlNode> convertToSQLNode(final ColumnSegment segment) {
Optional<OwnerSegment> owner = segment.getOwner();
String columnName = segment.getIdentifier().getValue();
- if (owner.isPresent()) {
- return Optional.of(new SqlIdentifier(ImmutableList.of(owner.get().getIdentifier().getValue(), columnName), SqlParserPos.ZERO));
- }
- return Optional.of(new SqlIdentifier(columnName, SqlParserPos.ZERO));
+ SqlIdentifier sqlIdentifier = owner.map(optional
+ -> new SqlIdentifier(Arrays.asList(optional.getIdentifier().getValue(), columnName), SqlParserPos.ZERO)).orElseGet(() -> new SqlIdentifier(columnName, SqlParserPos.ZERO));
+ return Optional.of(sqlIdentifier);
}
@Override
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 d1d3ce5..954e6d8 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
@@ -17,6 +17,9 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.from;
+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.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.from.impl.JoinTableConverter;
@@ -48,6 +51,12 @@ public final class TableConverter implements SQLSegmentConverter<TableSegment, S
@Override
public Optional<TableSegment> convertToSQLSegment(final SqlNode sqlNode) {
+ if (sqlNode instanceof SqlBasicCall || sqlNode instanceof SqlIdentifier) {
+ return new SimpleTableConverter().convertToSQLSegment(sqlNode).map(optional -> optional);
+ }
+ if (sqlNode instanceof SqlJoin) {
+ return new JoinTableConverter().convertToSQLSegment(sqlNode).map(optional -> optional);
+ }
return Optional.empty();
}
}
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 1387ec9..4160080 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
@@ -17,7 +17,6 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.from.impl;
-import com.google.common.base.Preconditions;
import org.apache.calcite.sql.JoinConditionType;
import org.apache.calcite.sql.JoinType;
import org.apache.calcite.sql.SqlJoin;
@@ -28,7 +27,6 @@ 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;
@@ -47,8 +45,8 @@ public final class JoinTableConverter implements SQLSegmentConverter<JoinTableSe
@Override
public Optional<SqlNode> convertToSQLNode(final JoinTableSegment segment) {
- SqlNode left = convertJoinedTable(segment.getLeft());
- SqlNode right = convertJoinedTable(segment.getRight());
+ SqlNode left = new TableConverter().convertToSQLNode(segment.getLeft()).orElseThrow(IllegalStateException::new);
+ SqlNode right = new TableConverter().convertToSQLNode(segment.getRight()).orElseThrow(IllegalStateException::new);
Optional<SqlNode> condition = new ExpressionConverter().convertToSQLNode(segment.getCondition());
SqlLiteral conditionType = condition.isPresent() ? JoinConditionType.ON.symbol(SqlParserPos.ZERO) : JoinConditionType.NONE.symbol(SqlParserPos.ZERO);
return Optional.of(
@@ -60,12 +58,6 @@ public final class JoinTableConverter implements SQLSegmentConverter<JoinTableSe
return Optional.empty();
}
- private SqlNode convertJoinedTable(final TableSegment segment) {
- Optional<SqlNode> result = new TableConverter().convertToSQLNode(segment);
- Preconditions.checkState(result.isPresent());
- return result.get();
- }
-
private SqlLiteral convertJoinType(final String joinType) {
if (null == joinType) {
return JoinType.COMMA.symbol(SqlParserPos.ZERO);
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/ProjectionsConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/ProjectionsConverter.java
index bf7905b..7c28332 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/ProjectionsConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/ProjectionsConverter.java
@@ -17,6 +17,8 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.projection;
+import org.apache.calcite.sql.SqlBasicCall;
+import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.parser.SqlParserPos;
@@ -36,6 +38,7 @@ import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.Subquery
import java.util.ArrayList;
import java.util.Collection;
+import java.util.LinkedList;
import java.util.Optional;
/**
@@ -52,11 +55,6 @@ public final class ProjectionsConverter implements SQLSegmentConverter<Projectio
return Optional.of(new SqlNodeList(projectionSQLNodes, SqlParserPos.ZERO));
}
- @Override
- public Optional<ProjectionsSegment> convertToSQLSegment(final SqlNodeList sqlNode) {
- return Optional.empty();
- }
-
private Optional<SqlNode> getProjectionSQLNode(final ProjectionSegment segment) {
if (segment instanceof ColumnProjectionSegment) {
return new ColumnProjectionConverter().convertToSQLNode((ColumnProjectionSegment) segment);
@@ -72,4 +70,27 @@ public final class ProjectionsConverter implements SQLSegmentConverter<Projectio
// TODO process other projection
return Optional.empty();
}
+
+ @Override
+ public Optional<ProjectionsSegment> convertToSQLSegment(final SqlNodeList sqlNode) {
+ Collection<ProjectionSegment> projections = new LinkedList<>();
+ for (SqlNode each : sqlNode) {
+ getProjectionSegment(each).ifPresent(projections::add);
+ }
+ int startIndex = sqlNode.get(0).getParserPosition().getColumnNum() - 1;
+ int stopIndex = sqlNode.get(sqlNode.size() - 1).getParserPosition().getEndColumnNum() - 1;
+ ProjectionsSegment result = new ProjectionsSegment(startIndex, stopIndex);
+ result.getProjections().addAll(projections);
+ return Optional.of(result);
+ }
+
+ private Optional<ProjectionSegment> getProjectionSegment(final SqlNode sqlNode) {
+ if (sqlNode instanceof SqlIdentifier) {
+ return new ColumnProjectionConverter().convertToSQLSegment(sqlNode).map(optional -> optional);
+ } else if (sqlNode instanceof SqlBasicCall) {
+ return new ExpressionProjectionConverter().convertToSQLSegment(sqlNode).map(optional -> optional);
+ }
+ // TODO process other projection
+ return Optional.empty();
+ }
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ColumnProjectionConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ColumnProjectionConverter.java
index 904f045..1a0de30 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ColumnProjectionConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ColumnProjectionConverter.java
@@ -36,6 +36,6 @@ public final class ColumnProjectionConverter implements SQLSegmentConverter<Colu
@Override
public Optional<ColumnProjectionSegment> convertToSQLSegment(final SqlNode sqlNode) {
- return Optional.empty();
+ return new ColumnConverter().convertToSQLSegment(sqlNode).map(ColumnProjectionSegment::new);
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ExpressionProjectionConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ExpressionProjectionConverter.java
index 5b160f9..542c10b 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ExpressionProjectionConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ExpressionProjectionConverter.java
@@ -17,6 +17,7 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.projection.impl;
+import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlNode;
import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.expression.ExpressionConverter;
@@ -36,6 +37,10 @@ public final class ExpressionProjectionConverter implements SQLSegmentConverter<
@Override
public Optional<ExpressionProjectionSegment> convertToSQLSegment(final SqlNode sqlNode) {
+ if (sqlNode instanceof SqlBasicCall) {
+ return Optional.of(new ExpressionProjectionSegment(sqlNode.getParserPosition().getColumnNum() - 1,
+ sqlNode.getParserPosition().getEndColumnNum() - 1, sqlNode.toString(), new ExpressionConverter().convertToSQLSegment(sqlNode).orElse(null)));
+ }
return Optional.empty();
}
}
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 eafad49..9abfd0b 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
@@ -31,6 +31,7 @@ import org.apache.shardingsphere.infra.optimize.converter.segment.orderby.OrderB
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.dml.item.ProjectionsSegment;
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;
@@ -66,7 +67,22 @@ public final class SelectStatementConverter implements SQLStatementConverter<Sel
@Override
public SelectStatement convertToSQLStatement(final SqlNode sqlNode) {
- // TODO support sql node convert to sql statement
- return new MySQLSelectStatement();
+ SqlSelect sqlSelect = sqlNode instanceof SqlOrderBy ? (SqlSelect) ((SqlOrderBy) sqlNode).query : (SqlSelect) sqlNode;
+ ProjectionsSegment projections = new ProjectionsConverter().convertToSQLSegment(sqlSelect.getSelectList()).orElseThrow(IllegalStateException::new);
+ projections.setDistinctRow(sqlSelect.isDistinct());
+ // TODO create select statement for different dialect
+ MySQLSelectStatement result = new MySQLSelectStatement();
+ result.setProjections(projections);
+ new TableConverter().convertToSQLSegment(sqlSelect.getFrom()).ifPresent(result::setFrom);
+ new WhereConverter().convertToSQLSegment(sqlSelect.getWhere()).ifPresent(result::setWhere);
+ new GroupByConverter().convertToSQLSegment(sqlSelect.getGroup()).ifPresent(result::setGroupBy);
+ new HavingConverter().convertToSQLSegment(sqlSelect.getHaving()).ifPresent(result::setHaving);
+ 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);
+ }
+ return result;
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCase.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCase.java
deleted file mode 100644
index 05dfbe3..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCase.java
+++ /dev/null
@@ -1,43 +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.parameterized.jaxb;
-
-import lombok.Getter;
-import lombok.Setter;
-
-import javax.xml.bind.annotation.XmlAccessType;
-import javax.xml.bind.annotation.XmlAccessorType;
-import javax.xml.bind.annotation.XmlAttribute;
-
-/**
- * SQL node convert case for xml tag.
- */
-@XmlAccessorType(XmlAccessType.FIELD)
-@Getter
-@Setter
-public final class SQLNodeConvertCase {
-
- @XmlAttribute
- private String id;
-
- @XmlAttribute
- private String value;
-
- @XmlAttribute(name = "db-types")
- private String databaseTypes;
-}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCases.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCases.java
deleted file mode 100644
index e4acca4..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCases.java
+++ /dev/null
@@ -1,40 +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.parameterized.jaxb;
-
-import lombok.Getter;
-
-import javax.xml.bind.annotation.XmlAttribute;
-import javax.xml.bind.annotation.XmlElement;
-import javax.xml.bind.annotation.XmlRootElement;
-import java.util.LinkedList;
-import java.util.List;
-
-/**
- * SQL node convert cases for xml root tag.
- */
-@XmlRootElement(name = "sql-cases")
-@Getter
-public final class SQLNodeConvertCases {
-
- @XmlAttribute(name = "db-types")
- private String databaseTypes;
-
- @XmlElement(name = "sql-case")
- private final List<SQLNodeConvertCase> sqlNodeConvertCases = new LinkedList<>();
-}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCasesRegistry.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCasesRegistry.java
deleted file mode 100644
index fb63419..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/jaxb/SQLNodeConvertCasesRegistry.java
+++ /dev/null
@@ -1,47 +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.parameterized.jaxb;
-
-import lombok.Getter;
-import org.apache.shardingsphere.infra.optimize.converter.parameterized.loader.SQLNodeConvertCasesLoader;
-
-/**
- * SQL node convert cases registry.
- */
-public final class SQLNodeConvertCasesRegistry {
-
- private static final SQLNodeConvertCasesRegistry INSTANCE = new SQLNodeConvertCasesRegistry();
-
- private static final String CASE_PATH = "converter/supported/";
-
- @Getter
- private final SQLNodeConvertCasesLoader sqlNodeConvertCasesLoader;
-
- private SQLNodeConvertCasesRegistry() {
- sqlNodeConvertCasesLoader = new SQLNodeConvertCasesLoader(CASE_PATH);
- }
-
- /**
- * Get singleton instance.
- *
- * @return singleton instance
- */
- public static SQLNodeConvertCasesRegistry getInstance() {
- return INSTANCE;
- }
-}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/loader/SQLNodeConvertCasesLoader.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/loader/SQLNodeConvertCasesLoader.java
deleted file mode 100644
index 7d94714c..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/loader/SQLNodeConvertCasesLoader.java
+++ /dev/null
@@ -1,146 +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.parameterized.loader;
-
-import com.google.common.base.Preconditions;
-import com.google.common.base.Splitter;
-import com.google.common.base.Strings;
-import lombok.SneakyThrows;
-import org.apache.shardingsphere.infra.optimize.converter.parameterized.jaxb.SQLNodeConvertCase;
-import org.apache.shardingsphere.infra.optimize.converter.parameterized.jaxb.SQLNodeConvertCases;
-
-import javax.xml.bind.JAXBContext;
-import javax.xml.bind.JAXBException;
-import java.io.File;
-import java.io.FileInputStream;
-import java.io.IOException;
-import java.io.InputStream;
-import java.net.URISyntaxException;
-import java.net.URL;
-import java.nio.file.FileVisitResult;
-import java.nio.file.Files;
-import java.nio.file.Path;
-import java.nio.file.Paths;
-import java.nio.file.SimpleFileVisitor;
-import java.nio.file.attribute.BasicFileAttributes;
-import java.util.Arrays;
-import java.util.Collection;
-import java.util.Collections;
-import java.util.LinkedList;
-import java.util.Map;
-import java.util.TreeMap;
-
-/**
- * SQL node convert cases loader.
- */
-public final class SQLNodeConvertCasesLoader {
-
- private static final String FILE_EXTENSION = ".xml";
-
- private final Map<String, SQLNodeConvertCase> cases;
-
- public SQLNodeConvertCasesLoader(final String rootDirection) {
- cases = load(rootDirection);
- }
-
- @SneakyThrows({JAXBException.class, IOException.class})
- private Map<String, SQLNodeConvertCase> load(final String path) {
- Map<String, SQLNodeConvertCase> result = new TreeMap<>();
- for (File each : loadFiles(path)) {
- buildCaseMap(result, new FileInputStream(each));
- }
- return result;
- }
-
- @SneakyThrows({URISyntaxException.class, IOException.class})
- private static Collection<File> loadFiles(final String path) {
- URL url = SQLNodeConvertCasesLoader.class.getClassLoader().getResource(path);
- if (null == url) {
- return Collections.emptyList();
- }
- Collection<File> result = new LinkedList<>();
- Files.walkFileTree(Paths.get(url.toURI()), new SimpleFileVisitor<Path>() {
-
- @Override
- public FileVisitResult visitFile(final Path file, final BasicFileAttributes attributes) {
- if (file.toString().endsWith(FILE_EXTENSION)) {
- result.add(file.toFile());
- }
- return FileVisitResult.CONTINUE;
- }
- });
- return result;
- }
-
- private void buildCaseMap(final Map<String, SQLNodeConvertCase> sqlNodeConvertCaseMap, final InputStream inputStream) throws JAXBException {
- SQLNodeConvertCases sqlNodeConvertCases = (SQLNodeConvertCases) JAXBContext.newInstance(SQLNodeConvertCases.class).createUnmarshaller().unmarshal(inputStream);
- for (SQLNodeConvertCase each : sqlNodeConvertCases.getSqlNodeConvertCases()) {
- if (null == each.getDatabaseTypes()) {
- each.setDatabaseTypes(sqlNodeConvertCases.getDatabaseTypes());
- }
- Preconditions.checkState(!sqlNodeConvertCaseMap.containsKey(each.getId()), "Find duplicated SQL node convert case ID: %s", each.getId());
- sqlNodeConvertCaseMap.put(each.getId(), each);
- }
- }
-
- /**
- * Get case value.
- *
- * @param caseId case ID
- * @return case value
- */
- public String getCaseValue(final String caseId) {
- Preconditions.checkState(cases.containsKey(caseId), "Can't find SQL of ID: %s", caseId);
- return cases.get(caseId).getValue();
- }
-
- /**
- * Get test parameters for junit parameterized test cases.
- *
- * @param databaseTypes database types
- * @return test parameters for junit parameterized test cases
- */
- public Collection<Object[]> getTestParameters(final Collection<String> databaseTypes) {
- Collection<Object[]> result = new LinkedList<>();
- for (SQLNodeConvertCase each : cases.values()) {
- result.addAll(getSQLTestParameters(databaseTypes, each));
- }
- return result;
- }
-
- private Collection<Object[]> getSQLTestParameters(final Collection<String> databaseTypes, final SQLNodeConvertCase sqlNodeConvertCase) {
- Collection<Object[]> result = new LinkedList<>();
- for (String each : getDatabaseTypes(sqlNodeConvertCase.getDatabaseTypes())) {
- if (databaseTypes.contains(each)) {
- Object[] parameters = new Object[2];
- parameters[0] = sqlNodeConvertCase.getId();
- parameters[1] = each;
- result.add(parameters);
- }
- }
- return result;
- }
-
- private static Collection<String> getDatabaseTypes(final String databaseTypes) {
- return Strings.isNullOrEmpty(databaseTypes) ? getAllDatabaseTypes() : Splitter.on(',').trimResults().splitToList(databaseTypes);
- }
-
- private static Collection<String> getAllDatabaseTypes() {
- return Arrays.asList("H2", "MySQL", "PostgreSQL", "Oracle", "SQLServer", "SQL92");
- }
-}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/resources/converter/supported/select.xml b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/resources/converter/supported/select.xml
deleted file mode 100644
index 365fa25..0000000
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/resources/converter/supported/select.xml
+++ /dev/null
@@ -1,34 +0,0 @@
-<?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-cases>
- <sql-case id="select_with_join_table_subquery" value="SELECT t_order_federate.order_id, t_order_federate.user_id, u.user_id FROM t_order_federate, (SELECT * FROM t_user_info) as u WHERE t_order_federate.user_id = u.user_id" db-types="MySQL" />
- <sql-case id="select_with_projection_subquery" value="SELECT t_order_federate.order_id, t_order_federate.user_id, (SELECT COUNT(*) FROM t_user_info) FROM t_order_federate" db-types="MySQL" />
- <sql-case id="select_with_in_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE user_id IN (SELECT * FROM t_user_info)" db-types="MySQL" />
- <sql-case id="select_with_between_and_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE user_id BETWEEN (SELECT user_id FROM t_user_info WHERE information = 'before') AND (SELECT user_id FROM t_user_info WHERE information = 'after')" db-types="MySQL" />
- <sql-case id="select_with_exist_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)" db-types="MySQL" />
- <sql-case id="select_with_not_exist_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE NOT EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)" db-types="MySQL" />
- <sql-case id="select_with_simple_table" value="SELECT order_id, user_id FROM t_order" db-types="MySQL" />
- <sql-case id="select_with_limit_offset_and_row_count" value="SELECT order_id, user_id FROM t_order LIMIT 1, 2" db-types="MySQL" />
- <sql-case id="select_with_limit_row_count" value="SELECT order_id, user_id FROM t_order LIMIT 2" db-types="MySQL" />
- <sql-case id="select_with_where_condition" value="SELECT order_id, user_id FROM t_order WHERE order_id = 10" db-types="MySQL" />
- <sql-case id="select_with_where_condition_and_group_by" value="SELECT order_id, user_id FROM t_order WHERE order_id = 10 GROUP BY order_id" db-types="MySQL" />
- <sql-case id="select_with_where_condition_and_order_by" value="SELECT order_id, user_id FROM t_order WHERE user_id = 10 ORDER BY order_id DESC" db-types="MySQL" />
- <sql-case id="select_with_inner_join_and_order_by" value="SELECT 10 + 30, o1.order_id + 10, o1.order_id, o1.user_id, o2.status FROM t_order o1 JOIN t_order_item o2 ON o1.order_id = o2.order_id WHERE o1.status='FINISHED' AND o2.order_item_id > 1024 AND 1=1 ORDER BY o1.order_id DESC" db-types="MySQL" />
- <sql-case id="select_with_left_outer_join_and_order_by" value="SELECT 10 + 30, o1.order_id + 10, o1.order_id, o1.user_id, o2.status FROM t_order o1 LEFT OUTER JOIN t_order_item o2 ON o1.order_id = o2.order_id WHERE o1.status='FINISHED' AND o2.order_item_id > 1024 AND 1=1 ORDER BY o1.order_id DESC" db-types="MySQL" />
-</sql-cases>
diff --git a/shardingsphere-test/pom.xml b/shardingsphere-test/pom.xml
index 2a73ecb..a92f627 100644
--- a/shardingsphere-test/pom.xml
+++ b/shardingsphere-test/pom.xml
@@ -37,6 +37,7 @@
<module>shardingsphere-integration-scaling-test</module>
<module>shardingsphere-rewrite-test</module>
<module>shardingsphere-parser-test</module>
+ <module>shardingsphere-optimize-test</module>
</modules>
<properties>
diff --git a/shardingsphere-test/shardingsphere-optimize-test/pom.xml b/shardingsphere-test/shardingsphere-optimize-test/pom.xml
new file mode 100644
index 0000000..bf7087f
--- /dev/null
+++ b/shardingsphere-test/shardingsphere-optimize-test/pom.xml
@@ -0,0 +1,71 @@
+<?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.
+ -->
+
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+ xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
+ <modelVersion>4.0.0</modelVersion>
+ <parent>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-test</artifactId>
+ <version>5.0.0-RC1-SNAPSHOT</version>
+ </parent>
+ <artifactId>shardingsphere-optimize-test</artifactId>
+ <name>${project.artifactId}</name>
+
+ <properties>
+ <maven.deploy.skip>true</maven.deploy.skip>
+ </properties>
+
+ <dependencies>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-infra-optimize</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-parser-test</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-sql-parser-sql92</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-sql-parser-mysql</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-sql-parser-postgresql</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-sql-parser-oracle</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-sql-parser-sqlserver</artifactId>
+ <version>${project.version}</version>
+ </dependency>
+ </dependencies>
+</project>
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertParameterizedTest.java b/shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java
similarity index 51%
rename from shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertParameterizedTest.java
rename to shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java
index a068770..58cc854 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertParameterizedTest.java
+++ b/shardingsphere-test/shardingsphere-optimize-test/src/test/java/org/apache/shardingsphere/infra/optimize/converter/parameterized/engine/SQLNodeConvertEngineParameterizedTest.java
@@ -32,11 +32,17 @@ 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.parameterized.jaxb.SQLNodeConvertCasesRegistry;
-import org.apache.shardingsphere.infra.optimize.converter.parameterized.loader.SQLNodeConvertCasesLoader;
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;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.SQLCaseAssertContext;
+import org.apache.shardingsphere.test.sql.parser.parameterized.asserts.statement.SQLStatementAssert;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.CasesRegistry;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.SQLParserTestCasesRegistry;
+import org.apache.shardingsphere.test.sql.parser.parameterized.jaxb.cases.SQLParserTestCasesRegistryFactory;
+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;
@@ -45,48 +51,88 @@ import org.junit.runners.Parameterized.Parameters;
import java.util.Arrays;
import java.util.Collection;
+import java.util.HashSet;
+import java.util.LinkedList;
import java.util.Properties;
+import java.util.Set;
-import static org.hamcrest.CoreMatchers.is;
-import static org.junit.Assert.assertThat;
import static org.junit.Assert.assertTrue;
@RunWith(Parameterized.class)
@RequiredArgsConstructor
-public final class SQLNodeConvertParameterizedTest {
+public final class SQLNodeConvertEngineParameterizedTest {
- private static final SQLNodeConvertCasesLoader SQL_NODE_CONVERT_CASES_LOADER = SQLNodeConvertCasesRegistry.getInstance().getSqlNodeConvertCasesLoader();
+ private static final SQLCasesLoader SQL_CASES_LOADER = CasesRegistry.getInstance().getSqlCasesLoader();
- private final String caseId;
+ private static final SQLParserTestCasesRegistry SQL_PARSER_TEST_CASES_REGISTRY = SQLParserTestCasesRegistryFactory.getInstance().getRegistry();
+
+ private static final String SELECT_STATEMENT_PREFIX = "SELECT";
+
+ private static final Set<String> SUPPORTED_SQL_CASE_IDS = new HashSet<>();
+
+ static {
+ SUPPORTED_SQL_CASE_IDS.add("select_with_join_table_subquery");
+ SUPPORTED_SQL_CASE_IDS.add("select_with_projection_subquery");
+ SUPPORTED_SQL_CASE_IDS.add("select_with_in_subquery_condition");
+ SUPPORTED_SQL_CASE_IDS.add("select_with_between_and_subquery_condition");
+ SUPPORTED_SQL_CASE_IDS.add("select_with_exist_subquery_condition");
+ SUPPORTED_SQL_CASE_IDS.add("select_with_not_exist_subquery_condition");
+ SUPPORTED_SQL_CASE_IDS.add("select_with_simple_table");
+ SUPPORTED_SQL_CASE_IDS.add("select_pagination_with_limit_offset_and_row_count");
+ SUPPORTED_SQL_CASE_IDS.add("select_pagination_with_limit_row_count");
+ SUPPORTED_SQL_CASE_IDS.add("select_group_by_with_limit");
+ SUPPORTED_SQL_CASE_IDS.add("select_left_outer_join_related_with_alias");
+ SUPPORTED_SQL_CASE_IDS.add("select_right_outer_join_related_with_alias");
+ }
+
+ private final String sqlCaseId;
private final String databaseType;
- @Parameters(name = "{0} -> {1}")
+ private final SQLCaseType sqlCaseType;
+
+ @Parameters(name = "{0} ({2}) -> {1}")
public static Collection<Object[]> getTestParameters() {
- return SQLNodeConvertParameterizedTest.getTestParameters("H2", "MySQL", "PostgreSQL", "Oracle", "SQLServer", "SQL92");
+ return getTestParameters("MySQL");
}
private static Collection<Object[]> getTestParameters(final String... databaseTypes) {
- return SQL_NODE_CONVERT_CASES_LOADER.getTestParameters(Arrays.asList(databaseTypes));
+ Collection<Object[]> result = new LinkedList<>();
+ for (Object[] each : SQL_CASES_LOADER.getTestParameters(Arrays.asList(databaseTypes))) {
+ if (!isPlaceholderWithoutParameter(each) && isSupportedSQLCase(each)) {
+ result.add(each);
+ }
+ }
+ return result;
+ }
+
+ private static boolean isPlaceholderWithoutParameter(final Object[] sqlTestParameter) {
+ return SQLCaseType.Placeholder == sqlTestParameter[2] && SQL_PARSER_TEST_CASES_REGISTRY.get(sqlTestParameter[0].toString()).getParameters().isEmpty();
+ }
+
+ private static boolean isSupportedSQLCase(final Object[] sqlTestParameter) {
+ String sqlCaseId = sqlTestParameter[0].toString();
+ return sqlCaseId.toUpperCase().startsWith(SELECT_STATEMENT_PREFIX) && SUPPORTED_SQL_CASE_IDS.contains(sqlCaseId);
}
@Test
public void assertConvertToSQLNode() {
String databaseType = "H2".equals(this.databaseType) ? "MySQL" : this.databaseType;
- String sql = SQL_NODE_CONVERT_CASES_LOADER.getCaseValue(caseId);
+ 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 expected = parseSqlNode(databaseType, sql);
- SqlNode actual = SQLNodeConvertEngine.convertToSQLNode(parseSQLStatement(databaseType, sql));
assertTrue(actual.equalsDeep(expected, Litmus.THROW));
}
@Ignore
public void assertConvertToSQLStatement() {
+ SQLParserTestCase expected = SQL_PARSER_TEST_CASES_REGISTRY.get(sqlCaseId);
String databaseType = "H2".equals(this.databaseType) ? "MySQL" : this.databaseType;
- String sql = SQL_NODE_CONVERT_CASES_LOADER.getCaseValue(caseId);
- SQLStatement expected = parseSQLStatement(databaseType, sql);
- SQLStatement actual = SQLNodeConvertEngine.convertToSQLStatement(parseSqlNode(databaseType, sql));
- // TODO optimize assert logic
- assertThat(actual.toString(), is(expected.toString()));
+ 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);
+ SQLStatementAssert.assertIs(new SQLCaseAssertContext(SQL_CASES_LOADER, sqlCaseId, sqlCaseType), actual, expected);
}
@SneakyThrows(SqlParseException.class)
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-pagination.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-pagination.xml
index 9797942..e802705 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-pagination.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select-pagination.xml
@@ -2010,4 +2010,31 @@
<row-count value="20" parameter-index="1" literal-start-index="64" literal-stop-index="65" start-index="64" stop-index="64"/>
</limit>
</select>
+
+ <select sql-case-id="select_pagination_with_limit_offset_and_row_count">
+ <from>
+ <simple-table name="t_order" start-index="30" stop-index="36"/>
+ </from>
+ <projections start-index="7" stop-index="23">
+ <column-projection start-index="7" stop-index="14" name="order_id" />
+ <column-projection start-index="17" stop-index="23" name="user_id" />
+ </projections>
+ <limit start-index="38" stop-index="47">
+ <offset value="1" start-index="44" stop-index="44" />
+ <row-count value="2" start-index="47" stop-index="47" />
+ </limit>
+ </select>
+
+ <select sql-case-id="select_pagination_with_limit_row_count">
+ <from>
+ <simple-table name="t_order" start-index="30" stop-index="36"/>
+ </from>
+ <projections start-index="7" stop-index="23">
+ <column-projection start-index="7" stop-index="14" name="order_id" />
+ <column-projection start-index="17" stop-index="23" name="user_id" />
+ </projections>
+ <limit start-index="38" stop-index="44">
+ <row-count value="2" start-index="44" stop-index="44" />
+ </limit>
+ </select>
</sql-parser-test-cases>
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 5fed181..29e3305 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
@@ -238,4 +238,285 @@
</subquery-projection>
</projections>
</select>
+
+ <select sql-case-id="select_with_join_table_subquery">
+ <projections start-index="7" stop-index="68">
+ <column-projection name="order_id" start-index="7" stop-index="31">
+ <owner start-index="7" stop-index="22" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="34" stop-index="57">
+ <owner start-index="34" stop-index="49" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="60" stop-index="68">
+ <owner start-index="60" stop-index="60" name="u"/>
+ </column-projection>
+ </projections>
+ <from start-index="70" stop-index="90">
+ <join-table>
+ <left>
+ <simple-table name="t_order_federate" start-index="75" stop-index="90" />
+ </left>
+ <right>
+ <subquery-table alias="u">
+ <subquery>
+ <select>
+ <projections start-index="101" stop-index="101">
+ <shorthand-projection start-index="101" stop-index="101" />
+ </projections>
+ <from>
+ <simple-table start-index="108" stop-index="118" name="t_user_info"/>
+ </from>
+ </select>
+ </subquery>
+ </subquery-table>
+ </right>
+ </join-table>
+ </from>
+ <where start-index="126" stop-index="167">
+ <expr>
+ <binary-operation-expression start-index="132" stop-index="167">
+ <left>
+ <column name="user_id" start-index="132" stop-index="155">
+ <owner start-index="132" stop-index="147" name="t_order_federate"/>
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="user_id" start-index="159" stop-index="167">
+ <owner start-index="159" stop-index="159" name="u"/>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_with_projection_subquery">
+ <projections start-index="7" stop-index="93">
+ <column-projection name="order_id" start-index="7" stop-index="31">
+ <owner start-index="7" stop-index="22" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="34" stop-index="57">
+ <owner start-index="34" stop-index="49" name="t_order_federate"/>
+ </column-projection>
+ <subquery-projection start-index="60" stop-index="93" text="(SELECT COUNT(*) FROM t_user_info)">
+ <subquery>
+ <select>
+ <projections start-index="68" stop-index="75">
+ <aggregation-projection type="COUNT" inner-expression="(*)" start-index="68" stop-index="75" />
+ </projections>
+ <from start-index="77" stop-index="80">
+ <simple-table name="t_user_info" start-index="82" stop-index="92" />
+ </from>
+ </select>
+ </subquery>
+ </subquery-projection>
+ </projections>
+ <from start-index="95" stop-index="115">
+ <simple-table name="t_order_federate" start-index="100" stop-index="115" />
+ </from>
+ </select>
+
+ <select sql-case-id="select_with_in_subquery_condition">
+ <projections start-index="7" stop-index="57">
+ <column-projection name="order_id" start-index="7" stop-index="31">
+ <owner start-index="7" stop-index="22" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="34" stop-index="57">
+ <owner start-index="34" stop-index="49" name="t_order_federate"/>
+ </column-projection>
+ </projections>
+ <from start-index="59" stop-index="79">
+ <simple-table name="t_order_federate" start-index="64" stop-index="79" />
+ </from>
+ <where start-index="81" stop-index="124">
+ <expr>
+ <in-expression start-index="87" stop-index="124">
+ <left>
+ <column name="user_id" start-index="87" stop-index="93" />
+ </left>
+ <right>
+ <subquery start-index="98" stop-index="124">
+ <select>
+ <projections start-index="106" stop-index="106">
+ <shorthand-projection start-index="106" stop-index="106" />
+ </projections>
+ <from start-index="108" stop-index="123">
+ <simple-table name="t_user_info" start-index="113" stop-index="123" />
+ </from>
+ </select>
+ </subquery>
+ </right>
+ </in-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_with_between_and_subquery_condition">
+ <projections start-index="7" stop-index="57">
+ <column-projection name="order_id" start-index="7" stop-index="31">
+ <owner start-index="7" stop-index="22" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="34" stop-index="57">
+ <owner start-index="34" stop-index="49" name="t_order_federate"/>
+ </column-projection>
+ </projections>
+ <from start-index="59" stop-index="79">
+ <simple-table name="t_order_federate" start-index="64" stop-index="79" />
+ </from>
+ <where start-index="81" stop-index="230">
+ <expr>
+ <between-expression start-index="87" stop-index="230">
+ <left>
+ <column name="user_id" start-index="87" stop-index="93" />
+ </left>
+ <between-expr>
+ <subquery start-index="103" stop-index="164">
+ <select>
+ <projections start-index="111" stop-index="117">
+ <column-projection name="user_id" start-index="111" stop-index="117" />
+ </projections>
+ <from start-index="119" stop-index="134">
+ <simple-table name="t_user_info" start-index="124" stop-index="134" />
+ </from>
+ <where start-index="136" stop-index="163">
+ <expr>
+ <binary-operation-expression start-index="142" stop-index="163">
+ <left>
+ <column name="information" start-index="142" stop-index="152" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="before" start-index="156" stop-index="163" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </between-expr>
+ <and-expr>
+ <subquery start-index="170" stop-index="230">
+ <select>
+ <projections start-index="178" stop-index="184">
+ <column-projection name="user_id" start-index="178" stop-index="184" />
+ </projections>
+ <from start-index="186" stop-index="201">
+ <simple-table name="t_user_info" start-index="191" stop-index="201" />
+ </from>
+ <where start-index="203" stop-index="229">
+ <expr>
+ <binary-operation-expression start-index="209" stop-index="229">
+ <left>
+ <column name="information" start-index="209" stop-index="219" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="after" start-index="223" stop-index="229" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </and-expr>
+ </between-expression>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_with_exist_subquery_condition">
+ <projections start-index="7" stop-index="57">
+ <column-projection name="order_id" start-index="7" stop-index="31">
+ <owner start-index="7" stop-index="22" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="34" stop-index="57">
+ <owner start-index="34" stop-index="49" name="t_order_federate"/>
+ </column-projection>
+ </projections>
+ <from start-index="59" stop-index="79">
+ <simple-table name="t_order_federate" start-index="64" stop-index="79" />
+ </from>
+ <where start-index="81" stop-index="173">
+ <expr>
+ <exists-subquery start-index="87" stop-index="173">
+ <subquery start-index="94" stop-index="173">
+ <select>
+ <projections start-index="102" stop-index="102">
+ <shorthand-projection start-index="102" stop-index="102" />
+ </projections>
+ <from start-index="104" stop-index="119">
+ <simple-table name="t_user_info" start-index="109" stop-index="119" />
+ </from>
+ <where start-index="121" stop-index="172">
+ <expr>
+ <binary-operation-expression start-index="127" stop-index="172">
+ <left>
+ <column name="user_id" start-index="127" stop-index="150">
+ <owner start-index="127" stop-index="142" name="t_order_federate"/>
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="user_id" start-index="154" stop-index="172">
+ <owner start-index="154" stop-index="164" name="t_user_info"/>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </exists-subquery>
+ </expr>
+ </where>
+ </select>
+
+ <select sql-case-id="select_with_not_exist_subquery_condition">
+ <projections start-index="7" stop-index="57">
+ <column-projection name="order_id" start-index="7" stop-index="31">
+ <owner start-index="7" stop-index="22" name="t_order_federate"/>
+ </column-projection>
+ <column-projection name="user_id" start-index="34" stop-index="57">
+ <owner start-index="34" stop-index="49" name="t_order_federate"/>
+ </column-projection>
+ </projections>
+ <from start-index="59" stop-index="79">
+ <simple-table name="t_order_federate" start-index="64" stop-index="79" />
+ </from>
+ <where start-index="81" stop-index="177">
+ <expr>
+ <exists-subquery start-index="91" stop-index="177">
+ <not>true</not>
+ <subquery start-index="98" stop-index="177">
+ <select>
+ <projections start-index="106" stop-index="106">
+ <shorthand-projection start-index="106" stop-index="106" />
+ </projections>
+ <from start-index="108" stop-index="123">
+ <simple-table name="t_user_info" start-index="113" stop-index="123" />
+ </from>
+ <where start-index="125" stop-index="176">
+ <expr>
+ <binary-operation-expression start-index="131" stop-index="176">
+ <left>
+ <column name="user_id" start-index="131" stop-index="154">
+ <owner start-index="131" stop-index="146" name="t_order_federate"/>
+ </column>
+ </left>
+ <operator>=</operator>
+ <right>
+ <column name="user_id" start-index="158" stop-index="176">
+ <owner start-index="158" stop-index="168" name="t_user_info"/>
+ </column>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery>
+ </exists-subquery>
+ </expr>
+ </where>
+ </select>
</sql-parser-test-cases>
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml
index fdced91..fbbf5b8 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select-pagination.xml
@@ -35,4 +35,6 @@
<sql-case id="select_pagination_with_row_number_not_at_end" value="SELECT * FROM t_order WHERE ROWNUM <= ? ORDER BY order_id" db-types="Oracle" />
<sql-case id="select_pagination_with_fetch_first_with_row_number" value="SELECT * FROM t_order ORDER BY order_id FETCH FIRST 5 ROWS ONLY" db-types="Oracle" />
<sql-case id="select_pagination_with_offset_fetch" value="SELECT * FROM t_order ORDER BY order_id OFFSET 0 ROW FETCH NEXT ? ROWS ONLY" db-types="SQLServer" />
+ <sql-case id="select_pagination_with_limit_offset_and_row_count" value="SELECT order_id, user_id FROM t_order LIMIT 1, 2" db-types="MySQL" />
+ <sql-case id="select_pagination_with_limit_row_count" value="SELECT order_id, user_id FROM t_order LIMIT 2" db-types="MySQL" />
</sql-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 24ec921..6da3e1c 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
@@ -23,4 +23,10 @@
<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-case id="select_with_join_table_subquery" value="SELECT t_order_federate.order_id, t_order_federate.user_id, u.user_id FROM t_order_federate, (SELECT * FROM t_user_info) as u WHERE t_order_federate.user_id = u.user_id" db-types="MySQL, PostgreSQL, SQLServer, SQL92" />
+ <sql-case id="select_with_projection_subquery" value="SELECT t_order_federate.order_id, t_order_federate.user_id, (SELECT COUNT(*) FROM t_user_info) FROM t_order_federate" />
+ <sql-case id="select_with_in_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE user_id IN (SELECT * FROM t_user_info)" />
+ <sql-case id="select_with_between_and_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE user_id BETWEEN (SELECT user_id FROM t_user_info WHERE information = 'before') AND (SELECT user_id FROM t_user_info WHERE information = 'after')" />
+ <sql-case id="select_with_exist_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)" db-types="MySQL, PostgreSQL" />
+ <sql-case id="select_with_not_exist_subquery_condition" value="SELECT t_order_federate.order_id, t_order_federate.user_id FROM t_order_federate WHERE NOT EXISTS (SELECT * FROM t_user_info WHERE t_order_federate.user_id = t_user_info.user_id)" db-types="MySQL" />
</sql-cases>
diff --git a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
index 84a65db..b37811b 100644
--- a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
+++ b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
@@ -83,7 +83,7 @@
<sql-case id="select_from_dual" value="SELECT 1 FROM DUAL" db-types="MySQL" />
<sql-case id="select_with_cast_as_signed" value="SELECT user_id,CAST(order_id AS SIGNED) FROM t_order" db-types="MySQL"/>
<sql-case id="select_with_cast_as_unsigned" value="SELECT CAST(order_id AS UNSIGNED),user_id FROM t_order" db-types="MySQL"/>
- <sql-case id="select_with_simple_table" value="SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name" db-types="Oracle" />
+ <sql-case id="select_with_simple_table" value="SELECT * FROM employees WHERE department_id = 30 ORDER BY last_name" db-types="MySQL, Oracle" />
<sql-case id="select_with_binding_tables_with_subquery_without_join" value="SELECT a.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary"
FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b ORDER BY a.department_id" db-types="Oracle" />
<sql-case id="select_with_partitioned_table" value="SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1500 ORDER BY cust_id, time_id, channel_id" db-types="Oracle" />