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.

not really a backward compatibility, more a bug based misuse

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.

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.

Do you have any idea about when this issue is going to be implemented?

As soon as you provide a patch.

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

"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

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"

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,

In the meantime fell free to use this interceptor.

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)

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

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.

It was suggested that a better work around for MySQL (which still does not have NULLS FIRST/LAST support btw) would be to do somethign like this:

• for NULLS FIRST : order by case when property is null then 0 else 1 end, property
• for NULLS LAST : order by case when property is null then 1 else 0 end, property

(the original suggestion was to use the MySQL specific function IF instead of the CASE...)

Couldn't this be another dialect method like isNullOrderingSupported()?

Fixed this issue.
All nulls are moved in last while sort by desc.

please find the fixed version here http://subversion.ny.jpmorgan.com/svn/repos/CMDB/CMDB/hibernate/3.2.3