Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4408

Implement Oracle SUBSTR function

    XMLWordPrintableJSON

Details

    Description

      Oracle SUBSTR function throws an exception when matching operand exception.

      public abstract class SqlOperatorBaseTest {
...
@Test void testSubString() {
    SqlTester t = tester(SqlLibrary.ORACLE);
    t.setFor(SqlLibraryOperators.SUBSTR);
    t.checkString("substr(CAST('abc' AS varchar(3)), 1, 3)", "ab", "VARCHAR(3) NOT NULL");
    t.checkString("substr(CAST('abc' AS varchar(3)), 1)", "abc", "VARCHAR(3) NOT NULL");
  }
...

      throws

      class org.apache.calcite.sql.SqlFunction: SUBSTR
java.lang.UnsupportedOperationException: class org.apache.calcite.sql.SqlFunction: SUBSTR
	at org.apache.calcite.util.Util.needToImplement(Util.java:1080)
	at org.apache.calcite.sql.SqlOperator.getOperandCountRange(SqlOperator.java:203)
	at org.apache.calcite.sql.SqlUtil.lambda$filterRoutinesByParameterCount$3(SqlUtil.java:610)
	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:541)
	at org.apache.calcite.sql.SqlUtil.lookupRoutine(SqlUtil.java:470)
	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:248)
	at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:226)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5933)
	at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5920)
	at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:138)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1775)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1760)
	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:275)
	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:467)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5629)
	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:115)
	at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:274)
	at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:467)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:5629)
	at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:115)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1024)
	at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:732)
	at org.apache.calcite.sql.test.AbstractSqlTester.parseAndValidate(AbstractSqlTester.java:177)
	at org.apache.calcite.sql.test.AbstractSqlTester.getResultType(AbstractSqlTester.java:165)
	at org.apache.calcite.sql.test.AbstractSqlTester.getColumnType(AbstractSqlTester.java:157)
	at org.apache.calcite.sql.test.AbstractSqlTester.check(AbstractSqlTester.java:479)
	at org.apache.calcite.sql.test.AbstractSqlTester.check(AbstractSqlTester.java:464)
	at org.apache.calcite.sql.test.AbstractSqlTester.checkString(AbstractSqlTester.java:449)
	at org.apache.calcite.sql.test.SqlOperatorBaseTest.testSubString(SqlOperatorBaseTest.java:2192)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)
	at org.junit.jupiter.engine.extension.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:139)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:131)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:81)
	at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:202)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:171)
	at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService.invokeAll(ForkJoinPoolHierarchicalTestExecutorService.java:115)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:171)
	at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService.invokeAll(ForkJoinPoolHierarchicalTestExecutorService.java:115)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.ForkJoinPoolHierarchicalTestExecutorService$ExclusiveTask.compute(ForkJoinPoolHierarchicalTestExecutorService.java:171)
	at java.util.concurrent.RecursiveAction.exec(RecursiveAction.java:189)
	at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
	at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
	at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
	at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)

      We need to complete the implementation of the Oracle SUBSTR function, including operand type checking, and expanding it to Calcite operators so that it can be executed in queries.

      Note that SUBSTR has semantics that are significantly different from the SUBSTRING operator, which Calcite already implements.

      Attachments

        Issue Links

          is related to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-2572 SUBSTRING function fails if start position is less than 1

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. CALCITE-815 Add an option to allow empty strings to represent null values

          • Major - Major loss of function.
          • Open
          relates to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-4427 Make SUBSTRING operator comply with ISO standard SQL

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #2271

          Activity

            People

              Unassigned Unassigned
              jamesstarr James Starr
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 2h 40m
                  2h 40m