Interestingly, the error is now different. Earlier, the error I used to get was:
Exception in thread "main" org.apache.calcite.runtime.CalciteContextException: From line 9, column 9 to line 9, column 26: No match found for function signature SUBSTR(<CHARACTER>, <NUMERIC>, <NUMERIC>) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:467) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:867) at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:852) at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5003) at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1835) After your suggestion, I get the following error. It feels like it has made progress but now it does not have an implementation for the SUBSTR function. Is there a way for me to map SUBSTR to the underlying SUBSTRING operator? Exception in thread "main" java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlFunction: SUBSTR at org.apache.calcite.util.Util.needToImplement(Util.java:975) at org.apache.calcite.sql.SqlOperator.getOperandCountRange(SqlOperator.java:198) at org.apache.calcite.sql.SqlUtil.lambda$filterRoutinesByParameterCount$3(SqlUtil.java:584) at com.google.common.collect.Iterators$5.computeNext(Iterators.java:637) at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141) at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136) at com.google.common.collect.Iterators$5.computeNext(Iterators.java:635) at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141) at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136) at com.google.common.collect.Iterators$5.computeNext(Iterators.java:635) at com.google.common.collect.AbstractIterator.tryToComputeNext(AbstractIterator.java:141) at com.google.common.collect.AbstractIterator.hasNext(AbstractIterator.java:136) at com.google.common.collect.Iterators.addAll(Iterators.java:355) at com.google.common.collect.Lists.newArrayList(Lists.java:143) at org.apache.calcite.sql.SqlUtil.lookupSubjectRoutines(SqlUtil.java:515) at org.apache.calcite.sql.SqlUtil.lookupRoutine(SqlUtil.java:443) at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:241) at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:219) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5766) at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5753) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1753) at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1738) at org.apache.calcite.sql.type.InferTypes.lambda$static$0(InferTypes.java:47) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1933) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1941) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1941) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1941) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1941) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1941) at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1941) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:4093) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:4085) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3424) at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1064) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1038) at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:232) at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1013) at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:721) at io.netspring.playground.calcite.CalcitePoc.processQuery(CalcitePoc.java:631) at io.netspring.playground.calcite.CalcitePoc.main(CalcitePoc.java:681) On Thu, Feb 25, 2021 at 6:18 AM Priyendra Deshwal <[email protected]> wrote: > > On Wed, Feb 24, 2021 at 9:51 AM Ruben Q L <[email protected]> wrote: > >> Honestly, I don't know the details. I just know that on our application we >> use "withIdentifierExpansion(true)", to avoid potential issues (I guess >> like the one you had). >> Several internal Calcite classes do the same, maybe someone with more >> experience on the SqlValidator could give more info. >> >> >> Regarding the SUBSTR issue, I was expecting that changing the >> CalciteConnectionConfig as: >> CalciteConnectionConfig.DEFAULT >> .set(CalciteConnectionProperty.CASE_SENSITIVE, "false") >> .set(CalciteConnectionProperty.FUN, "oracle") >> would do the trick; but it seems it does not. >> >> Instead, what I think would work in your test would be leaving the >> CalciteConnectionConfig as you have it, and enlarge the >> SqlStdOperatorTable: instead of just >> SqlStdOperatorTable.instance() >> use >> SqlOperatorTables.chain(SqlStdOperatorTable.instance(), >> >> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(SqlLibrary.ORACLE)) >> (or whatever SqlLibrary suits your needs). Could you verify it? >> > > Unfortunately, the suggestion did not seem to work :( > > I tried a) setting the FUN property, b) just using the chained operator > table and c) both of those together. I am continuing to look at various > Calcite unit tests etc to see if I can spot any ideas. Meanwhile, any > other suggestions are welcome. > > Thanks! > >
