When writing JPA Criteria queries that include a 'group by' and 'concat' (of an expression with a literal) an error is presented:
However, the query is written so that the function is included in both the 'select' and 'group by' clauses of the query.
This issue has been tested with PostgreSQL, Derby, HSQL and H2, and the result is always the same (expect with an H2 database, when the problem does not manifest).
Please find attached a maven project that exemplifies the issue in a number of tests, using HSQL and H2 databases to demonstrate how the results differ; these can be run by executing HSQLTest.class and H2.class, respectively.
The examples, when ran against the HSQL database, will show that without the presence of literals, as well as by writing the query as raw HQL, the queries are valid (and the tests pass). However, when writing a query using JPA Criteria, and using 'CriteriaBuilder.concat' with a literal, the problem occurs - the query is said to be invalid and does not execute successfully.
The 'ConcatTest' class has been commented to indicate the issues that arise when the queries are ran against HSQL or PostgreSQL databases, and what "workarounds" have been used to enable successful execution of the otherwise problematic queries.
|