H2 2.1.210: Query with Parameters throws NPE at org.h2.command.query.Query.getParameterValues(Query.java:449) #3414
Description
The query below worked well up to 2.1.204 but stopped to work on 2.1.210.
Query:
INSERT /*+ PARALLEL APPEND DYNAMIC_SAMPLING(0) */ INTO cfe.ledger_branch_balance
WITH scope AS (
SELECT *
FROM cfe.accounting_scope
WHERE id_status = 'C'
AND id_accounting_scope_code = ? )
, ex AS (
SELECT *
FROM cfe.execution
WHERE id_status = 'R'
AND value_date = ( SELECT Max( value_date )
FROM cfe.execution
WHERE id_status = 'R'
AND ( ? IS NULL
OR value_date <= ? ) ) )
, fxr AS (
SELECT id_currency_from
, fxrate
FROM common.fxrate_hst f
INNER JOIN ex
ON f.value_date <= ex.value_date
WHERE f.value_date = ( SELECT Max( value_date )
FROM common.fxrate_hst
WHERE id_currency_from = f.id_currency_from
AND id_currency_into = f.id_currency_into
AND value_date <= ex.value_date )
AND id_currency_into = ?
UNION ALL
SELECT ?
, 1
FROM dual )
SELECT /*+ PARALLEL DYNAMIC_SAMPLING(0) */
scope.id_accounting_scope
, ex.value_date
, ex.posting_date
, a.gl_level
, a.code
, b.description
, c.balance_bc
FROM ex
, scope
INNER JOIN cfe.ledger_branch_branch a
ON a.id_accounting_scope = scope.id_accounting_scope
AND a.code = a.code_inferior
INNER JOIN cfe.ledger_branch b
ON b.id_accounting_scope = scope.id_accounting_scope
AND b.code = a.code
INNER JOIN ( SELECT b.code
, Round( Sum( d.balance * fxr.fxrate ), 2 ) balance_bc
FROM scope
INNER JOIN cfe.ledger_branch_branch b
ON b.id_accounting_scope = scope.id_accounting_scope
INNER JOIN cfe.ledger_account c
ON b.code_inferior = c.code
AND c.id_accounting_scope_code = scope.id_accounting_scope_code
INNER JOIN ( SELECT id_account
, Sum( amount ) balance
FROM ( SELECT id_account_credit id_account
, amount
FROM cfe.ledger_account_entry
INNER JOIN ex
ON ledger_account_entry.posting_date <= ex.posting_date
INNER JOIN cfe.ledger_account c
ON ledger_account_entry.id_account_credit = c.id_account
AND c.id_accounting_scope_code = ?
UNION ALL
SELECT id_account_debit
, - amount
FROM cfe.ledger_account_entry
INNER JOIN ex
ON ledger_account_entry.posting_date <= ex.posting_date
INNER JOIN cfe.ledger_account c
ON ledger_account_entry.id_account_debit = c.id_account
AND c.id_accounting_scope_code = ?
UNION ALL
SELECT id_account_credit id_account
, amount
FROM cfe.ledger_acc_entry_manual
INNER JOIN ex
ON ledger_acc_entry_manual.value_date <= ex.value_date
INNER JOIN cfe.ledger_account c
ON ledger_acc_entry_manual.id_account_credit = c.id_account
AND c.id_accounting_scope_code = ?
UNION ALL
SELECT id_account_debit
, - amount
FROM cfe.ledger_acc_entry_manual
INNER JOIN ex
ON ledger_acc_entry_manual.value_date <= ex.value_date
INNER JOIN cfe.ledger_account c
ON ledger_acc_entry_manual.id_account_debit = c.id_account
AND c.id_accounting_scope_code = ? )
GROUP BY id_account ) d
ON c.id_account = d.id_account
INNER JOIN fxr
ON c.id_currency = fxr.id_currency_from
GROUP BY b.code ) c
ON c.code = a.codeApplied Parameters:
[IFRS9, 2022-02-01, 2022-02-01, NGN, NGN, IFRS9, IFRS9, IFRS9, IFRS9]
Exception:
Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException"; SQL statement:
SQL as above [50000-210]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:573)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
at org.h2.message.DbException.get(DbException.java:216)
at org.h2.message.DbException.convert(DbException.java:414)
at org.h2.command.Command.executeUpdate(Command.java:264)
at org.h2.server.TcpServerThread.process(TcpServerThread.java:408)
at org.h2.server.TcpServerThread.run(TcpServerThread.java:191)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.lang.NullPointerException
at org.h2.command.query.Query.getParameterValues(Query.java:449)
at org.h2.command.query.Query.query(Query.java:478)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.expression.Subquery.getValue(Subquery.java:46)
at org.h2.index.IndexCondition.getCurrentValue(IndexCondition.java:139)
at org.h2.index.IndexCursor.prepare(IndexCursor.java:107)
at org.h2.index.IndexCursor.find(IndexCursor.java:153)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1825)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:728)
at org.h2.command.query.Select.queryWithoutCache(Select.java:833)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:494)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.index.ViewIndex.find(ViewIndex.java:270)
at org.h2.index.ViewIndex.find(ViewIndex.java:153)
at org.h2.index.IndexCursor.find(IndexCursor.java:161)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.table.TableFilter.next(TableFilter.java:464)
at org.h2.table.TableFilter.next(TableFilter.java:464)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1825)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:728)
at org.h2.command.query.Select.queryWithoutCache(Select.java:833)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:494)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.command.query.SelectUnion.queryWithoutCache(SelectUnion.java:171)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:471)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.command.query.SelectUnion.queryWithoutCache(SelectUnion.java:171)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:471)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.command.query.SelectUnion.queryWithoutCache(SelectUnion.java:171)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:471)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.index.ViewIndex.find(ViewIndex.java:270)
at org.h2.index.ViewIndex.find(ViewIndex.java:153)
at org.h2.index.IndexCursor.find(IndexCursor.java:161)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.command.query.Select.gatherGroup(Select.java:517)
at org.h2.command.query.Select.queryGroup(Select.java:488)
at org.h2.command.query.Select.queryWithoutCache(Select.java:828)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:494)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.index.ViewIndex.find(ViewIndex.java:270)
at org.h2.index.ViewIndex.find(ViewIndex.java:153)
at org.h2.index.IndexCursor.find(IndexCursor.java:161)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.command.query.Select.gatherGroup(Select.java:517)
at org.h2.command.query.Select.queryGroup(Select.java:488)
at org.h2.command.query.Select.queryWithoutCache(Select.java:828)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:494)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.index.ViewIndex.find(ViewIndex.java:270)
at org.h2.index.ViewIndex.find(ViewIndex.java:153)
at org.h2.index.IndexCursor.find(IndexCursor.java:161)
at org.h2.table.TableFilter.next(TableFilter.java:394)
at org.h2.table.TableFilter.next(TableFilter.java:464)
at org.h2.table.TableFilter.next(TableFilter.java:464)
at org.h2.table.TableFilter.next(TableFilter.java:464)
at org.h2.command.query.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1825)
at org.h2.result.LazyResult.hasNext(LazyResult.java:78)
at org.h2.result.FetchedResult.next(FetchedResult.java:34)
at org.h2.command.query.Select.queryFlat(Select.java:728)
at org.h2.command.query.Select.queryWithoutCache(Select.java:833)
at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
at org.h2.command.query.Query.query(Query.java:494)
at org.h2.command.query.Query.query(Query.java:457)
at org.h2.command.dml.Insert.insertRows(Insert.java:199)
at org.h2.command.dml.Insert.update(Insert.java:135)
at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:61)
at org.h2.command.CommandContainer.update(CommandContainer.java:174)
at org.h2.command.Command.executeUpdate(Command.java:252)
... 3 more