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. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e4018b89-537f-46c1-ad7f-4a9199be4255%40googlegroups.com.