On P, 2005-09-18 at 18:04 -0400, Tom Lane wrote: > Another thing that's flaky in the current treatment is > the question of whether NULLs sort before or after ordinary values. > We've essentially tried to force NULLs to sort "high" (as if they > compare greater than all ordinary values), so that ASC and DESC > orderings can be obtained from forward and backwards scans of an > ordinary btree index. This is going to break entirely in the > presence of reverse-sort opclasses --- given the current btree code, > such an opclass would cause NULLs to appear to sort "low". I suspect > we have to bring out the NULL sort behavior as an explicit property > of opclasses, but I'm not sure just how to do that. A related point > is that we not infrequently get requests for a way to make ORDER BY > sort nulls low; it'd be nice if we could actually support that, > rather than going in the direction of making sure it can't happen.
I think that placement of NULL's should be a property of ORDER BY and separated from opclass. From: http://opensource2.atlassian.com/projects/hibernate/browse/HHH-465 ------------------------------------------------------------------------ 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= -- Hannu Krosing <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
