Thanks for continuing to assist me with this issue Ruben. I tried your suggestions on 1.25 and they did not work. However, when I tried the same on master, it actually worked just like you reported. This suggests that if I wait for 1.27 release then the issue should automatically get fixed for me. Thanks once again!
On Sun, Feb 28, 2021 at 4:21 AM Ruben Q L <[email protected]> wrote: > 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! > > > > > > > > >
