You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (JIRA)" <ji...@apache.org> on 2016/11/12 23:11:58 UTC
[jira] [Commented] (DERBY-6902) Value out of range error (22003) on
DELETE with expression in WHERE clause
[ https://issues.apache.org/jira/browse/DERBY-6902?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15660446#comment-15660446 ]
Bryan Pendleton commented on DERBY-6902:
----------------------------------------
I've been spending some time thinking about the statement:
{code}
delete from test where big_number < ? - small_number * 1000
{code}
When you look at this as an abstract parse tree, you have something like the following:
{code}
<
big_number expression
? - expression
small_number * 1000
{code}
That is, at the time that we are contemplating how to handle
the parameter marker "?", what we know from the parse tree
is that it is the left-hand side of a binary arithmetic operator ( ? - "expression" )
And, we know that the right-hand side of that operator is an
expression ( small_number * 1000 ), which has the type 'integer'.
Although, higher up the tree, the results of our expression will be
compared ( big_number < expression ) to a BIGINT is not visible
to us, in the parse tree, because we are a **sub-expression** of the
higher-level expression.
So treating the parameter marker as INTEGER, rather than BIGINT,
actually seems correct to me, upon this further reflection.
Two other points:
First, here is the code tree which performs this type analysis, for
others to consider:
{quote}
Chose type=INTEGER NOT NULL
at org.apache.derby.impl.sql.compile.ParameterNode.setType(ParameterNode.java:156)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(BinaryOperatorNode.java:307)
at org.apache.derby.impl.sql.compile.BinaryArithmeticOperatorNode.bindExpression(BinaryArithmeticOperatorNode.java:132)
at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(BinaryOperatorNode.java:286)
at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(BinaryComparisonOperatorNode.java:142)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(SelectNode.java:602)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(DMLStatementNode.java:209)
at org.apache.derby.impl.sql.compile.DeleteNode.bindStatement(DeleteNode.java:297)
{quote}
Secondly, if you change the critical statement from
{code}
delete from test where big_number < ? - small_number * 1000
{code}
to
{code}
delete from test where big_number < ? - cast( small_number as bigint) * 1000
{code}
then all is well, because the BinaryOperatorNode which is processing the
subtraction operator sees a "right-hand side" of type BIGINT, and so it
marks the parameter marker as type BIGINT.
tl;dr It isn't obvious to me that this is a bug in Derby; I would appreciate the
opinions of others about how the SQL and JDBC specs describe the
type analysis that is to occur here.
> Value out of range error (22003) on DELETE with expression in WHERE clause
> --------------------------------------------------------------------------
>
> Key: DERBY-6902
> URL: https://issues.apache.org/jira/browse/DERBY-6902
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.12.1.1
> Reporter: Vedran Pavic
> Attachments: repro.java
>
>
> Hi,
> I've ran into the problem from the subject, which appears to be a bug, using the latest 10.12.1.1 release.
> Given the table:
> {code:sql}
> create table test (
> id bigint primary key,
> big_number bigint not null,
> small_number int not null
> )
> {code}
> The following DELETE statement will fail with _The resulting value is outside the range for the data type INTEGER_ (22003):
> {code:sql}
> delete from test
> where big_number < ? - small_number * 1000
> {code}
> {code:java}
> java.sql.SQLDataException: The resulting value is outside the range for the data type INTEGER.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setLong(Unknown Source)
> at sample.PlainJdbcTest.delete(PlainJdbcTest.java:36)
> 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.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
> at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:114)
> at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:57)
> at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:66)
> at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:51)
> 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.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
> at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
> at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
> at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
> at com.sun.proxy.$Proxy2.processTestClass(Unknown Source)
> at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:109)
> 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.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
> at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
> at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:377)
> at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:54)
> at org.gradle.internal.concurrent.StoppableExecutorImpl$1.run(StoppableExecutorImpl.java:40)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: ERROR 22003: The resulting value is outside the range for the data type INTEGER.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.iapi.types.DataType.outOfRange(Unknown Source)
> at org.apache.derby.iapi.types.SQLInteger.setValue(Unknown Source)
> ... 47 more
> {code}
> Minimal project to reproduce the error is available here:
> https://github.com/vpavic-samples/derby-delete-error-22003
> Thanks,
> Vedran
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)