Issue Details (XML | Word | Printable)

Key: HHH-465
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Anthony Patricio
Votes: 32
Watchers: 21
Operations

If you were logged in you would be able to see more operations.
Hibernate Core

order by nulls first / last

Created: 10/May/05 03:39 PM   Updated: 24/Apr/09 09:25 PM
Component/s: core
Affects Version/s: 3.0.3
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. Java Source File CustomNullsFirstInterceptor.java (2 kB)



 Description  « Hide
support of nulls first / last in order clause

"NULLS LAST" is part of the SQL 99 standard.

The syntax is as follows:

ORDER BY [COLUMN NAME] [ASC | DESC] [NULLS FIRST | NULLS LAST]

In different DBs, the sorting of nulls relative to other values is handled differently.

PostgreSQL - Nulls are considered HIGHER than non-nulls.
DB2 - Higher
MSSQL - Lower
MySQL - Lower
Oracle - Higher

The following DBs have supported this functionality:
DB2 V7
Oracle 9i

PostgreSQL, MySQL, SQLServer do not appear to support this from what I can gather.

see http://forum.hibernate.org/viewtopic.php?t=942176&start=0&postdays=0&postorder=asc&highlight=

 All   Comments   Work Log   Change History   FishEye      Sort Order: Ascending order - Click to sort in descending order
Daniel Or added a comment - 08/Dec/05 06:42 AM
This is also a backward compatibility issue.
In Hibernate 2 'nulls first' segment was copied as is to the generated SQL. In Hibernate 3 it is ommitted.

Emmanuel Bernard added a comment - 08/Dec/05 05:08 PM
not really a backward compatibility, more a bug based misuse

Curtis Light added a comment - 30/Jan/06 03:59 PM
Would two additional overloaded methods such as the following be appropriate? The developer could use the database default ordering by using the existing API or attempt to specify the preference if the underlying database supports the clause. Would Hibernate's dialect functionality could help with the platform-related issues?

In org.hibernate.criterion.Order:

public static Order asc(String propertyName, NullPrecedence nullPrecedence)

public static Order desc(String propertyName, NullPrecedence nullPrecedence)

And add a NullPrecedence enumeration with NULLS_FIRST and NULLS_LAST constants.

Joris Verschoor added a comment - 06/Feb/06 05:55 AM
For databases that don't support it, you can do: order by -colum DESC when you mean order by column ASC
This will reverse the order.

Guilherme Chapiewski added a comment - 09/Feb/06 12:35 PM
Do you have any idea about when this issue is going to be implemented?

Christian Bauer added a comment - 09/Feb/06 01:25 PM
As soon as you provide a patch.

sridhar paladugu added a comment - 08/Sep/06 10:56 AM
We are working on a large project using Hibernate, and really would like to see this issue fixed.

I do not have problem in using some oracle native functions in order by clause in HQL except "NULLS LAST".

Hibernate while generating oracle sql ignoring th key word (NULLS LAST) and generating the SQL without the keyword present in order by clause.

Following is a sample query i am using

HQL: " select me.name from MyEntity me order by me.name NULLS LAST"

generated SQL: select TBL.NAME FROM MyTable TBL ORDER BY TBL.NAME.

Thanks
Sridhar


Emmanuel Bernard added a comment - 08/Sep/06 11:07 AM
"We are working on a large project using Hibernate, and really would like to see this issue fixed."

So either provide a patch or consider commercial support

Geoffrey De Smet added a comment - 28/Aug/07 10:02 AM
Oracle dialect:
- "property asc nulls first"
- "property desc nulls first"
- "property asc nulls last"
- "property desc nulls last"
MySQL dialect: use the minus sign trick
- "property asc"
- "-property asc" = desc nulls first
- "-property desc" = asc nulls last
- "property desc"

hamster added a comment - 03/Jun/08 04:37 PM
Just FYI: PostgreSQL supports nulls last/first starting from 8.3 version.
http://postgresql.com.cn/docs/8.3/static/queries-order.html

(compare with http://postgresql.com.cn/docs/8.2/static/queries-order.html)

Cheers,

Emilio Dolce added a comment - 02/Jul/08 12:01 PM
In the meantime fell free to use this interceptor.

Brooks Lyrette added a comment - 26/Aug/08 01:41 PM
Thanks for the Interceptor Emilio but you missed two important cases.

You have to watch our for 'limit' statements if paging is used and also for functions upper(column_name)

Peter Wyczesany added a comment - 18/Nov/08 03:44 PM
Hi,

I'm using PostreSQL 8.3, so it supports nulls last/first
I have mapping via annotations:

  @OneToMany(mappedBy="section", fetch=FetchType.LAZY)
  @OrderBy(clause="grade DESC, name ASC NULLS LAST, username ASC")
  public List<User> getUsers() {
    return users;
  }

And my ORDER BY clause for fetching this List looks like this:

  order by users0_.grade DESC, users0_.name ASC users0_.NULLS users0_.LAST, users0_.username ASC

Is there any possibility to fix this clause?
And when nulls last/first feature will be supported

Steve Ebersole added a comment - 24/Apr/09 09:25 PM
In the interest of completeness, it is actually the SQL-2003 spec which introduces NULLS FIRST | NULLS LAST.

Also, it does not define the "sort key" as simply as [COLUMN NAME]. In SQL-92 it was the case that a sort-key could only be (a) column name or (b) integer literal (indicating the select-clause position). For SQL-2003 (and SQL-99), the full definition of sort key is:
<sort key> ::= <value expression>
essentially anything that resolves to a value (see http://savage.net.au/SQL/sql-2003-2.bnf.html#sort%20key for details)

The difficulty here is if we are going to support SQL-2003 then we need to support its full breadth. Which implies an audit to see what databases fully comply with the various SQL-2003 defined compatibility levels.