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 "Vedran Pavic (JIRA)" <ji...@apache.org> on 2016/12/02 08:01:14 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=15714388#comment-15714388 ]
Vedran Pavic commented on DERBY-6902:
-------------------------------------
Hi Rick,
Thanks for the feedback - I stand corrected on the {{BIGINT}} and standard, I must've been referring to older version of the standard when I researched since I clearly remember using a SQL validator which complained about {{BIGINT}}.
Bryan, thanks for the suggestion. Reformulation of the statement is something I've been considering as well however I'm hesitant to do so since the original statement is perfectly valid and from the client perspective it expresses the best what the statement actually does. The fact is still that of all the RDBMS vendors I've thrown this at only Derby and H2 are unable to execute it which suggests the two are exceptions to the rule.
Thanks.
> 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
> Assignee: Bryan Pendleton
> Attachments: anotherTestCast.diff, moreTestCases.diff, 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)