Re: [h2] ArrayIndexOutOfBoundsException with union query

[Noel Grandin]

I have no idea what the underlying problem is.
But if you could create a standalone test case for it I could probably track it 
down.

In the meantime you could probably work around it by bumping up your cache size.
Append something like this to your URL
   ;CACHE_SIZE=1024
where the size is in kilobytes.

On 2014-05-21 10:42, christoff.schm...@finaris.de wrote:


one of our customers is facing the following problem while using our product, 
which uses an embedded H2 database (v.
1.3.173).



--
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 post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[h2] ArrayIndexOutOfBoundsException with union query

[christoff . schmitz]

Hi,

one of our customers is facing the following problem while using our 
product, which uses an embedded H2 database (v. 1.3.173).

The query looks like this:

SELECT INITIAL_FIXED_VOL, FIXED_YIELD_4AVG, FIXED_ORIGMAT_4AVG,
INITIAL_FLOAT_VOL, FLOAT_YIELD_4AVG, FLOAT_ORIGMAT_4AVG
FROM REPORT_0.KONTOKORRENT_FINAL_1
UNION
SELECT INITIAL_FIXED_VOL, FIXED_YIELD_4AVG, FIXED_ORIGMAT_4AVG,
INITIAL_FLOAT_VOL, FLOAT_YIELD_4AVG, FLOAT_ORIGMAT_4AVG
FROM REPORT_0.TERMINGELD_FINAL_1
UNION
SELECT INITIAL_FIXED_VOL, FIXED_YIELD_4AVG, FIXED_ORIGMAT_4AVG,
INITIAL_FLOAT_VOL, FLOAT_YIELD_4AVG, FLOAT_ORIGMAT_4AVG
FROM REPORT_0.REPOLEIHE_FINAL_1

The 3 objects in the from clauses are views that refer to a table 
function.

The following exception is thrown by H2 (full stack trace at the end of 
this email, it is quite complicated since the mentioned table functions 
execute code within the H2 database):

Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048
  at org.h2.store.Data.writeVarLong(Data.java:1243)
  at org.h2.store.Data.writeValue(Data.java:577)
  at org.h2.store.Data.writeValue(Data.java:641)
  at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:387)
  at org.h2.index.PageBtreeLeaf.writeData(PageBtreeLeaf.java:292)
  at org.h2.index.PageBtreeLeaf.write(PageBtreeLeaf.java:266)
  at org.h2.store.PageStore.writeBack(PageStore.java:1015)
  at org.h2.util.CacheLRU.removeOld(CacheLRU.java:209)
  at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:138)
  at org.h2.util.CacheLRU.put(CacheLRU.java:113)
  at org.h2.store.PageStore.getPage(PageStore.java:832)
  at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:231)
  at org.h2.index.PageDataNode.getNextPage(PageDataNode.java:231)
  at org.h2.index.PageDataLeaf.getNextPage(PageDataLeaf.java:396)
  at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:93)
  at org.h2.index.PageDataCursor.next(PageDataCursor.java:52)
  at org.h2.index.IndexCursor.next(IndexCursor.java:271)
  at org.h2.table.TableFilter.next(TableFilter.java:359)
  at org.h2.command.dml.Select.queryFlat(Select.java:518)
  at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
  at org.h2.command.dml.Query.query(Query.java:314)
  at org.h2.command.dml.Query.query(Query.java:284)
  at org.h2.command.dml.Query.query(Query.java:36)
  at org.h2.command.CommandContainer.query(CommandContainer.java:91)
  at org.h2.command.Command.executeQuery(Command.java:195)
  ... 156 more

However, this problem does not occur, when UNION ALL is used instead of 
UNION. 
A workaround for my customer, but maybe you can extract useful 
information. 
Unfortunately, I cannot provide a simple reproduction code for this.



com.rapidrep.exception.SQLScriptletException: Allgemeiner Fehler:
"java.lang.ArrayIndexOutOfBoundsException: 2048"
General error: "java.lang.ArrayIndexOutOfBoundsException: 2048"; SQL
statement:


SELECT B.*, 'Y' AS SECURED,



( CASE
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (1=1))

THEN  'OTHER'
ELSE NULL
END ) AS
KUNDENGRUPPE


,



( CASE
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (1=1))
THEN  DATE '2013-12-31'
ELSE NULL
END ) AS
TRADE_DATE


,



( CASE
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (1=1))
THEN  DATE '2014-01-31'
ELSE NULL
END ) AS
MATURITY_DATE


,



( CASE
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (RIGHT( FINANZBESTANDSWAEHRUNG, 3) IN
('EUR', 'DEM', 'FFR', 'ITL')))

THEN  'EUR'
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (1=1))

THEN   RIGHT( FINANZBESTANDSWAEHRUNG, 3)
ELSE NULL
END ) AS
CURRENCY


,



( CASE
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (KONS_OHNE_R_V = 'nein'))

THEN  'J'
WHEN ((LIEFERUNG LIKE  'BCA_%' ) AND (1=1))

THEN  'N'
ELSE NULL
END ) AS
FG_EXTERN


,



( CASE
WHEN ((1=1) AND (1=1))

THEN  'FLOAT'
ELSE NULL
END ) AS
LEGTYPE


 FROM REPORT_0.KONTOKORRENT_1 B

 [5-173]
  at 
com.rapidrep.kernel.internaldb.common.tablefunctions.dataelement.DataElementTableFunctionManager.readTableFunction(DataElementTableFunctionManager.java:217)
  at 
com.rapidrep.kernel.internaldb.h2.tablefunctions.H2DataElementTableFunctionManager.readTableFunction(H2DataElementTableFunctionManager.java:98)
  at sun.reflect.GeneratedMethodAccessor54.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at 
org.h2.engine.FunctionAlias$JavaMethod.getValue(FunctionAlias.java:411)
  at org.h2.expression.JavaFunction.getValue(JavaFunction.java:39)
  at org.h2.table.FunctionTable.getValueResultSet(FunctionTable.java:209)
  at org.h2.table.FunctionTable.getResult(FunctionTable.java:180)
  at org.h2.index.FunctionIndex.find(FunctionIndex.java:50)
  at org.h2.index.BaseIndex.find(BaseIndex.java:126)
  at org.h2.index.IndexCursor.find(IndexCursor.java:155)
  at org.h2.table.TableFilter.next(TableFilter.java:328)
  at org.h2.command.dml.Select.queryFlat(Select.java:518)
  at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
  at org.h2.command.dml.Query.query(Query.java:314)
  at org.h2.command.dml.Query.query