This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new ffa685068db IGNITE-23673 SQL Calcite: Fix NVL/DECODE failure when used
with parameters of different data types - Fixes #11659.
ffa685068db is described below
commit ffa685068db4cbcb1bae058ea0978c368674b5e1
Author: Aleksey Plekhanov <[email protected]>
AuthorDate: Thu Nov 21 09:40:01 2024 +0300
IGNITE-23673 SQL Calcite: Fix NVL/DECODE failure when used with parameters
of different data types - Fixes #11659.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
.../calcite/prepare/IgniteSqlCallRewriteTable.java | 117 +++++++++++++++++++++
.../query/calcite/prepare/IgniteSqlValidator.java | 7 +-
.../query/calcite/integration/FunctionsTest.java | 45 ++++++++
3 files changed, 168 insertions(+), 1 deletion(-)
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlCallRewriteTable.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlCallRewriteTable.java
new file mode 100644
index 00000000000..80052ed8454
--- /dev/null
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlCallRewriteTable.java
@@ -0,0 +1,117 @@
+/*
+ * 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.ignite.internal.processors.query.calcite.prepare;
+
+import java.util.List;
+import java.util.Map;
+import java.util.concurrent.ConcurrentHashMap;
+import java.util.function.BiFunction;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.fun.SqlCase;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.validate.SqlValidator;
+
+import static org.apache.calcite.util.Static.RESOURCE;
+
+/**
+ * Ignite SQL call rewrite table. Performs unconditional rewrites for some
predefined Calcite SQL operators,
+ * which can't be extended other ways by Ignite.
+ */
+public class IgniteSqlCallRewriteTable {
+ /** Instance. */
+ public static final IgniteSqlCallRewriteTable INSTANCE = new
IgniteSqlCallRewriteTable();
+
+ /** Registered rewriters map. */
+ private final Map<SqlOperator, BiFunction<SqlValidator, SqlCall, SqlCall>>
map = new ConcurrentHashMap<>();
+
+ /** */
+ private IgniteSqlCallRewriteTable() {
+ register(SqlLibraryOperators.NVL,
IgniteSqlCallRewriteTable::nvlRewriter);
+ register(SqlLibraryOperators.DECODE,
IgniteSqlCallRewriteTable::decodeRewriter);
+ }
+
+ /** Registers rewriter for SQL operator. */
+ public void register(SqlOperator operator, BiFunction<SqlValidator,
SqlCall, SqlCall> rewriter) {
+ map.put(operator, rewriter);
+ }
+
+ /** Rewrites SQL call. */
+ SqlCall rewrite(SqlValidator validator, SqlCall call) {
+ BiFunction<SqlValidator, SqlCall, SqlCall> rewriter =
map.get(call.getOperator());
+
+ return rewriter == null ? call : rewriter.apply(validator, call);
+ }
+
+ /** Rewrites NVL call to CASE WHEN call. */
+ private static SqlCall nvlRewriter(SqlValidator validator, SqlCall call) {
+ validateQuantifier(validator, call); // check DISTINCT/ALL
+
+ List<SqlNode> operands = call.getOperandList();
+
+ if (operands.size() == 2) {
+ SqlParserPos pos = call.getParserPosition();
+
+ SqlNodeList whenList = new SqlNodeList(pos);
+ SqlNodeList thenList = new SqlNodeList(pos);
+
+ whenList.add(SqlStdOperatorTable.IS_NOT_NULL.createCall(pos,
operands.get(0)));
+ thenList.add(SqlNode.clone(operands.get(0)));
+ SqlNode elseExpr = operands.get(1);
+
+ return SqlCase.createSwitched(pos, null, whenList, thenList,
elseExpr);
+ }
+ else
+ return call; // Operands count will be validated and exception
will be thrown later.
+ }
+
+ /** Rewrites DECODE call to CASE WHEN call. */
+ private static SqlCall decodeRewriter(SqlValidator validator, SqlCall
call) {
+ validateQuantifier(validator, call); // check DISTINCT/ALL
+
+ List<SqlNode> operands = call.getOperandList();
+
+ SqlParserPos pos = call.getParserPosition();
+
+ SqlNode op0 = operands.get(0);
+
+ SqlNodeList whenList = new SqlNodeList(pos);
+ SqlNodeList thenList = new SqlNodeList(pos);
+
+ for (int i = 1; i < operands.size() - 1; i += 2) {
+
whenList.add(SqlStdOperatorTable.IS_NOT_DISTINCT_FROM.createCall(pos,
SqlNode.clone(op0), operands.get(i)));
+ thenList.add(operands.get(i + 1));
+ }
+
+ SqlNode elseExpr = operands.size() % 2 == 0 ?
operands.get(operands.size() - 1) : null;
+
+ return SqlCase.createSwitched(pos, null, whenList, thenList, elseExpr);
+ }
+
+ /** Throws a validation error if a DISTINCT or ALL quantifier is present.
*/
+ private static void validateQuantifier(SqlValidator validator, SqlCall
call) {
+ if (call.getFunctionQuantifier() != null) {
+ throw validator.newValidationError(call.getFunctionQuantifier(),
+
RESOURCE.functionQuantifierNotAllowed(call.getOperator().getName()));
+ }
+ }
+}
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
index 36128236f24..0c662acb720 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
@@ -332,7 +332,12 @@ public class IgniteSqlValidator extends SqlValidatorImpl {
}
}
- return super.performUnconditionalRewrites(node, underFrom);
+ node = super.performUnconditionalRewrites(node, underFrom);
+
+ if (config().callRewrite() && node instanceof SqlCall)
+ node = IgniteSqlCallRewriteTable.INSTANCE.rewrite(this,
(SqlCall)node);
+
+ return node;
}
/** Rewrites JOIN clause if required */
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
index 0f7ffe89ecf..2d2a85b32f5 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/FunctionsTest.java
@@ -451,4 +451,49 @@ public class FunctionsTest extends
AbstractBasicIntegrationTest {
assertQuery("SELECT CAST(CAST(? AS DECIMAL(2, 1)) AS BOOLEAN)")
.withParams(NULL_RESULT).returns(NULL_RESULT).check();
}
+
+ /** Tests NVL with different parameters data types. */
+ @Test
+ public void testNvl() {
+ // Result type is the least restrictive type for parameters.
+ assertQuery("select nvl('1', 2)").returns("1").check();
+ assertQuery("select nvl(1, '2')").returns("1").check();
+ assertQuery("select nvl(1, 2.0)").returns(new
BigDecimal("1.0")).check();
+ assertQuery("select nvl(1, 2::DOUBLE)").returns(1d).check();
+ assertQuery("select nvl(1::TINYINT,
2::SMALLINT)").returns((short)1).check();
+ assertQuery("select nvl(1.0, '2')").returns("1.0").check();
+ assertQuery("select nvl(null, 2)").returns(2).check();
+ assertQuery("select nvl(null, '2')").returns("2").check();
+ assertQuery("select nvl(null, null)").returns(NULL_RESULT).check();
+ assertQuery("select nvl(?, ?)").withParams("1",
2).returns("1").check();
+ assertQuery("select nvl(?, ?)").withParams(1,
"2").returns("1").check();
+ assertQuery("select nvl(?, ?)").withParams(1, 2d).returns(1d).check();
+ assertQuery("select nvl(?, ?)").withParams(null, 2).returns(2).check();
+ assertQuery("select nvl(?, ?)").withParams(null,
"2").returns("2").check();
+ assertQuery("select nvl(?, ?)").withParams(null,
null).returns(NULL_RESULT).check();
+ }
+
+ /** Tests DECODE with different parameters data types. */
+ @Test
+ public void testDecode() {
+ // Result type is the least restrictive type for then-else parameters.
+ // Type cast rules for comparison are identical to rules for WHERE
clause.
+ assertQuery("select decode('1', 1, '1', '2')").returns("1").check();
+ assertQuery("select decode('2', 1, '1', 2, 2)").returns("2").check();
+ assertQuery("select decode(1, '1', 1, 2)").returns(1).check();
+ assertQuery("select decode(2, '1', 1, '2', 2)").returns(2).check();
+ assertQuery("select decode(2, '1', 1, '2', 2,
'3')").returns("2").check();
+ assertQuery("select decode(2, '1', 1, '2',
2::DOUBLE)").returns(2.0).check();
+ assertQuery("select decode(1.0, 1, 1, 2)").returns(1).check();
+ assertQuery("select decode(1.1, 1, 1, 2)").returns(2).check();
+ assertQuery("select decode(1, 1.0, 1, 2)").returns(1).check();
+ assertQuery("select decode(1, 1.1, 1, 2)").returns(2).check();
+ assertQuery("select decode(1, 1::DOUBLE, 1, 2)").returns(1).check();
+ assertQuery("select decode('1', 1::DOUBLE, 1, 2)").returns(1).check();
+ assertQuery("select decode(1.0, '1', 1, 2)").returns(1).check();
+ assertQuery("select decode('1', 1.0, 1, 2)").returns(1).check();
+ assertQuery("select decode(null, null, 1, 2)").returns(1).check();
+ assertQuery("select decode(null, 1, 1, 2)").returns(2).check();
+ assertQuery("select decode(1, null, 1, 2)").returns(2).check();
+ }
}
