Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 17 Nov 2016 01:36:58 +0000 (UTC)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12994890.1470315830000.299598.1479346618422@Atlassian.JIRA>
In-Reply-To: <JIRA.12994890.1470315830000@Atlassian.JIRA>
References: <JIRA.12994890.1470315830000@Atlassian.JIRA> <JIRA.12994890.1470315830268@arcas>
Subject: [jira] [Commented] (DERBY-6902) Value out of range error (22003) on
 DELETE with expression in WHERE clause
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 17 Nov 2016 01:37:00 -0000


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

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

Hi Vedran,

BIGINT is a Standard datatype. See the list of Standard datatypes in the 2011 Standard, part 2, section 4.4.1 (Introduction to Number): NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, or DOUBLE PRECISION.

My guess is that the compiler (during Connection.prepareStatement()) guessed a datatype for the ? (the guessed datatype was INTEGER). That decision was already baked in by the time that you called PreparedStatement.setLong().

If your application has to run against a database which doesn't support BIGINT, then you might consider CASTing the ? to INTEGER and invoking delete.setInt().

Hope this helps,
-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
>            Assignee: Bryan Pendleton
>         Attachments: anotherTestCast.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)