Performance issue with some H2 queries containing COALESCE clause

[flambchap]

Hi. Our users experience some performance issues with H2 queries (see below).

Steps to reproduce the behavior:
Run this query:

select 
  cast((
    select coalesce(
      json_arrayagg(json_array("v0") order by "t"."v0"),
      json_array(null on null)
    )
    from (
      select 2 "v0"
      union
      select 4 "ID"
    ) "t"
  ) as text);

It takes a considerable amount of time for JSqlParser to parse this query. Profiling shows that the parser spends a ton of time inside net.sf.jsqlparser.parser.CCJSqlParser.jj_add_error_token(). However, the query

select 
  cast((
    select 
      json_arrayagg(json_array("v0") order by "t"."v0")
    from (
      select 2 "v0"
      union
      select 4 "ID"
    ) "t"
  ) as text);

(without COALESCE()) is parsed without any problems. See the ticket in the link above for some more profiling info.

System

• H2
• SapMachine 11.0.11, AdoptOpenJDK 11.0.11
• JSqlParser 4.1

[manticore-projects]

Greetings, thank you for reporting. I have responded to the original case on the DBeaver's site already.
Its not about the Coalesce, but about the NULL ON NULL aggregate function. The H2 Ordered Aggregates are not supported yet, please see issue #1183. I will try to work on this asap.

[flambchap]

Thank you for your investigation.