db-derby-dev mailing list archives

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

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

Vedran Pavic commented on DERBY-6902:
-------------------------------------

Hi,

I'd just like the stress again [the original background|https://issues.apache.org/jira/browse/DERBY-6902?focusedCommentId=15409077&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-15409077]
to this issue:

{quote}
To give more background, we need this statement in a library that supports multiple RDBMSs
so we need to use standard SQL and have found this issue with Derby and H2. PostgreSQL, Oracle,
MySQL/MariaDB and HSQLDB handle it without any problems.
{quote}

Using {{CAST}} will not result in a standard and portable SQL due to the fact that even though
{{CAST}} is standard, {{BIGINT}} isn't.

Also I'm not sure I understand this claim:

{quote}
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.
{quote}

How's there not enough information when the parameter is clearly defined as {{long}}, which
translates to {{BIGINT}}?

{code:java}
delete.setLong(1, System.currentTimeMillis());
{code}

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, 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