Same query, sometimes I get JdbcSQLSyntaxErrorException, sometimes not :o

[ciphe...@gmail.com]

Hello,

I have run into a very strange situation and would appreciate any ideas.

I'm using H2 1.4.200 with a Linux server and Android client. The same query appears to sometimes produce a JdbcSQLSyntaxErrorException - but sometimes not.

Please ignore my SQLfor the most part - I assure that I can run the same SQL successfully in a web console, and the Android app that has currently failed running it, also typically runs it. Sucess or failure seems to depend on "time of day and wind direction". Can you identify any feature that I'm using that might cause such behaviour?

P.S.

Stack trace and execution plan (from a success case, I have no execution plan from a failed case) appended below.

-------------- stack trace from an error case ----------------

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "IWBATCHES.NAME" must be in the GROUP BY list; SQL statement:
   SELECT iwbatches.id, iwbatches.name, iwbatches.company_id, iwbatches.opener_id o_id, iwbatches.opened_when, COALESCE(CONCAT(openers.firstname,' ',openers.lastname),'') o_by, COALESCE(TO_CHAR(iwbatches.opened_when,'YYYY-MM-DD hh24:mi'),'') o_when, companies.name company_name, companies.code company_code, companies.streetaddress company_streetaddress, companies.settlement company_settlement, companies.regcode company_regcode, companies.vatcode company_vatcode, companies.contact company_contact, companies.email company_email, companies.phone company_phone, COUNT(iwbatchrows.id) rows FROM iwbatches LEFT JOIN iwbatchrows ON iwbatches.id = iwbatchrows.iwbatch_id AND iwbatchrows.needed IS NOT NULL LEFT JOIN persons openers ON iwbatches.opener_id = openers.id LEFT JOIN companies ON iwbatches.company_id = companies.id WHERE iwbatches.finished_when IS NULL AND (iwbatches.opened_when >= DATEADD('DAY',(SELECT CAST(value AS INT)  FROM cfg WHERE key = 'iwbatches.oldest.days'),CURRENT_TIMESTAMP)) AND (iwbatches.opened_when <= DATEADD('DAY',(SELECT CAST(value AS INT)  FROM cfg WHERE key = 'iwbatches.youngest.days'),CURRENT_TIMESTAMP)) GROUP BY iwbatches.id ORDER BY iwbatches.id ASC [90016-200]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:576)
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
   at org.h2.message.DbException.get(DbException.java:205)
   at org.h2.message.DbException.get(DbException.java:181)
   at org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:182)
   at org.h2.command.dml.Select.updateAgg(Select.java:545)
   at org.h2.command.dml.Select$LazyResultGroupSorted.<init>(Select.java:1888)
   at org.h2.command.dml.Select.queryGroupSorted(Select.java:314)
   at org.h2.command.dml.Select.queryWithoutCache(Select.java:837)
   at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:201)
   at org.h2.command.dml.Query.query(Query.java:489)
   at org.h2.command.dml.Query.query(Query.java:451)
   at org.h2.command.CommandContainer.query(CommandContainer.java:285)
   at org.h2.command.Command.executeQuery(Command.java:195)
   at org.h2.server.TcpServerThread.process(TcpServerThread.java:343)
   at org.h2.server.TcpServerThread.run(TcpServerThread.java:183)
   at java.base/java.lang.Thread.run(Thread.java:834)
   at org.h2.engine.SessionRemote.done(SessionRemote.java:629)
   at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:176)
   at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:111)
   at shared.PrepStat.executeQuery(PrepStat.java:353)
   at shared.PrepStat.getRecords(PrepStat.java:568)
   at shared.Db.list(Db.java:758)
   at ladu.client.IWBatchesOpenActivity.listDocuments(IWBatchesOpenActivity.java:135)
   at ladu.client.IWBatchesOpenActivity.setupVariables(IWBatchesOpenActivity.java:127)
   at ladu.client.IWBatchesOpenActivity.onCreate(IWBatchesOpenActivity.java:84)
   at android.app.Activity.performCreate(Activity.java:7144)
   at android.app.Activity.performCreate(Activity.java:7135)
   at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1272)
   at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2932)
   at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3087)
   at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
   at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
   at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1817)
   at android.os.Handler.dispatchMessage(Handler.java:106)
   at android.os.Looper.loop(Looper.java:193)
   at android.app.ActivityThread.main(ActivityThread.java:6762)
   at java.lang.reflect.Method.invoke(Native Method)
   at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)

   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)

