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 */
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/09a211f8-1ae5-49c4-8c7a-d7ed97a17c26n%40googlegroups.com.
