JPQL Hibernate NULLS LAST being ignored

Question

UPDATED - see answer in comment

I'm aware that CriteriaQuery.orderBy does not support NULLS LAST. I was trying to use a TypedQuery and noticed that it seems to just ignore everything after "NULLS LAST" - doesn't throw any errors, just ignores it:

    String sql = "SELECT c FROM Contact c WHERE c.partnerCode =:pCode and c.activeFlag='Y'" +
            " ORDER BY c.primaryFlag DESC NULLS LAST, c.lastName ASC";

    TypedQuery<Contact> query = em.createQuery(sql, Contact.class);
    query.setParameter("pCode", partnerCode);       

    return query.getResultList();

This returns my result ordered by primary flag descendings, nulls first, ignore sorting on last name.

If I do this:

      String sql = "SELECT c FROM Contact c WHERE c.partnerCode =:pCode and c.activeFlag='Y'" +
            " ORDER BY c.primaryFlag DESC, c.lastName ASC";

I get both primary and lastname sort, but still end up with nulls first because it's an Oracle DB.

I was mostly surprised that no error message was thrown when I added NULLS LAST and I was hoping that with some syntax tweaks I would be able to get it to accept the NULLS LAST request.

Answer 1

A co-worker provided a workaround for me. Still don't know why the query ignores NULLS LAST but I ended up using this as my workaround:

 String sql = "SELECT c FROM Contact c WHERE c.partnerCode =:pCode and c.activeFlag='Y'"              
 +" ORDER BY nvl(c.primaryFlag, 'N') DESC, c.lastName ASC"; 

Note: values for the column were 'Y', 'N' or null. Using nvl I am using 'N' in place of null.