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

[jakob-fiegerl]

I've got the following code which selects all distinct pets and orders them by the status (alive desc and lastModified desc)

 private final JPAQueryFactory query;

query.select(pet)
        .distinct()
        .from(pet)
        .orderBy(pet.status.when("alive").then(1).otherwise(0).desc())
        .orderBy(pet.lastModifiedDateTime.desc())
        .fetch();

which leads to:

org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

BUT without the distinct OR the orderBy(pet.status..) the query is just fine. But why??

query.select(pet)
        .from(pet)
        .orderBy(pet.status.when("alive").then(1).otherwise(0).desc())
        .orderBy(pet.lastModifiedDateTime.desc())
        .fetch();

QueryDSL Version 4.2.1

[jwgmeligmeyling]

This is simply a limitation of PostgreSQL:

select distinct * from (values ('a', true), ('b', true), ('c', false)) as pet(name, alive)
    order by case when pet.alive then 1 else 0 end asc ;

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...true), ('c', false)) as pet(name, alive) order by case when ...       

As the error mentions, it can be resolved by adding the order by expression to the select clause:

select distinct *,  case when pet.alive then 1 else 0 end  from (values ('a', true), ('b', true), ('c', false)) as pet(name, alive) order by case when pet.alive then 1 else 0 end asc ;

 name | alive | case 
------+-------+------
 c    | f     |    0
 a    | t     |    1
 b    | t     |    1
(3 rows)