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 2022/12/15 08:57:59 UTC
[shardingsphere] branch master updated: Support sql federation nulls last, nulls first operator (#22883)
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 0cbb5fb100d Support sql federation nulls last, nulls first operator (#22883)
0cbb5fb100d is described below
commit 0cbb5fb100de531e7e27382904197c1249baf2d6
Author: Zhengqiang Duan <du...@apache.org>
AuthorDate: Thu Dec 15 16:57:45 2022 +0800
Support sql federation nulls last, nulls first operator (#22883)
* Support sql federation nulls last, nulls first operator
* add integration test case
* add integration test case
---
.../orderby/item/ColumnOrderByItemConverter.java | 16 ++++++++++++----
.../orderby/item/ExpressionOrderByItemConverter.java | 18 ++++++++++++++++--
.../orderby/item/IndexOrderByItemConverter.java | 14 ++++++++++----
.../resources/cases/dql/dql-integration-test-cases.xml | 8 ++++++++
.../test/it/optimize/SQLNodeConverterEngineIT.java | 2 ++
5 files changed, 48 insertions(+), 10 deletions(-)
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ColumnOrderByItemConverter.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ColumnOrderByItemConverter.java
index 5e0ab1ba148..257b689ddb0 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ColumnOrderByItemConverter.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ColumnOrderByItemConverter.java
@@ -19,15 +19,16 @@ package org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.orde
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlPostfixOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
-import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.SQLSegmentConverter;
-import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.impl.ColumnConverter;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.NullsOrderType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ColumnOrderByItemSegment;
+import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.SQLSegmentConverter;
+import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.impl.ColumnConverter;
import java.util.Collections;
-import java.util.Objects;
import java.util.Optional;
/**
@@ -38,9 +39,16 @@ public final class ColumnOrderByItemConverter implements SQLSegmentConverter<Col
@Override
public Optional<SqlNode> convert(final ColumnOrderByItemSegment segment) {
Optional<SqlNode> result = new ColumnConverter().convert(segment.getColumn());
- if (result.isPresent() && Objects.equals(OrderDirection.DESC, segment.getOrderDirection())) {
+ if (!result.isPresent()) {
+ return Optional.empty();
+ }
+ if (OrderDirection.DESC.equals(segment.getOrderDirection())) {
result = Optional.of(new SqlBasicCall(SqlStdOperatorTable.DESC, Collections.singletonList(result.get()), SqlParserPos.ZERO));
}
+ if (segment.getNullsOrderType().isPresent()) {
+ SqlPostfixOperator nullsOrderType = NullsOrderType.FIRST.equals(segment.getNullsOrderType().get()) ? SqlStdOperatorTable.NULLS_FIRST : SqlStdOperatorTable.NULLS_LAST;
+ result = Optional.of(new SqlBasicCall(nullsOrderType, Collections.singletonList(result.get()), SqlParserPos.ZERO));
+ }
return result;
}
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ExpressionOrderByItemConverter.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ExpressionOrderByItemConverter.java
index 2cb42ff8e2a..d6d3b4b00e4 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ExpressionOrderByItemConverter.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/ExpressionOrderByItemConverter.java
@@ -17,11 +17,17 @@
package org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.orderby.item;
+import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlPostfixOperator;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.NullsOrderType;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ExpressionOrderByItemSegment;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.SQLSegmentConverter;
import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.expression.ExpressionConverter;
-import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.ExpressionOrderByItemSegment;
+import java.util.Collections;
import java.util.Optional;
/**
@@ -31,6 +37,14 @@ public final class ExpressionOrderByItemConverter implements SQLSegmentConverter
@Override
public Optional<SqlNode> convert(final ExpressionOrderByItemSegment segment) {
- return null == segment ? Optional.empty() : new ExpressionConverter().convert(segment.getExpr());
+ Optional<SqlNode> result = null == segment ? Optional.empty() : new ExpressionConverter().convert(segment.getExpr());
+ if (!result.isPresent()) {
+ return Optional.empty();
+ }
+ if (segment.getNullsOrderType().isPresent()) {
+ SqlPostfixOperator nullsOrderType = NullsOrderType.FIRST.equals(segment.getNullsOrderType().get()) ? SqlStdOperatorTable.NULLS_FIRST : SqlStdOperatorTable.NULLS_LAST;
+ result = Optional.of(new SqlBasicCall(nullsOrderType, Collections.singletonList(result.get()), SqlParserPos.ZERO));
+ }
+ return result;
}
}
diff --git a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/IndexOrderByItemConverter.java b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/IndexOrderByItemConverter.java
index f68a8e67952..f47232b1c60 100644
--- a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/IndexOrderByItemConverter.java
+++ b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/orderby/item/IndexOrderByItemConverter.java
@@ -20,11 +20,13 @@ package org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.orde
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlPostfixOperator;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
-import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.SQLSegmentConverter;
+import org.apache.shardingsphere.sql.parser.sql.common.enums.NullsOrderType;
import org.apache.shardingsphere.sql.parser.sql.common.enums.OrderDirection;
import org.apache.shardingsphere.sql.parser.sql.common.segment.dml.order.item.IndexOrderByItemSegment;
+import org.apache.shardingsphere.sqlfederation.optimizer.converter.segment.SQLSegmentConverter;
import java.util.Collections;
import java.util.Optional;
@@ -36,10 +38,14 @@ public final class IndexOrderByItemConverter implements SQLSegmentConverter<Inde
@Override
public Optional<SqlNode> convert(final IndexOrderByItemSegment segment) {
- SqlNode sqlNode = SqlLiteral.createExactNumeric(String.valueOf(segment.getColumnIndex()), SqlParserPos.ZERO);
+ SqlNode result = SqlLiteral.createExactNumeric(String.valueOf(segment.getColumnIndex()), SqlParserPos.ZERO);
if (OrderDirection.DESC.equals(segment.getOrderDirection())) {
- sqlNode = new SqlBasicCall(SqlStdOperatorTable.DESC, Collections.singletonList(sqlNode), SqlParserPos.ZERO);
+ result = new SqlBasicCall(SqlStdOperatorTable.DESC, Collections.singletonList(result), SqlParserPos.ZERO);
+ }
+ if (segment.getNullsOrderType().isPresent()) {
+ SqlPostfixOperator nullsOrderType = NullsOrderType.FIRST.equals(segment.getNullsOrderType().get()) ? SqlStdOperatorTable.NULLS_FIRST : SqlStdOperatorTable.NULLS_LAST;
+ result = new SqlBasicCall(nullsOrderType, Collections.singletonList(result), SqlParserPos.ZERO);
}
- return Optional.of(sqlNode);
+ return Optional.of(result);
}
}
diff --git a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index 8bfae909e52..d372041739e 100644
--- a/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++ b/test/e2e/suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -1166,4 +1166,12 @@
<test-case sql="SELECT merchant_id, COUNT(1) AS count FROM t_order GROUP BY merchant_id ORDER BY merchant_id DESC NULLS LAST" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
<assertion expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id ASC NULLS FIRST, i.item_id DESC" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id ORDER BY o.order_id ASC NULLS LAST, i.item_id DESC" db-types="PostgreSQL,openGauss,Oracle" scenario-types="db">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
</integration-test-cases>
diff --git a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
index b86bf94e556..a10fda84aa4 100644
--- a/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
+++ b/test/it/optimizer/src/test/java/org/apache/shardingsphere/test/it/optimize/SQLNodeConverterEngineIT.java
@@ -153,6 +153,8 @@ public final class SQLNodeConverterEngineIT {
SUPPORTED_SQL_CASE_IDS.add("select_natural_left_join");
SUPPORTED_SQL_CASE_IDS.add("select_natural_right_join");
SUPPORTED_SQL_CASE_IDS.add("select_natural_full_join");
+ SUPPORTED_SQL_CASE_IDS.add("select_order_by_for_nulls_first");
+ SUPPORTED_SQL_CASE_IDS.add("select_order_by_for_nulls_last");
}
// CHECKSTYLE:ON