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/09/30 08:14:52 UTC
[shardingsphere] branch master updated: support join subquery and
exist subquery convert to sql node (#12848)
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 1454934 support join subquery and exist subquery convert to sql node (#12848)
1454934 is described below
commit 1454934eb8a27d62b496e7c30906d2d3bec549ba
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu Sep 30 16:14:21 2021 +0800
support join subquery and exist subquery convert to sql node (#12848)
* support join subquery and exist subquery convert to sql node
* optimize code
---
.../segment/expression/ExpressionConverter.java | 4 ++
.../impl/ExistsSubqueryExpressionConverter.java} | 21 ++++---
.../segment/from/impl/SubqueryTableConverter.java | 18 +++++-
.../segment/projection/ProjectionsConverter.java | 4 ++
.../impl/ShorthandProjectionConverter.java} | 15 ++---
.../statement/SelectStatementConverter.java | 14 +----
.../statement/SelectStatementConverterTest.java | 65 +++++++++++++++++-----
7 files changed, 98 insertions(+), 43 deletions(-)
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/ExpressionConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/ExpressionConverter.java
index 3e1fc92..2ef03dc 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/ExpressionConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/ExpressionConverter.java
@@ -21,10 +21,12 @@ import org.apache.calcite.sql.SqlNode;
import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl.BinaryOperationExpressionConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl.ColumnConverter;
+import org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl.ExistsSubqueryExpressionConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl.ListExpressionConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl.LiteralExpressionConverter;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.BinaryOperationExpression;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExistsSubqueryExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ListExpression;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
@@ -53,6 +55,8 @@ public final class ExpressionConverter implements SQLSegmentConverter<Expression
return new BinaryOperationExpressionConverter().convert((BinaryOperationExpression) segment);
} else if (segment instanceof ColumnSegment) {
return new ColumnConverter().convert((ColumnSegment) segment);
+ } else if (segment instanceof ExistsSubqueryExpression) {
+ return new ExistsSubqueryExpressionConverter().convert((ExistsSubqueryExpression) segment);
}
throw new UnsupportedOperationException("unsupported TableSegment type: " + segment.getClass());
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ExistsSubqueryExpressionConverter.java
similarity index 50%
copy from shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
copy to shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ExistsSubqueryExpressionConverter.java
index 2439145..f5d37ff 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/expression/impl/ExistsSubqueryExpressionConverter.java
@@ -15,22 +15,29 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.infra.optimize.converter.segment.from.impl;
+package org.apache.shardingsphere.infra.optimize.converter.segment.expression.impl;
+import org.apache.calcite.sql.SqlBasicCall;
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.table.SubqueryTableSegment;
+import org.apache.shardingsphere.infra.optimize.converter.statement.SelectStatementConverter;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExistsSubqueryExpression;
import java.util.Optional;
/**
- * Subquery converter.
+ * Exists subquery expression converter.
*/
-public final class SubqueryTableConverter implements SQLSegmentConverter<SubqueryTableSegment, SqlNode> {
+public final class ExistsSubqueryExpressionConverter implements SQLSegmentConverter<ExistsSubqueryExpression, SqlNode> {
@Override
- public Optional<SqlNode> convert(final SubqueryTableSegment segment) {
- // TODO
- return Optional.empty();
+ public Optional<SqlNode> convert(final ExistsSubqueryExpression expression) {
+ if (null == expression) {
+ return Optional.empty();
+ }
+ SqlBasicCall sqlNode = new SqlBasicCall(SqlStdOperatorTable.EXISTS, new SqlNode[]{new SelectStatementConverter().convert(expression.getSubquery().getSelect())}, SqlParserPos.ZERO);
+ return expression.isNot() ? Optional.of(new SqlBasicCall(SqlStdOperatorTable.NOT, new SqlNode[] {sqlNode}, SqlParserPos.ZERO)) : Optional.of(sqlNode);
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
index 2439145..4bb447d 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
@@ -17,20 +17,32 @@
package org.apache.shardingsphere.infra.optimize.converter.segment.from.impl;
+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.infra.optimize.converter.statement.SelectStatementConverter;
import org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SubqueryTableSegment;
+import java.util.Collection;
+import java.util.LinkedList;
import java.util.Optional;
/**
- * Subquery converter.
+ * Subquery table converter.
*/
public final class SubqueryTableConverter implements SQLSegmentConverter<SubqueryTableSegment, SqlNode> {
@Override
public Optional<SqlNode> convert(final SubqueryTableSegment segment) {
- // TODO
- return Optional.empty();
+ if (null == segment) {
+ return Optional.empty();
+ }
+ Collection<SqlNode> sqlNodes = new LinkedList<>();
+ sqlNodes.add(new SelectStatementConverter().convert(segment.getSubquery().getSelect()));
+ segment.getAlias().ifPresent(optional -> sqlNodes.add(new SqlIdentifier(optional, SqlParserPos.ZERO)));
+ return Optional.of(new SqlBasicCall(SqlStdOperatorTable.AS, sqlNodes.toArray(new SqlNode[]{}), 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 cbe17d1..d8842f2 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
@@ -23,10 +23,12 @@ import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.shardingsphere.infra.optimize.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.projection.impl.ColumnProjectionConverter;
import org.apache.shardingsphere.infra.optimize.converter.segment.projection.impl.ExpressionProjectionConverter;
+import org.apache.shardingsphere.infra.optimize.converter.segment.projection.impl.ShorthandProjectionConverter;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ColumnProjectionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ExpressionProjectionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionSegment;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ProjectionsSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ShorthandProjectionSegment;
import java.util.ArrayList;
import java.util.Collection;
@@ -51,6 +53,8 @@ public final class ProjectionsConverter implements SQLSegmentConverter<Projectio
return new ColumnProjectionConverter().convert((ColumnProjectionSegment) segment);
} else if (segment instanceof ExpressionProjectionSegment) {
return new ExpressionProjectionConverter().convert((ExpressionProjectionSegment) segment);
+ } else if (segment instanceof ShorthandProjectionSegment) {
+ return new ShorthandProjectionConverter().convert((ShorthandProjectionSegment) segment);
}
// 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/from/impl/SubqueryTableConverter.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ShorthandProjectionConverter.java
similarity index 65%
copy from shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
copy to shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ShorthandProjectionConverter.java
index 2439145..5732fd6 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/from/impl/SubqueryTableConverter.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/main/java/org/apache/shardingsphere/infra/optimize/converter/segment/projection/impl/ShorthandProjectionConverter.java
@@ -15,22 +15,23 @@
* limitations under the License.
*/
-package org.apache.shardingsphere.infra.optimize.converter.segment.from.impl;
+package org.apache.shardingsphere.infra.optimize.converter.segment.projection.impl;
+import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlNode;
+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.table.SubqueryTableSegment;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.item.ShorthandProjectionSegment;
import java.util.Optional;
/**
- * Subquery converter.
+ * Shorthand projection converter.
*/
-public final class SubqueryTableConverter implements SQLSegmentConverter<SubqueryTableSegment, SqlNode> {
+public final class ShorthandProjectionConverter implements SQLSegmentConverter<ShorthandProjectionSegment, SqlNode> {
@Override
- public Optional<SqlNode> convert(final SubqueryTableSegment segment) {
- // TODO
- return Optional.empty();
+ public Optional<SqlNode> convert(final ShorthandProjectionSegment segment) {
+ return null == segment ? Optional.empty() : Optional.of(SqlIdentifier.star(SqlParserPos.ZERO));
}
}
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 dff2707..51253d7 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
@@ -55,17 +55,7 @@ public final class SelectStatementConverter implements SQLStatementConverter<Sel
Optional<LimitSegment> limit = SelectStatementHandler.getLimitSegment(selectStatement);
Optional<SqlNode> offset = new OffsetConverter().convert(limit.orElse(null));
Optional<SqlNode> rowCount = new RowCountConverter().convert(limit.orElse(null));
- return new SqlSelect(SqlParserPos.ZERO,
- distinct.orElse(null),
- projections.get(),
- from.orElse(null),
- where.orElse(null),
- groupBy.orElse(null),
- having.orElse(null),
- null,
- orderBy.orElse(null),
- offset.orElse(null),
- rowCount.orElse(null),
- null);
+ return new SqlSelect(SqlParserPos.ZERO, distinct.orElse(null), projections.get(), from.orElse(null), where.orElse(null), groupBy.orElse(null),
+ having.orElse(null), SqlNodeList.EMPTY, orderBy.orElse(null), offset.orElse(null), rowCount.orElse(null), SqlNodeList.EMPTY);
}
}
diff --git a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverterTest.java b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverterTest.java
index ab0b1bf..a92e0fe 100644
--- a/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverterTest.java
+++ b/shardingsphere-infra/shardingsphere-infra-optimize/src/test/java/org/apache/shardingsphere/infra/optimize/converter/statement/SelectStatementConverterTest.java
@@ -18,6 +18,7 @@
package org.apache.shardingsphere.infra.optimize.converter.statement;
import lombok.SneakyThrows;
+import org.apache.calcite.config.Lex;
import org.apache.calcite.sql.SqlJoin;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlSelect;
@@ -25,6 +26,7 @@ import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParser.Config;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
+import org.apache.calcite.util.Litmus;
import org.apache.shardingsphere.infra.config.properties.ConfigurationProperties;
import org.apache.shardingsphere.infra.database.type.DatabaseType;
import org.apache.shardingsphere.infra.database.type.DatabaseTypeRegistry;
@@ -44,6 +46,7 @@ import static org.hamcrest.CoreMatchers.is;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertThat;
+import static org.junit.Assert.assertTrue;
public final class SelectStatementConverterTest {
@@ -57,7 +60,7 @@ public final class SelectStatementConverterTest {
@Test
public void assertConvertSimpleSelect() {
String sql = "select order_id, user_id from t_order";
- SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql.toUpperCase(), false);
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
SqlNode sqlNode = SQLNodeConvertEngine.convert(sqlStatement);
assertThat(sqlNode, instanceOf(SqlSelect.class));
SqlSelect sqlSelect = (SqlSelect) sqlNode;
@@ -65,7 +68,7 @@ public final class SelectStatementConverterTest {
assertNull(sqlSelect.getWhere());
assertNull(sqlSelect.getOffset());
assertNull(sqlSelect.getFetch());
- SqlNode calciteSqlNode = parseByCalciteParser(sql, new MySQLDatabaseType());
+ SqlNode calciteSqlNode = parse(sql, new MySQLDatabaseType());
assertNotNull(calciteSqlNode);
assertThat(sqlNode.toString(), is(calciteSqlNode.toString()));
}
@@ -73,7 +76,7 @@ public final class SelectStatementConverterTest {
@Test
public void assertConvertLimit() {
String sql = "select order_id, user_id from t_order limit 1, 2";
- SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql.toUpperCase(), false);
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
SqlNode sqlNode = SQLNodeConvertEngine.convert(sqlStatement);
assertThat(sqlNode, instanceOf(SqlSelect.class));
SqlSelect sqlSelect = (SqlSelect) sqlNode;
@@ -81,7 +84,7 @@ public final class SelectStatementConverterTest {
assertNull(sqlSelect.getWhere());
assertNotNull(sqlSelect.getOffset());
assertNotNull(sqlSelect.getFetch());
- SqlNode calciteSqlNode = parseByCalciteParser(sql, new MySQLDatabaseType());
+ SqlNode calciteSqlNode = parse(sql, new MySQLDatabaseType());
assertNotNull(calciteSqlNode);
assertThat(sqlNode.toString(), is(calciteSqlNode.toString()));
}
@@ -89,7 +92,7 @@ public final class SelectStatementConverterTest {
@Test
public void assertConvertLimitWithRowCountOnly() {
String sql = "select order_id, user_id from t_order limit 2";
- SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql.toUpperCase(), false);
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
SqlNode sqlNode = SQLNodeConvertEngine.convert(sqlStatement);
assertThat(sqlNode, instanceOf(SqlSelect.class));
SqlSelect sqlSelect = (SqlSelect) sqlNode;
@@ -97,7 +100,7 @@ public final class SelectStatementConverterTest {
assertNull(sqlSelect.getWhere());
assertNull(sqlSelect.getOffset());
assertNotNull(sqlSelect.getFetch());
- SqlNode calciteSqlNode = parseByCalciteParser(sql, new MySQLDatabaseType());
+ SqlNode calciteSqlNode = parse(sql, new MySQLDatabaseType());
assertNotNull(calciteSqlNode);
assertThat(sqlNode.toString(), is(calciteSqlNode.toString()));
}
@@ -105,13 +108,13 @@ public final class SelectStatementConverterTest {
@Test
public void assertConvertWhere() {
String sql = "select order_id, user_id from t_order where order_id = 10";
- SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql.toUpperCase(), false);
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
SqlNode sqlNode = SQLNodeConvertEngine.convert(sqlStatement);
assertThat(sqlNode, instanceOf(SqlSelect.class));
SqlSelect sqlSelect = (SqlSelect) sqlNode;
assertThat(sqlSelect.getSelectList().size(), is(2));
assertNotNull(sqlSelect.getWhere());
- SqlNode calciteSqlNode = parseByCalciteParser(sql, new MySQLDatabaseType());
+ SqlNode calciteSqlNode = parse(sql, new MySQLDatabaseType());
assertNotNull(calciteSqlNode);
assertThat(sqlNode.toString(), is(calciteSqlNode.toString()));
}
@@ -119,14 +122,14 @@ public final class SelectStatementConverterTest {
@Test
public void assertConvertWhereAndGroupBy() {
String sql = "select order_id, user_id from t_order where order_id = 10 group by order_id";
- SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql.toUpperCase(), false);
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
SqlNode sqlNode = SQLNodeConvertEngine.convert(sqlStatement);
assertThat(sqlNode, instanceOf(SqlSelect.class));
SqlSelect sqlSelect = (SqlSelect) sqlNode;
assertThat(sqlSelect.getSelectList().size(), is(2));
assertNotNull(sqlSelect.getWhere());
assertThat(sqlSelect.getGroup().size(), is(1));
- SqlNode calciteSqlNode = parseByCalciteParser(sql, new MySQLDatabaseType());
+ SqlNode calciteSqlNode = parse(sql, new MySQLDatabaseType());
assertNotNull(calciteSqlNode);
assertThat(sqlNode.toString(), is(calciteSqlNode.toString()));
}
@@ -134,14 +137,14 @@ public final class SelectStatementConverterTest {
@Test
public void assertConvertWhereAndOrderBy() {
String sql = "select order_id, user_id from t_order where user_id = 10 order by order_id desc";
- SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql.toUpperCase(), false);
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
SqlNode sqlNode = SQLNodeConvertEngine.convert(sqlStatement);
assertThat(sqlNode, instanceOf(SqlSelect.class));
SqlSelect sqlSelect = (SqlSelect) sqlNode;
assertThat(sqlSelect.getSelectList().size(), is(2));
assertNotNull(sqlSelect.getWhere());
assertThat(sqlSelect.getOrderList().size(), is(1));
- SqlNode calciteSqlNode = parseByCalciteParser(sql, new MySQLDatabaseType());
+ SqlNode calciteSqlNode = parse(sql, new MySQLDatabaseType());
assertNotNull(calciteSqlNode);
assertThat(sqlNode.toString(), is(calciteSqlNode.toString()));
}
@@ -171,9 +174,43 @@ public final class SelectStatementConverterTest {
// TODO outer join is not supported by parser of ShardingSphere
}
+ @Test
+ public void assertConvertExistSubquery() {
+ String sql = "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)";
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
+ SqlNode expected = parse(sql, new MySQLDatabaseType());
+ SqlNode actual = SQLNodeConvertEngine.convert(sqlStatement);
+ assertTrue(expected.equalsDeep(actual, Litmus.THROW));
+ }
+
+ @Test
+ public void assertConvertNotExistSubquery() {
+ String sql = "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)";
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
+ SqlNode expected = parse(sql, new MySQLDatabaseType());
+ SqlNode actual = SQLNodeConvertEngine.convert(sqlStatement);
+ assertTrue(expected.equalsDeep(actual, Litmus.THROW));
+ }
+
+ @Test
+ public void assertConvertJoinSubquery() {
+ String sql = "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";
+ SQLStatement sqlStatement = sqlStatementParserEngine.parse(sql, false);
+ SqlNode expected = parse(sql, new MySQLDatabaseType());
+ SqlNode actual = SQLNodeConvertEngine.convert(sqlStatement);
+ assertTrue(expected.equalsDeep(actual, Litmus.THROW));
+ }
+
@SneakyThrows(SqlParseException.class)
- protected SqlNode parseByCalciteParser(final String sql, final DatabaseType databaseType) {
- return SqlParser.create(sql, Config.DEFAULT.withConformance(getSQLConformance(databaseType))).parseQuery();
+ private SqlNode parse(final String sql, final DatabaseType databaseType) {
+ return SqlParser.create(sql, Config.DEFAULT.withConformance(getSQLConformance(databaseType)).withLex(getLex(databaseType))).parseQuery();
+ }
+
+ private Lex getLex(final DatabaseType databaseType) {
+ return databaseType instanceof MySQLDatabaseType ? Lex.MYSQL : Lex.ORACLE;
}
private SqlConformanceEnum getSQLConformance(final DatabaseType databaseType) {