Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

H2 2.1.210: Query with Parameters throws NPE at org.h2.command.query.Query.getParameterValues(Query.java:449) #3414

Closed
manticore-projects opened this issue Feb 2, 2022 · 2 comments · Fixed by #3420
Closed

H2 2.1.210: Query with Parameters throws NPE at org.h2.command.query.Query.getParameterValues(Query.java:449) #3414

manticore-projects opened this issue Feb 2, 2022 · 2 comments · Fixed by #3420

Comments

@manticore-projects
Copy link
Contributor

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.code

Applied 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
@katzyn
Copy link
Contributor

katzyn commented Feb 2, 2022

Please, provide a complete test case with definitions of your schema and related tables.

@manticore-projects
Copy link
Contributor Author

Please, provide a complete test case with definitions of your schema and related tables.

Sure, please see attached. It comes in 2 versions:

  1. A Test Case with the original DB with Schema and Data and the query to trigger the NPE (please copy the attached DB into the /tmp/ folder or adjust the DB URL)
  2. A virtual Test Case re-building the database from DDL and DML statements (max. 100 records each table). Interestingly this does NOT throw the NPE, for whatever reason.
    issue3414.zip

@katzyn katzyn mentioned this issue Feb 2, 2022
Merged
@ThorbenLindhauer ThorbenLindhauer mentioned this issue Feb 17, 2022
Closed
@katzyn katzyn mentioned this issue Apr 8, 2022
Closed
@Helen2022a Helen2022a mentioned this issue Jun 4, 2022
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix NPE in Query.getParameterValues() katzyn/h2database
2 participants
@manticore-projects @katzyn