[ 
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)

Reply via email to