You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (Jira)" <ji...@apache.org> on 2020/11/24 18:47:00 UTC
[jira] [Comment Edited] (CALCITE-4408) Implement Oracle SUBSTR
function
[ https://issues.apache.org/jira/browse/CALCITE-4408?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17238308#comment-17238308 ]
Julian Hyde edited comment on CALCITE-4408 at 11/24/20, 6:46 PM:
-----------------------------------------------------------------
Now I see why you and I have been driving in different directions. You want to make this work regardless of the details of the {{SUBSTR}} / {{SUBSTRING}} function on the target DB; you are assuming that we would translate Calcite SUBSTRING to the native SUBSTR function, and therefore we need to be defensive because we can't rely on the semantics. I am assuming that {{SUBSTRING}} has the exact semantics.
I confess that I have been deferring thinking about how we implement Calcite's {{SUBSTRING}} in terms of Oracle's {{SUBSTR}}. (Most other DBs have a {{SUBSTRING}} function that comply with standard SQL, even though they may also have a {{SUBSTR}} function with different semantics.)
Are you aware of any other databases besides Oracle that do not have a standards-compliant {{SUBSTRING}} function?
was (Author: julianhyde):
Now I see why you and I have been driving in different directions. You want to make this work regardless of the details of the SUBSTR / SUBSTRING function on the target DB; you are assuming. I am assuming that SUBSTRING has the exact semantics.
I confess that I have been deferring thinking about how we implement Calcite's SUBSTRING in terms of Oracle's SUBSTR. (Most other DBs have a SUBSTRING function that comply with standard SQL, even though they may also have a SUBSTR function with different semantics.)
> Implement Oracle SUBSTR function
> --------------------------------
>
> Key: CALCITE-4408
> URL: https://issues.apache.org/jira/browse/CALCITE-4408
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: James Starr
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> Oracle SUBSTR function throws an exception when matching operand exception.
>
> {code:java}
> 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");
> }
> ...
> {code}
> throws
> {noformat}
> 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)
> {noformat}
> 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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)