-------------- execution plan from a success case ----------------------

SELECT
    "IWBATCHES"."ID",
    "IWBATCHES"."NAME",
    "IWBATCHES"."COMPANY_ID",
    "IWBATCHES"."OPENER_ID" AS "O_ID",
    "IWBATCHES"."OPENED_WHEN",
    COALESCE(CONCAT("OPENERS"."FIRSTNAME", ' ', "OPENERS"."LASTNAME"), '') AS "O_BY",
    COALESCE(TO_CHAR("IWBATCHES"."OPENED_WHEN", 'YYYY-MM-DD hh24:mi'), '') AS "O_WHEN",
    "COMPANIES"."NAME" AS "COMPANY_NAME",
    "COMPANIES"."CODE" AS "COMPANY_CODE",
    "COMPANIES"."STREETADDRESS" AS "COMPANY_STREETADDRESS",
    "COMPANIES"."SETTLEMENT" AS "COMPANY_SETTLEMENT",
    "COMPANIES"."REGCODE" AS "COMPANY_REGCODE",
    "COMPANIES"."VATCODE" AS "COMPANY_VATCODE",
    "COMPANIES"."CONTACT" AS "COMPANY_CONTACT",
    "COMPANIES"."EMAIL" AS "COMPANY_EMAIL",
    "COMPANIES"."PHONE" AS "COMPANY_PHONE",
    COUNT("IWBATCHROWS"."ID") AS "ROWS"
FROM "PUBLIC"."IWBATCHES"
    /* PUBLIC.PRIMARY_KEY_AC */
    /* WHERE (IWBATCHES.OPENED_WHEN >= DATEADD('DAY', (SELECT
        CAST(VALUE AS INTEGER)
    FROM PUBLIC.CFG
        /++ PUBLIC.CFG_KEY: KEY = 'iwbatches.oldest.days' ++/
    WHERE KEY = 'iwbatches.oldest.days'), CURRENT_TIMESTAMP))
        AND ((IWBATCHES.FINISHED_WHEN IS NULL)
        AND (IWBATCHES.OPENED_WHEN <= DATEADD('DAY', (SELECT
        CAST(VALUE AS INTEGER)
    FROM PUBLIC.CFG
        /++ PUBLIC.CFG_KEY: KEY = 'iwbatches.youngest.days' ++/
    WHERE KEY = 'iwbatches.youngest.days'), CURRENT_TIMESTAMP)))
    */
LEFT OUTER JOIN "PUBLIC"."IWBATCHROWS"
    /* PUBLIC.IDX_IWBATCHROWS_IWBATCH_ID_ASC: IWBATCH_ID = IWBATCHES.ID */
    ON ("IWBATCHROWS"."NEEDED" IS NOT NULL)
    AND ("IWBATCHES"."ID" = "IWBATCHROWS"."IWBATCH_ID")
LEFT OUTER JOIN "PUBLIC"."PERSONS" "OPENERS"
    /* PUBLIC.PRIMARY_KEY_2: ID = IWBATCHES.OPENER_ID */
    ON "IWBATCHES"."OPENER_ID" = "OPENERS"."ID"
LEFT OUTER JOIN "PUBLIC"."COMPANIES"
    /* PUBLIC.PRIMARY_KEY_5: ID = IWBATCHES.COMPANY_ID */
    ON "IWBATCHES"."COMPANY_ID" = "COMPANIES"."ID"
WHERE ("IWBATCHES"."OPENED_WHEN" <= DATEADD('DAY', (SELECT
    CAST("VALUE" AS INTEGER)
FROM "PUBLIC"."CFG"
    /* PUBLIC.CFG_KEY: KEY = 'iwbatches.youngest.days' */
WHERE "KEY" = 'iwbatches.youngest.days'), CURRENT_TIMESTAMP))
    AND (("IWBATCHES"."FINISHED_WHEN" IS NULL)
    AND ("IWBATCHES"."OPENED_WHEN" >= DATEADD('DAY', (SELECT
    CAST("VALUE" AS INTEGER)
FROM "PUBLIC"."CFG"
    /* PUBLIC.CFG_KEY: KEY = 'iwbatches.oldest.days' */
WHERE "KEY" = 'iwbatches.oldest.days'), CURRENT_TIMESTAMP)))
GROUP BY "IWBATCHES"."ID"
ORDER BY 1
/* group sorted */

