[IGNITE-18924] Sql. Dynamic parameters. Type inference error for IN operator: SELECT ? IN (?, ?)

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.0.0-beta1
• Component/s: sql
• Labels: calcite2-required calcite3-required ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Maksim Zhuravkov
• Created: 28/Feb/23 15:30
• Updated: 28/Feb/23 15:33

Description

The following query fails with an exception in calcite:

assertQuery("SELECT ? IN (?, ?)").withParams(1, 1, 2).check();

class org.apache.calcite.sql.SqlNodeList: ?, ?
java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlNodeList: ?, ?
	at org.apache.calcite.util.Util.needToImplement(Util.java:1101)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6381)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6360)
	at org.apache.calcite.sql.SqlNodeList.accept(SqlNodeList.java:266)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1869)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1854)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.deriveType(IgniteSqlValidator.java:321)
	at org.apache.calcite.sql.type.SqlTypeUtil.deriveType(SqlTypeUtil.java:200)
	at org.apache.calcite.sql.type.InferTypes.lambda$static$0(InferTypes.java:47)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.inferUnknownTypes(IgniteSqlValidator.java:604)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:462)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4505)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3710)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validateSelect(IgniteSqlValidator.java:255)
	at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:64)
	at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:89)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1107)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1078)
	at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:248)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1053)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:759)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validate(IgniteSqlValidator.java:140)
	at org.apache.ignite.internal.sql.engine.prepare.IgnitePlanner.validateAndGetTypeMetadata(IgnitePlanner.java:211)
	at org.apache.ignite.internal.sql.engine.prepare.PrepareServiceImpl.lambda$prepareQuery$3(PrepareServiceImpl.java:234)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)

Although this example works:

assertQuery("SELECT 1 IN (?, ?)").withParams(1, 2).returns(true).check();

The same behaviour is observed for NOT IN operator.