You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by al...@apache.org on 2022/12/27 13:56:10 UTC
[ignite] branch master updated: IGNITE-18282 SQL Calcite: Improve dynamic parameters type inference - Fixes #10458.
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 708d77f6570 IGNITE-18282 SQL Calcite: Improve dynamic parameters type inference - Fixes #10458.
708d77f6570 is described below
commit 708d77f6570143df5899a0d544ec2de7e2b923ac
Author: Aleksey Plekhanov <pl...@gmail.com>
AuthorDate: Tue Dec 27 16:50:59 2022 +0300
IGNITE-18282 SQL Calcite: Improve dynamic parameters type inference - Fixes #10458.
Signed-off-by: Aleksey Plekhanov <pl...@gmail.com>
---
.../query/calcite/CalciteQueryProcessor.java | 32 ++---
.../calcite/exec/exp/ExpressionFactoryImpl.java | 7 ++
.../processors/query/calcite/prepare/CacheKey.java | 20 ++-
.../query/calcite/prepare/IgniteSqlValidator.java | 16 +--
.../query/calcite/prepare/IgniteTypeCoercion.java | 135 ++++++++++++++++++++-
.../processors/query/calcite/QueryChecker.java | 2 +-
.../integration/AbstractBasicIntegrationTest.java | 6 +-
.../DynamicParametersIntegrationTest.java | 112 +++++++++++++++++
.../query/calcite/integration/FunctionsTest.java | 12 --
.../ignite/testsuites/IntegrationTestSuite.java | 4 +-
10 files changed, 300 insertions(+), 46 deletions(-)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
index 925d516cbf3..2d543a919a8 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
@@ -424,23 +424,24 @@ public class CalciteQueryProcessor extends GridProcessorAdapter implements Query
List<RootQuery<Object[]>> qrys = new ArrayList<>(batchedParams.size());
- Function<RootQuery<Object[]>, QueryPlan> planSupplier = new Function<RootQuery<Object[]>, QueryPlan>() {
- private QueryPlan plan;
-
- @Override public QueryPlan apply(RootQuery<Object[]> qry) {
- if (plan == null) {
- plan = queryPlanCache().queryPlan(new CacheKey(schema.getName(), sql), () ->
- prepareSvc.prepareSingle(qryNode, qry.planningContext())
- );
+ BiFunction<RootQuery<Object[]>, Object[], QueryPlan> planSupplier =
+ new BiFunction<RootQuery<Object[]>, Object[], QueryPlan>() {
+ private QueryPlan plan;
+
+ @Override public QueryPlan apply(RootQuery<Object[]> qry, Object[] params) {
+ if (plan == null) {
+ plan = queryPlanCache().queryPlan(new CacheKey(schema.getName(), sql, null, params), () ->
+ prepareSvc.prepareSingle(qryNode, qry.planningContext())
+ );
+ }
+
+ return plan;
}
-
- return plan;
- }
- };
+ };
for (final Object[] batch: batchedParams) {
FieldsQueryCursor<List<?>> cur = processQuery(qryCtx, qry ->
- executionSvc.executePlan(qry, planSupplier.apply(qry)), schema.getName(), sql, qrys, batch);
+ executionSvc.executePlan(qry, planSupplier.apply(qry, batch)), schema.getName(), sql, qrys, batch);
cursors.add(cur);
}
@@ -460,7 +461,7 @@ public class CalciteQueryProcessor extends GridProcessorAdapter implements Query
assert schema != null : "Schema not found: " + schemaName;
- QueryPlan plan = queryPlanCache().queryPlan(new CacheKey(schema.getName(), sql));
+ QueryPlan plan = queryPlanCache().queryPlan(new CacheKey(schema.getName(), sql, null, params));
if (plan != null) {
return Collections.singletonList(
@@ -478,7 +479,8 @@ public class CalciteQueryProcessor extends GridProcessorAdapter implements Query
QueryPlan plan0;
if (qryList.size() == 1) {
plan0 = queryPlanCache().queryPlan(
- new CacheKey(schema.getName(), sql), // Use source SQL to avoid redundant parsing next time.
+ // Use source SQL to avoid redundant parsing next time.
+ new CacheKey(schema.getName(), sql, null, params),
() -> prepareSvc.prepareSingle(sqlNode, qry.planningContext())
);
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
index 8e3cc5dd6b8..69acc6028b3 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/ExpressionFactoryImpl.java
@@ -51,6 +51,7 @@ import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
@@ -537,6 +538,12 @@ public class ExpressionFactoryImpl<Row> implements ExpressionFactory<Row> {
return super.visitFieldAccess(fieldAccess);
}
+
+ @Override public RexNode visitDynamicParam(RexDynamicParam dynamicParam) {
+ b.append(", paramType=").append(dynamicParam.getType().getFullTypeString());
+
+ return super.visitDynamicParam(dynamicParam);
+ }
}.apply(node);
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
index 35e48db46df..07cedd66a3c 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/CacheKey.java
@@ -17,7 +17,9 @@
package org.apache.ignite.internal.processors.query.calcite.prepare;
+import java.util.Arrays;
import java.util.Objects;
+import org.apache.ignite.internal.util.typedef.X;
/**
*
@@ -32,16 +34,22 @@ public class CacheKey {
/** */
private final Object contextKey;
+ /** */
+ private final Class<?>[] paramTypes;
+
/**
* @param schemaName Schema name.
* @param query Query string.
* @param contextKey Optional context key to differ queries with and without/different flags, having an impact
* on result plan (like LOCAL flag)
+ * @param params Dynamic parameters.
*/
- public CacheKey(String schemaName, String query, Object contextKey) {
+ public CacheKey(String schemaName, String query, Object contextKey, Object[] params) {
this.schemaName = schemaName;
this.query = query;
this.contextKey = contextKey;
+ paramTypes = params.length == 0 ? null :
+ Arrays.stream(params).map(p -> (p != null) ? p.getClass() : Void.class).toArray(Class[]::new);;
}
/**
@@ -49,7 +57,7 @@ public class CacheKey {
* @param query Query string.
*/
public CacheKey(String schemaName, String query) {
- this(schemaName, query, null);
+ this(schemaName, query, null, X.EMPTY_OBJECT_ARRAY);
}
/** {@inheritDoc} */
@@ -63,9 +71,14 @@ public class CacheKey {
if (!schemaName.equals(cacheKey.schemaName))
return false;
+
if (!query.equals(cacheKey.query))
return false;
- return Objects.equals(contextKey, cacheKey.contextKey);
+
+ if (!Objects.equals(contextKey, cacheKey.contextKey))
+ return false;
+
+ return Arrays.deepEquals(paramTypes, cacheKey.paramTypes);
}
/** {@inheritDoc} */
@@ -73,6 +86,7 @@ public class CacheKey {
int result = schemaName.hashCode();
result = 31 * result + query.hashCode();
result = 31 * result + (contextKey != null ? contextKey.hashCode() : 0);
+ result = 31 * result + Arrays.deepHashCode(paramTypes);
return result;
}
}
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 8f5644e8963..74143e198d2 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
@@ -494,14 +494,14 @@ public class IgniteSqlValidator extends SqlValidatorImpl {
/** {@inheritDoc} */
@Override protected void inferUnknownTypes(RelDataType inferredType, SqlValidatorScope scope, SqlNode node) {
if (node instanceof SqlDynamicParam && inferredType.equals(unknownType)) {
- // Infer type of dynamic parameters of unknown type as OTHER.
- // Parameter will be converted from Object class to required class in runtime.
- // Such an approach helps to bypass some cases where parameter types can never be inferred (for example,
- // in expression "CASE WHEN ... THEN ? ELSE ? END"), but also has new issues: if SQL function's method
- // has overloads, it's not possible to find correct unique method to call, so random method will be choosen.
- // For such functions operand type inference should be implemented to find the correct method
- // (see https://issues.apache.org/jira/browse/CALCITE-4347).
- setValidatedNodeType(node, typeFactory().createCustomType(Object.class));
+ if (parameters.length > ((SqlDynamicParam)node).getIndex()) {
+ Object param = parameters[((SqlDynamicParam)node).getIndex()];
+
+ setValidatedNodeType(node, (param == null) ? typeFactory().createSqlType(SqlTypeName.NULL) :
+ typeFactory().toSql(typeFactory().createType(param.getClass())));
+ }
+ else
+ setValidatedNodeType(node, typeFactory().createCustomType(Object.class));
}
else if (node instanceof SqlCall) {
final SqlValidatorScope newScope = scopes.get(node);
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.java
index 007f1f36684..0528f37a090 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteTypeCoercion.java
@@ -17,12 +17,19 @@
package org.apache.ignite.internal.processors.query.calcite.prepare;
+import java.nio.charset.Charset;
+import org.apache.calcite.adapter.java.JavaTypeFactory;
+import org.apache.calcite.rel.type.DynamicRecordType;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeFactoryImpl;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCollation;
import org.apache.calcite.sql.SqlDataTypeSpec;
-import org.apache.calcite.sql.SqlDynamicParam;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlUserDefinedTypeNameSpec;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParserPos;
@@ -30,9 +37,14 @@ import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
import org.apache.calcite.sql.validate.implicit.TypeCoercionImpl;
+import org.apache.calcite.util.Util;
import org.apache.ignite.internal.processors.query.calcite.type.IgniteCustomType;
import org.apache.ignite.internal.processors.query.calcite.type.OtherType;
import org.apache.ignite.internal.processors.query.calcite.type.UuidType;
+import org.jetbrains.annotations.Nullable;
+
+import static java.util.Objects.requireNonNull;
+import static org.apache.calcite.sql.type.NonNullableAccessors.getCollation;
/**
* Implementation of implicit type cast.
@@ -53,9 +65,6 @@ public class IgniteTypeCoercion extends TypeCoercionImpl {
if (targetType instanceof IgniteCustomType) {
SqlNode operand = call.getOperandList().get(idx);
- if (operand instanceof SqlDynamicParam)
- return false;
-
RelDataType fromType = validator.deriveType(scope, operand);
if (fromType == null)
@@ -80,7 +89,27 @@ public class IgniteTypeCoercion extends TypeCoercionImpl {
return false;
}
- return super.coerceOperandType(scope, call, idx, targetType);
+ // The next block is fully copied from parent class with cutted operand check to SqlDynamicParam.
+
+ // Transform the JavaType to SQL type because the SqlDataTypeSpec
+ // does not support deriving JavaType yet.
+ if (RelDataTypeFactoryImpl.isJavaType(targetType))
+ targetType = ((JavaTypeFactory)factory).toSql(targetType);
+
+ SqlNode operand = call.getOperandList().get(idx);
+
+ requireNonNull(scope, "scope");
+ // Check it early.
+ if (!needToCast(scope, operand, targetType))
+ return false;
+
+ // Fix up nullable attr.
+ RelDataType targetType1 = syncAttributes(validator.deriveType(scope, operand), targetType);
+ SqlNode desired = castTo(operand, targetType1);
+ call.setOperand(idx, desired);
+ updateInferredType(desired, targetType1);
+
+ return true;
}
/** {@inheritDoc} */
@@ -119,4 +148,100 @@ public class IgniteTypeCoercion extends TypeCoercionImpl {
return super.needToCast(scope, node, toType);
}
+
+
+ // The method is fully copied from parent class with cutted operand check to SqlDynamicParam, which not supported.
+
+ /** {@inheritDoc} */
+ @Override protected boolean coerceColumnType(
+ @Nullable SqlValidatorScope scope,
+ SqlNodeList nodeList,
+ int idx,
+ RelDataType targetType
+ ) {
+ // Transform the JavaType to SQL type because the SqlDataTypeSpec
+ // does not support deriving JavaType yet.
+ if (RelDataTypeFactoryImpl.isJavaType(targetType))
+ targetType = ((JavaTypeFactory)factory).toSql(targetType);
+
+ // This will happen when there is a star/dynamic-star column in the select list,
+ // and the source is values expression, i.e. `select * from (values(1, 2, 3))`.
+ // There is no need to coerce the column type, only remark
+ // the inferred row type has changed, we will then add in type coercion
+ // when expanding star/dynamic-star.
+
+ // See SqlToRelConverter#convertSelectList for details.
+ if (idx >= nodeList.size()) {
+ // Can only happen when there is a star(*) in the column,
+ // just return true.
+ return true;
+ }
+
+ final SqlNode node = nodeList.get(idx);
+ if (node instanceof SqlIdentifier) {
+ // Do not expand a star/dynamic table col.
+ SqlIdentifier node1 = (SqlIdentifier)node;
+ if (node1.isStar())
+ return true;
+ else if (DynamicRecordType.isDynamicStarColName(Util.last(node1.names))) {
+ // Should support implicit cast for dynamic table.
+ return false;
+ }
+ }
+
+ requireNonNull(scope, "scope is needed for needToCast(scope, operand, targetType)");
+ if (node instanceof SqlCall) {
+ SqlCall node2 = (SqlCall)node;
+ if (node2.getOperator().kind == SqlKind.AS) {
+ final SqlNode operand = node2.operand(0);
+ if (!needToCast(scope, operand, targetType))
+ return false;
+
+ RelDataType targetType2 = syncAttributes(validator.deriveType(scope, operand), targetType);
+ final SqlNode casted = castTo(operand, targetType2);
+ node2.setOperand(0, casted);
+ updateInferredType(casted, targetType2);
+ return true;
+ }
+ }
+
+ if (!needToCast(scope, node, targetType))
+ return false;
+
+ RelDataType targetType3 = syncAttributes(validator.deriveType(scope, node), targetType);
+ final SqlNode node3 = castTo(node, targetType3);
+ nodeList.set(idx, node3);
+ updateInferredType(node3, targetType3);
+ return true;
+ }
+
+ /**
+ * Sync the data type additional attributes before casting, i.e. nullability, charset, collation.
+ */
+ private RelDataType syncAttributes(
+ RelDataType fromType,
+ RelDataType toType
+ ) {
+ RelDataType syncedType = toType;
+ if (fromType != null) {
+ syncedType = factory.createTypeWithNullability(syncedType, fromType.isNullable());
+ if (SqlTypeUtil.inCharOrBinaryFamilies(fromType)
+ && SqlTypeUtil.inCharOrBinaryFamilies(toType)) {
+ Charset charset = fromType.getCharset();
+ if (charset != null && SqlTypeUtil.inCharFamily(syncedType)) {
+ SqlCollation collation = getCollation(fromType);
+ syncedType = factory.createTypeWithCharsetAndCollation(syncedType,
+ charset,
+ collation);
+ }
+ }
+ }
+ return syncedType;
+ }
+
+ /** */
+ private static SqlNode castTo(SqlNode node, RelDataType type) {
+ return SqlStdOperatorTable.CAST.createCall(SqlParserPos.ZERO, node,
+ SqlTypeUtil.convertTypeToSpec(type).withNullable(type.isNullable()));
+ }
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java
index 1f7503817ba..b705b9d2bb8 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java
@@ -361,7 +361,7 @@ public abstract class QueryChecker {
QueryEngine engine = getEngine();
List<FieldsQueryCursor<List<?>>> explainCursors =
- engine.query(null, "PUBLIC", "EXPLAIN PLAN FOR " + qry);
+ engine.query(null, "PUBLIC", "EXPLAIN PLAN FOR " + qry, params);
FieldsQueryCursor<List<?>> explainCursor = explainCursors.get(0);
List<List<?>> explainRes = explainCursor.getAll();
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java
index 94086d33cad..a4582842e4f 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/AbstractBasicIntegrationTest.java
@@ -164,7 +164,11 @@ public class AbstractBasicIntegrationTest extends GridCommonAbstractTest {
IgniteCache<Integer, Employer> person = client.getOrCreateCache(new CacheConfiguration<Integer, Employer>()
.setName(TABLE_NAME)
.setSqlSchema("PUBLIC")
- .setQueryEntities(F.asList(new QueryEntity(Integer.class, Employer.class).setTableName(TABLE_NAME)))
+ .setQueryEntities(F.asList(new QueryEntity(Integer.class, Employer.class)
+ .setTableName(TABLE_NAME)
+ .addQueryField("ID", Integer.class.getName(), null)
+ .setKeyFieldName("ID")
+ ))
.setCacheMode(cacheMode)
.setBackups(backups)
);
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DynamicParametersIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DynamicParametersIntegrationTest.java
new file mode 100644
index 00000000000..ae35a0d51f0
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DynamicParametersIntegrationTest.java
@@ -0,0 +1,112 @@
+/*
+ * 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.integration;
+
+import java.math.BigDecimal;
+import java.sql.Date;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.time.Duration;
+import java.time.LocalDate;
+import java.time.Period;
+import java.util.List;
+import java.util.UUID;
+import org.apache.ignite.internal.util.typedef.F;
+import org.junit.Test;
+
+/**
+ * Dynamic parameters types inference test.
+ */
+public class DynamicParametersIntegrationTest extends AbstractBasicIntegrationTest {
+ /** */
+ @Test
+ public void testMetadataTypesForDynamicParameters() {
+ List<Object> values = F.asList(
+ "test",
+ BigDecimal.valueOf(1),
+ 1,
+ 1L,
+ 1f,
+ 1d,
+ UUID.randomUUID(),
+ Duration.ofSeconds(1),
+ Date.valueOf("2022-01-01"),
+ Timestamp.valueOf("2022-01-01 01:01:01"),
+ Time.valueOf("02:01:01"),
+ Period.ofMonths(1)
+ );
+
+ List<String> types = F.asList("VARCHAR", "DECIMAL(32767, 0)", "INTEGER", "BIGINT", "REAL", "DOUBLE",
+ "UUID", "INTERVAL DAY TO SECOND", "DATE", "TIMESTAMP(0)", "TIME(0)", "INTERVAL YEAR TO MONTH");
+
+ for (int i = 0; i < values.size(); i++) {
+ assertQuery("SELECT typeof(?)").withParams(values.get(i)).returns(types.get(i)).check();
+ assertQuery("SELECT ?").withParams(values.get(i)).returns(values.get(i)).check();
+ }
+ }
+
+ /** */
+ @Test
+ public void testDynamicParameters() {
+ assertQuery("SELECT COALESCE(?, ?)").withParams("a", 10).returns("a").check();
+ assertQuery("SELECT COALESCE(null, ?)").withParams(13).returns(13).check();
+ assertQuery("SELECT LOWER(?)").withParams("ASD").returns("asd").check();
+ assertQuery("SELECT ?").withParams("asd").returns("asd").check();
+ assertQuery("SELECT ? + ?, LOWER(?) ").withParams(2, 2, "TeSt").returns(4, "test").check();
+ assertQuery("SELECT LOWER(?), ? + ? ").withParams("TeSt", 2, 2).returns("test", 4).check();
+ assertQuery("SELECT POWER(?, ?)").withParams(2, 3).returns(8d).check();
+ assertQuery("SELECT SQRT(?)").withParams(4d).returns(2d).check();
+ assertQuery("SELECT ? % ?").withParams(11, 10).returns(BigDecimal.ONE).check();
+
+ assertQuery("SELECT LAST_DAY(?)").withParams(Date.valueOf("2022-01-01"))
+ .returns(Date.valueOf("2022-01-31")).check();
+ assertQuery("SELECT LAST_DAY(?)").withParams(LocalDate.parse("2022-01-01"))
+ .returns(Date.valueOf("2022-01-31")).check();
+
+ createAndPopulateTable();
+
+ assertQuery("SELECT name LIKE '%' || ? || '%' FROM person where name is not null").withParams("go")
+ .returns(true).returns(false).returns(false).returns(false).check();
+
+ assertQuery("SELECT id FROM person WHERE name LIKE ? ORDER BY id LIMIT ?").withParams("I%", 1)
+ .returns(0).check();
+
+ assertQuery("SELECT id FROM person WHERE name LIKE ? ORDER BY id LIMIT ? OFFSET ?").withParams("I%", 1, 1)
+ .returns(2).check();
+
+ assertQuery("SELECT id FROM person WHERE salary<? and id>?").withParams(15, 1)
+ .returns(3).returns(4).check();
+ }
+
+ /** Tests the same query with different type of parameters to cover case with check right plans cache work. **/
+ @Test
+ public void testWithDifferentParametersTypes() {
+ assertQuery("SELECT ? + ?, LOWER(?) ").withParams(2, 2, "TeSt").returns(4, "test").check();
+ assertQuery("SELECT ? + ?, LOWER(?) ").withParams(2.2, 2.2, "TeSt").returns(4.4, "test").check();
+
+ assertQuery("SELECT COALESCE(?, ?)").withParams(null, null).returns(NULL_RESULT).check();
+ assertQuery("SELECT COALESCE(?, ?)").withParams(null, 13).returns(13).check();
+ assertQuery("SELECT COALESCE(?, ?)").withParams("a", 10).returns("a").check();
+ assertQuery("SELECT COALESCE(?, ?)").withParams("a", "b").returns("a").check();
+ assertQuery("SELECT COALESCE(?, ?)").withParams(22, 33).returns(22).check();
+ assertQuery("SELECT COALESCE(?, ?)").withParams(12.2, "b").returns("12.2").check();
+ assertQuery("SELECT COALESCE(?, ?)").withParams(12, "b").returns("12").check();
+ assertQuery("SELECT UPPER(TYPEOF(?))").withParams(1).returns("INTEGER").check();
+ assertQuery("SELECT UPPER(TYPEOF(?))").withParams(1d).returns("DOUBLE").check();
+ }
+}
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 2d33342fd2c..c7eaa949949 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
@@ -275,18 +275,6 @@ public class FunctionsTest extends AbstractBasicIntegrationTest {
assertThrows("SELECT 'abcd' ~ '[a-z'", IgniteSQLException.class, null);
}
- /** */
- @Test
- public void testDynamicParameterTypesInference() {
- assertQuery("SELECT lower(?)").withParams("ASD").returns("asd").check();
- assertQuery("SELECT ? % ?").withParams(11, 10).returns(BigDecimal.valueOf(1)).check();
- assertQuery("SELECT sqrt(?)").withParams(4d).returns(2d).check();
- assertQuery("SELECT last_day(?)").withParams(Date.valueOf("2022-01-01"))
- .returns(Date.valueOf("2022-01-31")).check();
- assertQuery("SELECT ?").withParams("asd").returns("asd").check();
- assertQuery("SELECT coalesce(?, ?)").withParams("a", 10).returns("a").check();
- }
-
/** */
@Test
public void testCastToBoolean() {
diff --git a/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java b/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
index 198d5743646..569eaa44328 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
@@ -27,6 +27,7 @@ import org.apache.ignite.internal.processors.query.calcite.integration.CalciteBa
import org.apache.ignite.internal.processors.query.calcite.integration.CalciteErrorHandlilngIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.CorrelatesIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.DataTypesTest;
+import org.apache.ignite.internal.processors.query.calcite.integration.DynamicParametersIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.FunctionsTest;
import org.apache.ignite.internal.processors.query.calcite.integration.HashSpoolIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.IndexDdlIntegrationTest;
@@ -112,7 +113,8 @@ import org.junit.runners.Suite;
KeepBinaryIntegrationTest.class,
QueryMetadataIntegrationTest.class,
MemoryQuotasIntegrationTest.class,
- LocalDateTimeSupportTest.class
+ LocalDateTimeSupportTest.class,
+ DynamicParametersIntegrationTest.class,
})
public class IntegrationTestSuite {
}