Allow specification for null handling in order-by expressions (JPQL and Criteria API)

[lukasj]

§4.9 of the specification explictly states:

SQL rules for the ordering of null values apply: that is, all null values must appear before all non-null values in the ordering or all null values must appear after all non-null values in the ordering, but it is not specified which.

However, pretty much all of the important JPA providers support a nulls first/nulls last clause. So while it is already possible to define the strategy, it would be cool if one could reliably use it on top of JPA.

[lukasj]

@glassfishrobot Commented
Reported by oliver.gierke

[lukasj]

@glassfishrobot Commented
c.beikov said:
Also not that this is important for database exchangeability. If I didn't specify nulls first or nulls last every query that uses the order by is non-portable between databases since databases have different defaults.

[lukasj]

@glassfishrobot Commented
This issue was imported from java.net JIRA JPA_SPEC-76

[lukasj]

@andyjefferson Commented
Here you go, just specify this BNF,

orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::={ state_field_path_expression | general_identification_variable | result_variable } [ASC | DESC] [NULLS FIRST | NULLS LAST]

"The keywords "NULLS FIRST" or "NULLS LAST" defines where nulls are placed in the ordering of that item. No specification of nulls means that the ordering when the item is null is undefined."

Then you'll have what any sane JPA provider would have implemented years ago.
Can't be that controversial can it?

[lukasj]

• Issue Imported From: https://github.com/javaee/jpa-spec/issues/76
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: Unassigned

[odrotbohm]

Is there a chance, this get's prioritized for JakartaEE 10? Users of Spring Data JPA and downstream projects have been repeatedly and patiently asked for this for years, and it's a severe limitation compared to accessing other store types.

[beikov]

AFAIU the community can just take over here and provide a specification, API and TCK update. We are willing to implement the JPA Criteria changes for Hibernate to serve as compatible implementation.

[beikov]

If anyone is willing to work on this, Hibernate can serve as a compatible implementation.

[opengeekv2]

How hard would it be to take this on as a new (and ocasional) contributor?

[gavinking]

I have proposed a solution in #440.