GROUP BY CASE WHEN works in JPA not in Cuba

Support
#1

Hi
Hereunder two tests to illustrate it, the first one will not work, whereas the second will.
It should be written in the documentation.
Regards
Michael

    @Test
    public void selectCaseWhenGroupCUBA() {
        String query = "select " +
                "(case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) as date" +
                ", SUM(e.grossAmount) " +
                "from busy$CustomerInvoiceSimpleLine e " +
                "group by (case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) " +
                "order by date";
        persistence.createTransaction().execute((em) -> {
            dump(em.createQuery(query).getResultList());
        });
    }

    @Test
    public void selectCaseWhenGroupJPA() {
        String query = "select " +
                "(case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) as date" +
                ", SUM(e.grossAmount) " +
                "from busy$CustomerInvoiceSimpleLine e " +
                "group by (case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) " +
                "order by date";
        persistence.createTransaction().execute((em) -> {
            dump(em.getDelegate().createQuery(query).getResultList());
        });
    }
#3

Hi, Michael!
We do our best to validate queries in accordance with the JPA standard.
According to the JPA 2.0 specification (JSR 317, p159) GROUP BY syntax is as follows:

groupby_clause ::= GROUP BY groupby_item {, groupby_item}* groupby_item ::= single_valued_path_expression | identification_variable,

where case_expression is not supported by the groupby_item.

Thanks for reporting, such behavior will be described in the CUBA Platform documentation.

We’ve created new enhacement for it.

#5

Hi @chizhikov

Probably a hole in the spec text.

I’m no expert in SQL or JPQL but my experience with SQL is that anything that is selectable is groupable. It’s quite fundamental actually.

The spec indicates clearly on the same page that any item in group by must be in the select. Having worked on a SQL engine long time ago, from a code perspective there is no point having different evaluations for select and group by.

Which is why I think it works with EclipseLink, and I’m pretty sure it will work with any JPA impl.

Moreover in this particular case the “case when” clause evaluates to a single_valued_path_expression.

Regards
Michael

1 Like