Re: any way for ORDER BY x to imply NULLS FIRST in 8.3?

On Wed, 2007-11-07 at 08:38 -0500, Bruce Momjian wrote:
> Simon Riggs wrote:
> > If an application has already made that choice then we should allow them
> > the opportunity to work with PostgreSQL. The application may be at
> > fault, but PostgreSQL is the loser because of that decision.
> >
> > The SQL Standard says that the default for this is defined by the
> > implementation; that doesn't bar us from changing the implementation if
> > we wish. We can do that without changing PostgreSQL's historic default.
> >
> > Perhaps we can have a parameter?
> >
> > default_null_sorting = 'last' # may alternatively be set to 'first'
> >
> > (or another wording/meaning.)
> >
> > That is what I thought you'd implemented, otherwise I would have
> > suggested this myself way back. This new parameter would be a small
> > change, but will make a major difference to application portability.
> >
> > This seems like the key to unlocking your new functionality for most
> > people.
>
> You already have that control at the SQL SELECT level so you are just
> avoiding typing to add the GUC parameter.

My understanding is that both MySQL and MSSQL support NULLS FIRST by
default, so being able to accept much of their SQL without change would
be a huge win.

Editing an application, you would be required to add the words NULLS
FIRST to every single ORDER BY and every single CREATE INDEX in an
application. If we know that is what people would do, why not have one
parameter to do this for them?

Now imagine you are writing an application that has to work on multiple
databases. Can you realistically create a workable framework that has
the SQL written in multiple different ways? That issue is the big issue
preventing many off-the-shelf software vendors from supporting Postgres.

Say you did decide to edit the application. As soon as you edit the SQL
within an application it typically will violate any support contract in
place. That's usually enough to prevent even the brave from doing this.

One might argue that SQL generators such as Hibernate can automatically
and easily generate the required SQL, so they don't need this. That's
very nice to know we'll be able to use the new feature maybe 10-20% of
the time, but what about other applications?

We already have parameters of this category, for example:
default_with_oids == WITH OIDS text on CREATE TABLE
default_transaction_isolation...
default_read_only...
plus many of the other GUCs in statement behaviour section of the Server
Configuration chapter.
add mising from
transform null equals etc
http://developer.postgresql.org/pgdocs/postgres/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-CLIENTS

> I think we need more requests for such a feature before we add it.

Almost none of the features we've added have come by request. Features
get added because we see the benefit ourselves. Yes, we should discuss
this more widely; I'm confident many others will see the benefit in
allowing migration from other systems to happen more easily.

What we have now implements SQL Standard behaviour. I think that's
uninteresting for 99% of applications. I believe in standardisation, but
nobody gets excited about it. There are few applications that will
specify NULLS FIRST for a few queries only, actually coding that into
the SQL.

Implement SQLServer and MySQL behaviour? Now we're talking about
hundreds of new applications that might decide to migrate/support
PostgreSQL because of our flexibility in being able to support both
kinds of sorting.

It's going to be a short patch.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com