Version 1.4.197 attempts to convert varchar primary keys to int, throwing conversion exceptions - version 1.4.199 fails to load data entirely

[Tim Fielder]

I'm working with tables that are keyed on varchar columns.  The values are strictly numeric, but leading zeroes are significant and as a result converting the values to bigint results in collisions.  H2, however, seems to want them to be ints because it's attempting to convert them and throwing conversion errors.  Here's an example:

org.h2.jdbc.JdbcSQLException: Data conversion error converting "11844266081"; SQL statement:
SELECT title, abstract FROM document WHERE docid = 0001179973; [22018-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.value.Value.convertTo(Value.java:1116)
    at org.h2.value.Value.convertTo(Value.java:617)
    at org.h2.value.Value.convertTo(Value.java:592)
    at org.h2.table.Table.compareTypeSafe(Table.java:1186)
    at org.h2.index.BaseIndex.compareValues(BaseIndex.java:364)
    at org.h2.index.BaseIndex.compareRows(BaseIndex.java:299)
    at org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.next(MVSecondaryIndex.java:514)
    at org.h2.index.IndexCursor.next(IndexCursor.java:316)
    at org.h2.table.TableFilter.next(TableFilter.java:502)
    at org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1452)
    at org.h2.result.LazyResult.hasNext(LazyResult.java:79)
    at org.h2.result.LazyResult.next(LazyResult.java:59)
    at org.h2.command.dml.Select.queryFlat(Select.java:527)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:633)
    at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)
    at org.h2.command.dml.Query.query(Query.java:371)
    at org.h2.command.dml.Query.query(Query.java:333)
    at org.h2.command.CommandContainer.query(CommandContainer.java:114)
    at org.h2.command.Command.executeQuery(Command.java:202)
    at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:85)
    at indexing.IndexDB.query(IndexDB.java:58)
    at indexing.ResultFrame.lambda$13(ResultFrame.java:300)
    at util.Threading$1.run(Threading.java:45)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NumberFormatException: For input string: "11844266081"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.lang.Integer.parseInt(Integer.java:583)
    at java.lang.Integer.parseInt(Integer.java:615)
    at org.h2.value.Value.convertTo(Value.java:1061)
    ... 23 more

It's not entirely clear to me where the "11844266081" input string is derived from, nor why it's being run through Integer.parseInt() - every single column in the database is a varchar.

I tried updating to the 1.4.199 release, and now the database can't seem to find any data at all.  Making absolutely no other changes to the code other than loading a different .jar file, I rebuilt the database and now I get the following error when running the same query:

org.h2.jdbc.JdbcSQLNonTransientException: No data is available [2000-199]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:447)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
    at org.h2.message.DbException.get(DbException.java:205)
    at org.h2.message.DbException.get(DbException.java:181)
    at org.h2.message.DbException.get(DbException.java:170)
    at org.h2.jdbc.JdbcResultSet.checkOnValidRow(JdbcResultSet.java:3258)
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3271)
    at org.h2.jdbc.JdbcResultSet.getString(JdbcResultSet.java:296)
    at indexing.ResultFrame.lambda$13(ResultFrame.java:302)
    at util.Threading$1.run(Threading.java:45)
    at java.lang.Thread.run(Thread.java:748)

I'm pretty much at a loss as to what to do here.  The database that is throwing the "no data is available" exception contains over 6GB of data.

[Noel Grandin]

Use the SCRIPT coomand or tool to dump and restore the db, that is more reliable.

[Tim Fielder]

On Friday, July 19, 2019 at 12:47:00 PM UTC-4, Noel Grandin wrote:

Use the SCRIPT coomand or tool to dump and restore the db, that is more reliable.

I'm not trying to load a database built in v1.4.197 in .199 - I rebuilt it entirely from the raw xml that I'm parsing.  Version 1.4.199 literally can't find any data in a database it generated from whole cloth less than 10 seconds ago.

[Noel Grandin]

OK, that's better, so the problem with .199 is not some kind of disk format issue, the problem is that some query of yours is returning no data.

Unfortunately your stack trace doesn't tell us anything other than that the result set is empty, so unless you can generate some kind of reduced test case, I can't really help you much.

The weird stack trace you are seeing in .197 is because you have a varchar column that you are querying against, but you are passing in a very large integer value, which tends to hit a rather unfortunate fallback path in our conversion code (which has been fixed in master).

You would do better to write your query something like

    SELECT title, abstract FROM document WHERE docid = '0001179973'

so that the comparison happens on a char basis, instead of having H2 trying to convert both sides to some integer type.

In fact, that might fix your problem with .199 too

[Tim Fielder]

On Friday, July 19, 2019 at 3:49:24 PM UTC-4, Noel Grandin wrote:

The weird stack trace you are seeing in .197 is because you have a varchar column that you are querying against, but you are passing in a very large integer value, which tends to hit a rather unfortunate fallback path in our conversion code (which has been fixed in master).

You would do better to write your query something like

    SELECT title, abstract FROM document WHERE docid = '0001179973'

so that the comparison happens on a char basis, instead of having H2 trying to convert both sides to some integer type.

In fact, that might fix your problem with .199 too

 

 Thanks.  I'll try it when I get back to work on Monday and get back to you.

[Tim Fielder]

Wrapping all of the %s in my format strings with single quotes ' seems to have solved the problem, at least in .199 - haven't tested with .197.  Thanks for the help.