PostgreSQL mode: STRING_AGG with prepared statement parameter not working

[coder-hugo]

In version 2 of the h2 database it's not possible anymore to use a prepared statement parameter for the separator of the STRING_AGG function (PostgreSQL mode).
In version 1 you could do the following:

PreparedStatement statement = connection.prepareStatement("select t1.id, string_agg(t2.value, ?) from t1 left join t2 on t2.t1_id = t1.id group by t1.id");
statement.setString(1, ",");

and you will receive a result that will look like this:

| 1 | v1,v2,v3 |
| 2 | v2,v5    |

If you do the same with version 2 of the h2 database, the result looks like this:

| 1 | v1v2v3 |
| 2 | v2v5   |

So somehow the comma gets lost in the prepared statement. If you don't use a prepared statement parameter for the separator but set it fixed to statement (string_agg(t2.value, ',')) you will receive the correct result. So in general the STRING_AGG function in the PostgresSQL mode seems to work.

[katzyn]

This issue appears because PostgreSQL-specific STRING_AGG is translated to standard LISTAGG function supported by H2. But LISTAGG allows only character string literals as separators and H2 doesn't preserve the original expression here and takes its value immediately. Unfortunately, it means parameters cannot work here, so this implementation needs to be extended to support JDBC parameters too.

[coder-hugo]

Thanks, for the fast reply. I just had a short look into the code, to check if I could provide a PR for this. But it looks like there is currently no aggregate function that supports JDBC parameters, so it will be a larger change. Am I right?

[katzyn]

Almost all of them support any expressions including parameters. In H2, unlike in the most of JDBC drivers, parameters are special kind of expressions. But LISTAGG handles some arguments as direct values and not as expressions.

[coder-hugo]

Thanks for the fast fix. Is there any schedule for the next release that will contain this fix?