Thanks for the feedback. I modified your initial TpcdsDeshwalTest to add a SUBSTR predicate in the WHERE clause, I ran the test, I got your initial "No match found for function signature SUBSTR(<CHARACTER>, <NUMERIC>, <NUMERIC>)" exception.
Then I just modified the SqlStdOperatorTable to use the chain (SqlStdOperatorTable.instance() + SqlLibraryOperatorTableFactory .INSTANCE.getOperatorTable(SqlLibrary.ORACLE)), re-ran the test, it worked fine. If I am not mistaken, the "getOperatorTable(SqlLibrary.ORACLE)" should provide the SUBSTR -> SUBSTRING conversion. I don't know why you're getting an UnsupportedOperationException. On Thu, Feb 25, 2021 at 2:38 PM Priyendra Deshwal <[email protected]> wrote: > 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! > > > > >
