Criteria Builder Limit/Offset odd behavior oracle

Question

i'm getting an odd behavior from one the many criteria bulders i have in a project.

I have 1 table and so 1 single entity. The entity is declared like this

@Entity
@Table(name = "CITIZEN")
public class CitizenEntity {
  private String uuid;
  private String name;
  private String surname;
  private String socialStatus;
  .....

  @Id
  @Column(name = "UUID", nullable = false, length = 255)
  public String getUuid() {
    return uuid;
  }

  public void setUuid(String uuid) {
    this.uuid = uuid;
  }

  @Basic
  @Column(name = "NAME", nullable = true, length = 255)
  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  @Basic
  @Column(name = "SURNAME", nullable = true, length = 255)
  public String getSurname() {
    return surname;
  }

  public void setSurname(String surname) {
    this.surname = surname;
  }
  @Basic
  @Column(name = "SOCIAL_STATUS", nullable = true, length = 255)
  public String getSocialStatus() {
    return socialStatus;
  }

  public void setSocialStatus(String socialStatus) {
    this.socialStatus = socialStatus;
  }
  .....
}

i have a couple of method created like this

private CriteriaQuery<CitizenEntity> createCriteriaQuery(SearchCriteria criteria) {
    final CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

    final CriteriaQuery<CitizenEntity> criteriaQuery = criteriaBuilder.createQuery(CitizenEntity.class);
    final Root<CitizenEntity> itemRoot = criteriaQuery.from(CitizenEntity.class);
    if (criteria.getCondition() != null) {
        criteriaQuery.where(resolve(criteriaBuilder, criteria.getCondition(), itemRoot));
    }

    final List<Order> orderList = createOrder(criteriaBuilder, criteria.getSorter(), itemRoot);
    if (orderList.isEmpty()) {
        return criteriaQuery;
    }

    return criteriaQuery.orderBy(orderList);
}

public Iterable<Citizen> loadCitizens(SearchCriteria criteria) {
    TypedQuery<CitizenEntity> query = em.createQuery(createCriteriaQuery(criteria));
    if (criteria.getOffset() != null) {
        query = query.setFirstResult(criteria.getOffset().intValue());
    }
    if (criteria.getNumElement() != null) {
        query = query.setMaxResults(criteria.getNumElement().intValue());
    }
    em.clear();
    final List<CitizenEntity> items = query.getResultList();
    return items.stream().map(converter::createCitizen).sequential().collect(Collectors.toList());
}

The class SearchCriteria contains only indications on how to compose the query and createCitizen method only convert each CitizenEntity in a Citizen class with his rules

There are over 1Million records in this table so i need to paginate it. the code already use a limit of 200, by default and the offset vary depending on which page i am on.

order may be done ascending or descending for any column in the CITIZZEN table that are many more then name, surname and UUID

PS: page size is 50 but the query still retrieve 200 record each time for some unknown reason (i mean the setMaxResult ALWAYS get the default value 200. I can set it to the real page size but it is hardly going to solve the problem because this routine is called 4 times in 4 different databases and the result is aggregated by the service)

The table present also a column with an enum (for example social_status with possible values: "single", "married" and a couple more. this is just an example ).

The user wants to order the query by socialStatus desc.

By my experience the above code should work great but ...

I have 2 problems

1. after few pages the result of the query is alwas the same

2. if i do the query on oracle myself, page 1 give me the same result i see on FE, while page 2 gives me a different result from FE.

We made different ipotesys.

1. It can be the setMaxResult that make the query retrieve 200 result and the next time we use the offset those results are gone (cache) so the query looks inconsistent.
2. there is a FE error (checked and it is not)

and some other stuff ...

i recently discovered that setFirstResult + setMaxResult can retrieve some duplicated rows but it should happen only for entities with relationships and we have no relationship on that entity.

Bottom Line, i need help and any will be appreciated.

PPS: Fun fact, the query with ASCENDING ORDER and NO ORDER works perfectly!