Jpa Specification ORA-01791: not a SELECTed expression

Viewed 392

I have a problem with Jpa Specification.
My specification looks like this:

public static Specification<PersonView> getByFilter(PersonViewFilter filter) {
    return (root, query, criteriaBuilder) -> {

        List<Predicate> predicates = new ArrayList<>();

        SetJoin<PersonView, PersonDetailsView> personDetailsJoin =
                root.join(PersonView_.personDetails);

        Path<String> namePath = personDetailsJoin.get(PersonDetailsView_.name);
        Path<String> surnamePath = personDetailsJoin.get(PersonDetailsView_.surname);

        predicates.add(namePredicate(personDetailsJoin, criteriaBuilder, filter.getName()));
        predicates.add(surnamePredicate(personDetailsJoin, criteriaBuilder, filter.getSurname()));
        predicates.add(peselPredicate(personDetailsJoin, criteriaBuilder, filter.getPesel()));

        predicates.removeAll(Collections.singleton(EMPTY_PREDICATE));

        query.orderBy(List.of(criteriaBuilder.asc(namePath), criteriaBuilder.asc(surnamePath))).distinct(true);
        return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}

When I try search results I can see an error: ORA-01791: not a SELECTed expression

The problem is related to generated sql by JpaSpecificationExecutor. It looks like this:

select
    * 
from
    ( select distinct pv.person_id 
    from
        personview pv
    inner join
        persondetailsview pdv on pv.person_id=pdv.person_id 
    where
        1=1 
    order by
        pdv.name asc,
        pdv.surname asc ) 
where
    rownum <= ?

In Select clausule should be added name and surname then it would work, but I don't know how to do this in Specification. Without distinct query works, but i don't have duplicates. Please for help.

1 Answers

From the error ORA-01791: not a SELECTed expression, you want to add the field to the select or remove the distinct with this :

query.distinct(false);

If you want to keep the distinct, have a look there : https://stackoverflow.com/a/53549880/2641426

Related