issue with orderBy dynamic path generation

[David Zeigler]

Hi,

I recently upgraded to 3.3.2 from 3.2.4 and I believe we're running into an issue related to this change in 3.3.0: https://github.com/querydsl/querydsl/issues/582

I have a number of jpa queries with order by and group by clauses which share the same columns.  In 3.3.2, the order by expressions are converted to use the left join aliases, but the groupBy clause continues to use the foreign key column in the generated query. This causes a failure in Oracle "ORA-00979 not a GROUP BY expression" because the order by has columns not in the group by.

Here's a pared down example to illustrate the issue.

JPAQuery query = new JPAQuery(entityManager)

.from(user)

.groupBy(user.department().id)

.orderBy(user.department().id.asc());

return query.list(user.count(), user.department().id);

In 3.3.2, the generated query is:

select count(user0_.id) as col_0_0_, user0_.department_id as col_1_0_ 

from users user0_ 

left outer join department department1_ on user0_.department_id=department1_.id 

group by user0_.department_id 

order by department1_.id asc;

ORA-00979: not a GROUP BY expression occurs because the order by uses columns not in the group by.  If I remove the order by, the query works fine.  If I change the query to the 3.2.4 behavior, it also works:

select count(user0_.id) as col_0_0_, user0_.department_id as col_1_0_ 

from users user0_ 

group by user0_.department_id 

order by user0_.department_id asc;

Does anyone know of a way to work around this in 3.3.2 (aside from rewriting all the queries to add explicit left joins)?  Would it be possible to add an option to disable the new behavior?

Thanks,

David

[Timo Westkämper]

Hi.

Instead of an option to disable the behaviour I'd suggest to disable the rewrite of the order by expressions under certain conditions. Having the same expressions in other parts of the query could be a good start. What do you think about it?

Br,

Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[David Zeigler]

That sounds great.

Thanks,

David

On Saturday, May 3, 2014 12:46:32 PM UTC-4, Timo Westkämper wrote:

Hi.

Instead of an option to disable the behaviour I'd suggest to disable the rewrite of the order by expressions under certain conditions. Having the same expressions in other parts of the query could be a good start. What do you think about it?

Br,

Timo

[Timo Westkämper]

David, could you create a ticket for it? Thanks, Timo

[David Zeigler]

I opened https://github.com/querydsl/querydsl/issues/732

Thanks,

David

On Saturday, May 3, 2014 1:37:23 PM UTC-4, Timo Westkämper wrote:

David, could you create a ticket for it? Thanks, Timo