History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: HHH-465
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Anthony Patricio
Votes: 28
Watchers: 17
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: 26/Aug/08 01:41 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 - 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 - 08/Dec/05 05:08 PM
not really a backward compatibility, more a bug based misuse

Curtis Light - 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 - 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 - 09/Feb/06 12:35 PM
Do you have any idea about when this issue is going to be implemented?

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

sridhar paladugu - 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 - 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 - 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 - 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 - 02/Jul/08 12:01 PM
In the meantime fell free to use this interceptor.

Brooks Lyrette - 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)