db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6902) Value out of range error (22003) on DELETE with expression in WHERE clause
Date Tue, 15 Nov 2016 02:55:59 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6902?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15665846#comment-15665846
] 

Rick Hillegas commented on DERBY-6902:
--------------------------------------

Hi Bryan,

Numeric comparisons are described in the 2011 SQL Standard, part 2, section 8.2 (<comparison
predicate>), General Rule 2: "Numbers are compared with respect to their algebraic value."
That says to me that numbers must ultimately be promoted to the biggest, most general numeric
type on either side of the comparison operator, just as they are promoted in Java. I don't
think that the JDBC spec provides any guidance here.

The problem with this comparison is that ? is an untyped number. Is it an integer? Is it a
large fixed numeric (decimal)? Is it a floating point number? Derby doesn't have enough information
to promote the expressions. The following ought to work, though:

  big_number < cast(? as bigint) - small_number * 1000

Thanks,
-Rick


> 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: newTestCase.diff, 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)

Mime
View raw message