Querydsl: Wrong orderBy dynamic path generation for SELECT DISTINCT JPA-Queries

[alexeev]

The orderBy dynamic path generation was introduced in #582 and it works as expected with regular JPA Queries, e.g

query.from(player).orderBy(player.team.name.asc()).list(player);

generates proper JPQL

select player from Player player 
    left join player.team as player_team 
    order by player_team.name asc

and the JPA-provider (here: Hibernate v4.2.14.Final) generates proper SQL

select player0_.id as id1_0_, player0_.age as age2_0_, player0_.team_id as team_id3_0_ 
    from Player player0_ 
    left outer join Team team1_ on player0_.team_id=team1_.id 
    order by team1_.name ASC  

However, one shouldn't transfer the same logic one-to-one to SELECT-DISTINCT queries.
In the following example:

query.from(player).distinct().orderBy(player.team.name.asc()).list(player);

it is wrong to generate JPQL

select distinct player from Player player 
    left join player.team as player_team 
    order by player_team.name asc

because the corresponding SQL statement is not correct:

select distinct player0_.id as id1_0_, player0_.age as age2_0_, player0_.team_id as team_id3_0_ 
    from Player player0_ 
    left outer join Team team1_ on player0_.team_id=team1_.id 
    order by team1_.name ASC  

PostgreSQL v9.3.5 reports

for SELECT DISTINCT, ORDER BY expressions must appear in select list

H2 v1.3.176 reports

org.h2.jdbc.JdbcSQLException: Order by expression "TEAM1_.NAME" must be in the result list in this case;

Not yet tested with other DBs but according to official docs, same behaviour can be expected

• Apache Derby
• MS SQL Server
• Oracle

---

affected versions: 3.5.1 and 3.6.0

[LSDancer]

+1
having problem with it too. our architect is constantly nagging cause we need too many workarounds because of this

[wernert75]

I have the same problem.

[timowest]

I understand the issue, but how can this be better handled in Querydsl?

Based on

query.from(player).orderBy(player.team.name.asc()).list(player);

we don't really know how the select part looks like since eager loading of the team relation would extend the select part.

[liranyogev]

Any workaround for this issue?

[alexeev]

@mooshben The ugly but trivial workaround is:

1) Add all orderBy statements explicitly to the list of projections

// original
List<Player> players = query.from(player).distinct().orderBy(player.team.name.asc()).list(player);
// workaround
List<Tuple> tuples = query.from(player).distinct().orderBy(player.team.name.asc()).list(player, player.team.name);

2) You would then have to deal with tuples and unwrap the player-objects

for (Tuple tuple : tuples) {
    player = (Player)tuple.get(0);
}

@timowest: wouldn't it be possible to automatize those two steps in a transparent way, in case if Query-Mixin is maked as distinct=true?

[alexey-anufriev]

+1

[dmaidaniuk]

I experienced same issue. Can be applied some automation on QueryDSL side here as propose @alexeev?

[hamavaro]

How SELECT DISTINCT ON specific field can be done in querydsl 4.0.2 ?
Thanks.

[orange-buffalo]

+1

[johnjaylward]

I am seeing this as well with QueryDSL 4.1.2 and MySql 5.7.12 and sql_mode set to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The relevant sql_mode is ONLY_FULL_GROUP_BY. When that is disabled, it works fine because mysql is lax by default.

[alexeev]

Any chance to fix it? For example by adopting the solution from the comment above?
https://github.com/querydsl/querydsl/issues/1150#issuecomment-141094163

[jwgmeligmeyling]

This is just a limitation of the database dialect. If at all worked around, it should be in the ORM layer.

Just make sure the query projects the team as well, if you want to order by its properties.

query.select(player, player.team).distinct().from(player).orderBy(player.team.name.asc()).fetch();