Numeric value out of range error (22003) on DELETE with expression in WHERE clause

[Vedran Pavić]

Hi,

I've ran into the problem from the subject, which appears to be a bug, using the latest 1.4.192 release.

Given the table:

create table test (

  id bigint primary key,

  big_number bigint not null,

  small_number int not null

)

The following DELETE statement will fail with Numeric value out of range error (22003) if there are records in table:

delete from test

where big_number < ? - small_number * 1000

org.h2.jdbc.JdbcSQLException: Numeric value out of range: "1470033680022"; SQL statement:

delete from test where big_number < ? - small_number * 1000 [22003-192]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)

The same statement will not fail if there aren't any records in the table.

Minimal project to reproduce the error is available here:

https://github.com/vpavic-samples/h2-delete-error-22003

Thanks,

Vedran

[Noel Grandin]

you need to cast small_number to a bigint before doing the multiply

[Vedran Pavić]

I would expect the conversion between the numeric types to occur implicitly since it's obvious what the result type should be.

The given example works without any problems on PostgreSQL, MySQL/MariaDB and Oracle.

We're using a standard SQL to support multiple RDBMS's so adding CAST to the statement would make it incompatible with some other vendors.

[Noel Grandin]

What does the complete stack trace look like?

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Vedran Pavić]

You can see the complete stacktrace in readme of sample project I posted link to in the original comment:

https://github.com/vpavic-samples/h2-delete-error-22003

[Noel Grandin]

At the point in time that we evaluate the expression

? - small_number * 1000

the left side of the minus operation is unknown and the right side is int.

Which means we perform the operation as an int subtraction and fail when we see a value outside that range.

I'm sorry, but I don't see a reasonable fix for this. Changing H2 to use a wider datatype by default is going to
pessimize all other operations which don't need the extra precision unnecessarily.

You are going to have use to use CAST to hint that you want the expression evaluated as BIGINT.

Since CAST is SQL-92 standard, I don't see a problem with this.

[Vedran Pavić]

Thanks for further analysis Noel.

At the point in time that we evaluate the expression
    ? - small_number * 1000
the left side of the minus operation is unknown and the right side is int.

How is it unknown? We're dealing with a PreparedStatement here, and PreparedStatement#setLong clearly indicates the type.

Also keep in mind that, as stated in the original comment and demonstrated in sample project, this statement executes just fine when there are no records in the table.

IMO the syntactic correctness of the expression in WHERE clause should not depend on the contents of the table.

You are going to have use to use CAST to hint that you want the expression evaluated as BIGINT.

Since CAST is SQL-92 standard, I don't see a problem with this.

CAST is standard, however BIGINT is not.

[Noel Grandin]

On 2016/08/02 1:19 PM, Vedran Pavić wrote:
>
> How is it unknown? We're dealing with a PreparedStatement here, and PreparedStatement#setLong clearly indicates the type.
>

Because PreparedStatement is "prepared" before the setLong method is called. Unfortunately, that's just how the API
works, at preparation time we don't have access to type information about the parameters and it has to be inferred.

[Silvio]

The "big_number <" part might be the hint that the RHS of the comparison is big?

[Noel Grandin]

​you could also try something like

delete from test

where big_number - ? < small_number * 1000

which would give the type checking a better chance

[Vedran Pavić]

Hi Noel,

 

Because PreparedStatement is "prepared" before the setLong method is called. Unfortunately, that's just how the API 

works, at preparation time we don't have access to type information about the parameters and it has to be inferred.

Can you elaborate a bit on this?

At the time PreparedStatement#executeUpdate is invoked the parameters have already been set therefore the query execution engine has the knowledge of all parameters and their types.

After all, the thing blows while evaluating conditions in org.h2.command.dml.Delete:72, and the list of parameters is available in there at that point.

To reiterate another point I've already made:

Also keep in mind that, as stated in the original comment and demonstrated in sample project, this statement executes just fine when there are no records in the table.

IMO the syntactic correctness of the expression in WHERE clause should not depend on the contents of the table.

IMO this shows the query execution engine is not doing this the right way.

you could also try something like

delete from test

where big_number - ? < small_number * 1000

I'm not too keen on resorting to such workaround since it could have an effect on execution plan on other RDBMSs.

Also I've found that this kind of problem is only present with H2 and Derby.

[Noel Grandin]

On 2016/08/04 11:49 AM, Vedran Pavić wrote:
> Can you elaborate a bit on this?
>

Notice when the prepareStatement call is executed. That is when we compile the query plan.

I'm sympathetic to your position that the engine should just "do the right thing", but I simply don't have time to make
major modifications to the type inference for a corner case that is easily worked around.

[Vedran Pavić]

Thanks for the feedback.

I understand that addressing this would require a major change.

Regards,

Vedran