[Evgenij Ryazanov]

Hello.

It may be surprising, but validity of some queries depends on the data.

H2 and some other DBMS support optional feature T301, “Functional dependencies” from the SQL Standard. When this feature is supported select expressions and where clause may reference non-aggregated columns when each such column functionally depends on grouping columns (has the same value in all source rows in each particular group). Therefore when your data satisfies this condition, there is no error. When some column has different values within a group such exception is raised.

In DBMS without this optional feature queries like that must be rejected unconditionally.

SELECT A, COUNT(C) FROM TEST GROUP BY A is a valid query.

SELECT A, B, COUNT(C) FROM TEST GROUP BY A, B is an another valid query.

SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS without the mentioned feature, and may be either valid or invalid for DBMS with this feature; it is valid only when rows with the same value of A have the same value of B.

You need to fix your query somehow.

[Andreas Reichel]

Greetings.

On Sat, 2021-05-22 at 21:26 -0700, Evgenij Ryazanov wrote:

It may be surprising, but validity of some queries depends on the data.

H2 and some other DBMS support optional feature T301, “Functional dependencies” from the SQL Standard. 

Indeed!

SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS without the mentioned feature

@OP: You can rewrite your query like below in order to make it work in general (without depending on T301).

You can also use that form to identify any duplicates easily (using a HAVING COUNT(*)>1), which would break you original SQL Statement.

Good luck.

WITH t AS (
        SELECT DISTINCT
            iwbatches.id
            , iwbatches.name
            , iwbatches.company_id
            , iwbatches.opener_id o_id
            , iwbatches.opened_when
            , Coalesce( Concat( openers.firstname, ' ', openers.lastname ), '' ) o_by
            , Coalesce( To_Char( iwbatches.opened_when, 'YYYY-MM-DD hh24:mi' ), '' ) o_when

            , companies.name company_name
            , companies.code company_code
            , companies.streetaddress company_streetaddress
            , companies.settlement company_settlement
            , companies.regcode company_regcode
            , companies.vatcode company_vatcode
            , companies.contact company_contact
            , companies.email company_email
            , companies.phone company_phone

            , iwbatchrows.id rows_id

        FROM iwbatches
            LEFT JOIN iwbatchrows
                ON iwbatches.id = iwbatchrows.iwbatch_id
                    AND iwbatchrows.needed IS NOT NULL
            LEFT JOIN persons openers
                ON iwbatches.opener_id = openers.id
            LEFT JOIN companies
                ON iwbatches.company_id = companies.id
        WHERE iwbatches.finished_when IS NULL

            AND ( iwbatches.opened_when >= Dateadd( 'DAY', (    SELECT CAST(value AS INT)

                                                                FROM cfg
                                                                WHERE key = 'iwbatches.oldest.days' ), CURRENT_TIMESTAMP ) )

            AND ( iwbatches.opened_when <= Dateadd( 'DAY', (    SELECT CAST(value AS INT)
                                                                FROM cfg
                                                                WHERE key = 'iwbatches.youngest.days' ), CURRENT_TIMESTAMP ) ) )
    , aggregate AS (
        SELECT  id
                , Count( rows_id ) rows
        FROM t
        GROUP BY id )
SELECT  aggregate.id
        , aggregate.rows
        , t.id
        , t.name
        , t.company_id
        , t.o_id
        , t.opened_when
        , t.o_by
        , t.o_when
        , t.company_name
        , t.company_code
        , t.company_streetaddress
        , t.company_settlement
        , t.company_regcode
        , t.company_vatcode
        , t.company_contact
        , t.company_email
        , t.company_phone
FROM aggregate
    LEFT JOIN t
        ON aggregate.id = t.id
ORDER BY    aggregate
            , id
;

[ciphe...@gmail.com]

Wow, thanks for educating me about new SQL features. :)

I suspected this was data-dependent, but couldn't figure out how this was possible, or what exactly was data-dependent.

P.S.

Also, many thanks for the new query you proposed. :) It didn't run on first try, but I'll adjust it and get things working soon. :)

[ciphe...@gmail.com]

P.S.

Yay, it works now - thank you everyone, I'm really grateful for your advise. :)