[ISTHMUS] Issues in string concatenation

[YBRua]

Isthmus may run into problems when converting a string concatenation call to substrait plan.

1. It does not support concatenating two string literals

The input to Isthmus is

"SELECT 'foo' || 'bar' FROM test;" -c "CREATE TABLE test(col_1 INTEGER NOT NULL, col_2 VARCHAR(10));"

and it throws an error. I suspect that this is because substrait only have two signatures for concat: concat:vchar_vchar and concat:str_str. In this case, both inputs are fixed char fchar so the signature match fails. Perhaps adding a cast somewhere can fix this?

Full stack trace below:

java.lang.IllegalArgumentException: Unable to convert call ||(char<3>, char<3>).
        at io.substrait.isthmus.expression.RexExpressionConverter.visitCall(RexExpressionConverter.java:78)
        at io.substrait.isthmus.expression.RexExpressionConverter.visitCall(RexExpressionConverter.java:19)
        at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
        at io.substrait.isthmus.SubstraitRelVisitor.toExpression(SubstraitRelVisitor.java:99)
        at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
        at java.util.Collections$2.tryAdvance(Collections.java:4853)
        at java.util.Collections$2.forEachRemaining(Collections.java:4861)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:921)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682)
        at io.substrait.isthmus.SubstraitRelVisitor.visit(SubstraitRelVisitor.java:156)
        at io.substrait.isthmus.SubstraitRelVisitor.visit(SubstraitRelVisitor.java:61)
        at io.substrait.isthmus.RelNodeVisitor.reverseAccept(RelNodeVisitor.java:95)
        at io.substrait.isthmus.SubstraitRelVisitor.apply(SubstraitRelVisitor.java:351)
        at io.substrait.isthmus.SubstraitRelVisitor.convert(SubstraitRelVisitor.java:374)
        at io.substrait.isthmus.SubstraitRelVisitor.convert(SubstraitRelVisitor.java:366)
        at io.substrait.isthmus.SqlToSubstrait.lambda$executeInner$0(SqlToSubstrait.java:80)
        at java.util.ArrayList.forEach(ArrayList.java:1511)
        at io.substrait.isthmus.SqlToSubstrait.executeInner(SqlToSubstrait.java:73)
        at io.substrait.isthmus.SqlToSubstrait.execute(SqlToSubstrait.java:41)
        at io.substrait.isthmus.PlanEntryPoint.call(PlanEntryPoint.java:59)
        at io.substrait.isthmus.PlanEntryPoint.call(PlanEntryPoint.java:16)
        at picocli.CommandLine.executeUserObject(CommandLine.java:1953)
        at picocli.CommandLine.access$1300(CommandLine.java:145)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2346)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2311)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
        at picocli.CommandLine.execute(CommandLine.java:2078)
        at io.substrait.isthmus.PlanEntryPoint.main(PlanEntryPoint.java:51)

2. It only recognizes a || b as the concatenation operator, and does not recognize CONCAT(a, b)

The input query is

"SELECT CONCAT('foo', col_2) FROM test;"

This seems to be limited by Apache Calcite. "SELECT 'foo' || col_2 FROM test;" would work just fine.

Full stack trace below:

org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 27: No match found for function signature CONCAT(<CHARACTER>, <CHARACTER>)
        at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:480)
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5266)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1948)
        at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:326)
        at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)
        at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6277)
        at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6264)
        at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1862)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1847)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:463)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4409)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3652)
        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:1100)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1071)
        at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:247)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1046)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:752)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:586)
        at io.substrait.isthmus.SqlToSubstrait.lambda$sqlToRelNode$1(SqlToSubstrait.java:110)
        at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
        at java.util.AbstractList$RandomAccessSpliterator.forEachRemaining(AbstractList.java:720)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:921)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:682)
        at io.substrait.isthmus.SqlToSubstrait.sqlToRelNode(SqlToSubstrait.java:119)
        at io.substrait.isthmus.SqlToSubstrait.executeInner(SqlToSubstrait.java:72)
        at io.substrait.isthmus.SqlToSubstrait.execute(SqlToSubstrait.java:41)
        at io.substrait.isthmus.PlanEntryPoint.call(PlanEntryPoint.java:59)
        at io.substrait.isthmus.PlanEntryPoint.call(PlanEntryPoint.java:16)
        at picocli.CommandLine.executeUserObject(CommandLine.java:1953)
        at picocli.CommandLine.access$1300(CommandLine.java:145)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2346)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2311)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
        at picocli.CommandLine.execute(CommandLine.java:2078)
        at io.substrait.isthmus.PlanEntryPoint.main(PlanEntryPoint.java:51)
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature CONCAT(<CHARACTER>, <CHARACTER>)
        at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:480)
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
        at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)
        ... 43 more