This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 8491a00b067 Support for federated query NOT operator (#32961)
8491a00b067 is described below
commit 8491a00b06739c8ab87eec83e25c00c5d61df4c0
Author: niu niu <[email protected]>
AuthorDate: Mon Sep 23 17:43:02 2024 +0800
Support for federated query NOT operator (#32961)
* Support for federated query NOT operator
* Improve not operator sql node converter
* Format code
---
.../operator/common/SQLExtensionOperatorTable.java | 5 ++
.../impl/LiteralExpressionConverter.java | 3 +
.../expression/impl/NotExpressionConverter.java | 3 +-
.../function/mysql/MySQLOperatorTable.java | 34 +++++++++++
.../function/mysql/impl/MySQLNotFunction.java | 69 ++++++++++++++++++++++
.../planner/util/SQLFederationPlannerUtils.java | 3 +-
.../test/resources/cases/dql/e2e-dql-select.xml | 16 +++++
.../src/test/resources/converter/select.xml | 2 +
.../parser/src/main/resources/case/dml/select.xml | 55 +++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 2 +
10 files changed, 189 insertions(+), 3 deletions(-)
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/operator/common/SQLExtensionOperatorTable.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/operator/common/SQLExtensionOperatorTable.java
index cfc8d3ced63..110f80291c1 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/operator/common/SQLExtensionOperatorTable.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/operator/common/SQLExtensionOperatorTable.java
@@ -22,6 +22,9 @@ import lombok.NoArgsConstructor;
import org.apache.calcite.sql.SqlBinaryOperator;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlPrefixOperator;
+import org.apache.calcite.sql.type.InferTypes;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.operator.dialect.mysql.MySQLMatchAgainstOperator;
/**
@@ -60,5 +63,7 @@ public final class SQLExtensionOperatorTable {
public static final SqlPrefixOperator TILDE = new SqlPrefixOperator("~",
SqlKind.OTHER, 26, null, null, null);
+ public static final SqlPrefixOperator NOT = new SqlPrefixOperator("NOT",
SqlKind.NOT, 26, ReturnTypes.BIGINT, InferTypes.BOOLEAN, OperandTypes.ANY);
+
public static final MySQLMatchAgainstOperator MATCH_AGAINST = new
MySQLMatchAgainstOperator();
}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
index 63ef34b78a5..ad63003be69 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/LiteralExpressionConverter.java
@@ -76,6 +76,9 @@ public final class LiteralExpressionConverter {
if (segment.getLiterals() instanceof String) {
return Optional.of(SqlLiteral.createCharString(literalValue,
SqlParserPos.ZERO));
}
+ if (segment.getLiterals() instanceof Boolean) {
+ return
Optional.of(SqlLiteral.createBoolean(Boolean.parseBoolean(literalValue),
SqlParserPos.ZERO));
+ }
return Optional.empty();
}
}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/NotExpressionConverter.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/NotExpressionConverter.java
index 6588f4473f8..f63f93c2c78 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/NotExpressionConverter.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/converter/segment/expression/impl/NotExpressionConverter.java
@@ -21,7 +21,6 @@ import lombok.AccessLevel;
import lombok.NoArgsConstructor;
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.sql.parser.statement.core.segment.dml.expr.NotExpression;
import
org.apache.shardingsphere.sqlfederation.optimizer.converter.operator.common.SQLExtensionOperatorTable;
@@ -50,6 +49,6 @@ public final class NotExpressionConverter {
if (segment.getNotSign().equals(true)) {
return Optional.of(new
SqlBasicCall(SQLExtensionOperatorTable.NOT_SIGN, sqlNodes, SqlParserPos.ZERO));
}
- return Optional.of(new SqlBasicCall(SqlStdOperatorTable.NOT, sqlNodes,
SqlParserPos.ZERO));
+ return Optional.of(new SqlBasicCall(SQLExtensionOperatorTable.NOT,
sqlNodes, SqlParserPos.ZERO));
}
}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/MySQLOperatorTable.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/MySQLOperatorTable.java
new file mode 100644
index 00000000000..558921e0a6d
--- /dev/null
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/MySQLOperatorTable.java
@@ -0,0 +1,34 @@
+/*
+ * 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.sqlfederation.optimizer.function.mysql;
+
+import org.apache.calcite.sql.util.ReflectiveSqlOperatorTable;
+import org.apache.calcite.sql.validate.SqlUserDefinedFunction;
+import
org.apache.shardingsphere.sqlfederation.optimizer.function.mysql.impl.MySQLNotFunction;
+
+/**
+ * MySQL operator table.
+ */
+public final class MySQLOperatorTable extends ReflectiveSqlOperatorTable {
+
+ public static final SqlUserDefinedFunction NOT = new MySQLNotFunction();
+
+ public MySQLOperatorTable() {
+ init();
+ }
+}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/impl/MySQLNotFunction.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/impl/MySQLNotFunction.java
new file mode 100644
index 00000000000..986e78efed3
--- /dev/null
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/function/mysql/impl/MySQLNotFunction.java
@@ -0,0 +1,69 @@
+/*
+ * 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.sqlfederation.optimizer.function.mysql.impl;
+
+import com.google.common.collect.ImmutableList;
+import org.apache.calcite.schema.impl.ScalarFunctionImpl;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.InferTypes;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.validate.SqlUserDefinedFunction;
+
+import java.util.Collections;
+
+/**
+ * MySQL not function.
+ */
+public final class MySQLNotFunction extends SqlUserDefinedFunction {
+
+ public MySQLNotFunction() {
+ super(new SqlIdentifier("NOT", SqlParserPos.ZERO), SqlKind.NOT,
ReturnTypes.BIGINT_NULLABLE, InferTypes.BOOLEAN,
+
OperandTypes.operandMetadata(Collections.singletonList(SqlTypeFamily.ANY),
typeFactory -> ImmutableList.of(typeFactory.createSqlType(SqlTypeName.BIGINT)),
null, arg -> false),
+ ScalarFunctionImpl.create(MySQLNotFunction.class, "not"));
+ }
+
+ @Override
+ public SqlSyntax getSyntax() {
+ return SqlSyntax.PREFIX;
+ }
+
+ /**
+ * not.
+ *
+ * @param value value
+ * @return not operator result
+ */
+ public static Long not(final Object value) {
+ if (null == value) {
+ return null;
+ }
+ if (value instanceof Number) {
+ return ((Number) value).longValue() == 0 ? 1L : 0L;
+ }
+ if (value instanceof Boolean) {
+ return ((Boolean) value) ? 0L : 1L;
+ }
+ return null;
+ }
+}
diff --git
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationPlannerUtils.java
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationPlannerUtils.java
index 6701c4a729f..b6b94855efb 100644
---
a/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationPlannerUtils.java
+++
b/kernel/sql-federation/optimizer/src/main/java/org/apache/shardingsphere/sqlfederation/optimizer/planner/util/SQLFederationPlannerUtils.java
@@ -51,6 +51,7 @@ import org.apache.calcite.sql2rel.StandardConvertletTable;
import
org.apache.shardingsphere.infra.database.core.spi.DatabaseTypedSPILoader;
import org.apache.shardingsphere.infra.database.core.type.DatabaseType;
import org.apache.shardingsphere.parser.rule.SQLParserRule;
+import
org.apache.shardingsphere.sqlfederation.optimizer.function.mysql.MySQLOperatorTable;
import
org.apache.shardingsphere.sqlfederation.optimizer.function.SQLFederationFunctionRegister;
import
org.apache.shardingsphere.sqlfederation.optimizer.metadata.view.ShardingSphereViewExpander;
import
org.apache.shardingsphere.sqlfederation.optimizer.planner.rule.converter.EnumerableModifyConverterRule;
@@ -232,7 +233,7 @@ public final class SQLFederationPlannerUtils {
}
private static SqlOperatorTable getSQLOperatorTable(final
CalciteCatalogReader catalogReader, final DatabaseType databaseType) {
- return
SqlOperatorTables.chain(Arrays.asList(SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
+ return SqlOperatorTables.chain(Arrays.asList(new MySQLOperatorTable(),
SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
Arrays.asList(SqlLibrary.STANDARD,
DATABASE_TYPE_SQL_LIBRARIES.getOrDefault(databaseType.getType(),
SqlLibrary.MYSQL))), catalogReader));
}
diff --git a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
index 1d45a8f2189..0f22234124d 100644
--- a/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
+++ b/test/e2e/sql/src/test/resources/cases/dql/e2e-dql-select.xml
@@ -386,4 +386,20 @@
<test-case sql="SELECT BIN(NULL), BIN(''), BIN(' ')" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
<assertion expected-data-source-name="read_dataset" />
</test-case>
+
+ <test-case sql="SELECT NOT 1, not 0, NOT null" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT NOT order_id FROM t_order" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT NOT (t1.order_id + t2.order_id) FROM t_order t1
INNER JOIN t_order_item t2 ON t1.order_id = t2.order_id" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
+
+ <test-case sql="SELECT BIN(NOT 1), NOT BIT_COUNT(0)" db-types="MySQL"
scenario-types="db_tbl_sql_federation">
+ <assertion expected-data-source-name="read_dataset" />
+ </test-case>
</e2e-test-cases>
diff --git a/test/it/optimizer/src/test/resources/converter/select.xml
b/test/it/optimizer/src/test/resources/converter/select.xml
index 437688b8969..740ec9aeb51 100644
--- a/test/it/optimizer/src/test/resources/converter/select.xml
+++ b/test/it/optimizer/src/test/resources/converter/select.xml
@@ -35,4 +35,6 @@
<test-cases sql-case-id="select_with_json_value_return_type"
expected-sql="SELECT * FROM `t_order` WHERE JSON_VALUE(`items`, '''$.name'''
'RETURNING' VARCHAR(100)) = 'jack'" db-types="MySQL" />
<test-cases sql-case-id="select_projection_with_parameter"
expected-sql="SELECT 1 "id", ?, "SYSDATE"
"create_time", "TRUNC"("SYSDATE")
"create_date"" db-types="Oracle" sql-case-types="PLACEHOLDER" />
<test-cases sql-case-id="select_projection_with_parameter"
expected-sql="SELECT 1 "id", 'OK' "status",
"SYSDATE" "create_time",
"TRUNC"("SYSDATE") "create_date""
db-types="Oracle" sql-case-types="LITERAL" />
+ <test-cases sql-case-id="select_with_not_operator_number"
expected-sql="SELECT NOT 0, NOT 1, NOT 2" db-types="MySQL" />
+ <test-cases sql-case-id="select_with_not_operator_boolean"
expected-sql="SELECT NOT TRUE, NOT FALSE" db-types="MySQL" />
</sql-node-converter-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index d001ed31d33..31770a3e40f 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -9555,4 +9555,59 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_with_not_operator_number">
+ <projections start-index="7" stop-index="25">
+ <expression-projection text="NOT 0" start-index="7"
stop-index="11">
+ <expr>
+ <not-expression start-index="7" stop-index="11">
+ <expr>
+ <literal-expression value="0" start-index="11"
stop-index="11" />
+ </expr>
+ </not-expression>
+ </expr>
+ </expression-projection>
+ <expression-projection text="NOT 1" start-index="14"
stop-index="18">
+ <expr>
+ <not-expression start-index="14" stop-index="18">
+ <expr>
+ <literal-expression value="1" start-index="18"
stop-index="18" />
+ </expr>
+ </not-expression>
+ </expr>
+ </expression-projection>
+ <expression-projection text="NOT 2" start-index="21"
stop-index="25">
+ <expr>
+ <not-expression start-index="21" stop-index="25">
+ <expr>
+ <literal-expression value="2" start-index="25"
stop-index="25" />
+ </expr>
+ </not-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
+
+ <select sql-case-id="select_with_not_operator_boolean">
+ <projections start-index="7" stop-index="25">
+ <expression-projection text="NOT TRUE" start-index="7"
stop-index="14">
+ <expr>
+ <not-expression start-index="7" stop-index="14">
+ <expr>
+ <literal-expression value="true" start-index="11"
stop-index="14" />
+ </expr>
+ </not-expression>
+ </expr>
+ </expression-projection>
+ <expression-projection text="NOT FALSE" start-index="17"
stop-index="25">
+ <expr>
+ <not-expression start-index="17" stop-index="25">
+ <expr>
+ <literal-expression value="false" start-index="21"
stop-index="25" />
+ </expr>
+ </not-expression>
+ </expr>
+ </expression-projection>
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 9f199e5b391..c391cd8b72a 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -283,4 +283,6 @@
<sql-case id="select_with_reserved_word_with_table_ref" value="select
xxx.condition from xxx" db-types="MySQL,Doris"/>
<sql-case id="select_with_reserved_word" value="select describe from xxx"
db-types="MySQL,Doris"/>
<sql-case id="select_with_nvl_function_and_interval_hour" value="SELECT *
FROM t_order t WHERE t.CREATE_TIME <= nvl(END_TIME, sysdate) - INTERVAL ?
HOUR AND t.STATUS = 'FAILURE'" db-types="Oracle"/>
+ <sql-case id="select_with_not_operator_number" value="SELECT NOT 0, NOT 1,
NOT 2" db-types="MySQL" />
+ <sql-case id="select_with_not_operator_boolean" value="SELECT NOT TRUE,
NOT FALSE" db-types="MySQL" />
</sql-cases>
