Teodor Sigaev <teodor@sigaev.ru> writes:
> 2) ORDER BY .. [ NULLS ( FIRST | LAST ) ]
> http://www.sigaev.ru/misc/NULLS_82-0.5.gz
i haven't looked at the other patches yet, but this one is just horrid :-(
Instead of creating a proper parse-tree representation of the NULLS
FIRST/LAST option, you've kluged it to convert the syntax into a
double-column ordering using "foo IS [NOT] NULL" as the first column.
This has obvious semantic disdvantages (what if foo is an expensive
function?); but the real problem is that there's no way for the planner
to reason about ordering in this representation. This patch would
guarantee that an ORDER BY with the NULLS option couldn't use an
indexscan, even if the index sorts nulls at the correct end.
I think a reasonable implementation requires introducing an explicit
concept of nulls-first-or-last into the planner's model of sort order,
and probably into btree index opclasses as well. There is already some
discussion about this in the archives with respect to the problem of
handling descending-sort opclasses nicely. (If you just switch the
operators to make a descending-sort opclass, then nulls end up at the
"other end" from where they would otherwise, meaning that a backwards
index scan does something unexpected. We have to fix that or else
descending-sort opclasses can break mergejoins...)
regards, tom lane