[IGNITE-18762] Sql. Type inference errors for some usages of dynamic parameters in CASE WHEN .. END expressions

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: calcite2-required calcite3-required ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Maksim Zhuravkov
• Created: 08/Feb/23 13:14
• Updated: 09/Feb/23 08:31

Description

In some cases CASE WHEN .. END expressions produce inference errors. Examples:

    @Test
    public void test() {
// CASE WHEN <condition> THEN .. WHEN <condition2> THEN ... END
        assertQuery("SELECT CASE ? "
                + "WHEN '1' = ? "
                + "THEN 'b' "
                + "ELSE ? END")
                .withParams("1", "2", "3")
                .check();
    }

Result:

class org.apache.calcite.sql.SqlBasicCall: CAST('1' AS UNKNOWN)
java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlBasicCall: CAST('1' AS UNKNOWN)
	at org.apache.calcite.util.Util.needToImplement(Util.java:1101)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType(SqlValidatorImpl.java:1767)
	at org.apache.calcite.sql.SqlBinaryOperator.convertType(SqlBinaryOperator.java:137)
	at org.apache.calcite.sql.SqlBinaryOperator.adjustType(SqlBinaryOperator.java:132)
	at org.apache.calcite.sql.SqlOperator.deriveType(SqlOperator.java:609)
	at org.apache.calcite.sql.SqlBinaryOperator.deriveType(SqlBinaryOperator.java:178)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6373)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6360)
	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
	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.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:541)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:2016)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.inferUnknownTypes(IgniteSqlValidator.java:568)
	at org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.inferUnknownTypes(IgniteSqlValidator.java:564)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:462)