Hi!
On Feb 07, Paul DuBois wrote:
> At 16:56 +0200 2/6/03, monty@stripped wrote:
> >
> >ChangeSet
> > 1.1560 03/02/06 16:55:59 monty@stripped +19 -0
> > Added START TRANSACTION syntax
> > Added ALL as parameter option for all group functions.
> > Make join handling uniform. This allows us to use ',', JOIN and
> >INNER JOIN the same way.
> > Sort NULL last if DESC is used (ANSI SQL 99 requirement)
>
> Okay, way back, NULL sorted first by default and last if DESC was given.
>
> Then in 4.0.2, it was changed so that NULL sorted first whether or not
> DESC was given:
>
> >@node News-4.0.2, News-4.0.1, News-4.0.3, News-4.0.x
> >@appendixsubsec Changes in release 4.0.2 (01 Jul 2002)
> >
> >@item
> >Use @code{ORDER BY column DESC} now sorts @code{NULL} values first.
> >(In other words, @code{NULL} values sort first in all cases, whether or
> >not @code{DESC} is specified.)
>
>
> I thought that change was made for ANSI-behavior reasons?
>
> Now, NULL is once again supposed to sort last with DESC ... for ANSI
> behavior?
>
> I don't get it. What's going on? These flip-flops in behavior
> are confusing.
It was a customer that complained, which resulted in a discussion,
Monty fixed it back after
=====
Date: Wed, 5 Feb 2003 08:44:00 -0700
From: Peter Gulutzan <peterg@stripped>
To: "Carsten H. Pedersen" <carsten@stripped>
Cc: dev-private@stripped
Subject: Re: Non ANSI defined sorting of NULLS
Carsten,
This is what some other DBMSs do (a chart from SQL Performance Tuning):
ANSI/DBMS ORDER BY Support
NULLs Sort
ANSI SQL Low Or High
DB2 High
Informix Low
Ingres High
InterBase At End
SQL Server Low
Oracle High
Sybase Low
.. Where "Low" means "NULLs are considered less than all other values" and
"High" means "NULLs are considered greater than all other values", and "At
End" means "NULLs come at the end of a sorted list even if you use ORDER
BY ... DESC". The ANSI definition of ORDER BY does indicate (at least
as I understand it) that the customer is correct. In SQL:2003 there is a
new clause "ORDER BY ... [NULLS FIRST | NULLS LAST]" but at the
moment the specification is buggy, it's hard to say what it refers to.
There are workarounds. For example, if the column is CHAR, one can
use ORDER BY CASE WHEN column1 IS NULL THEN 'ZZZZZZ' ELSE
column1.
Peter
For technical support contracts, visit https://order.mysql.com/?ref=mpgu
__ ___ ___ ____ __
/ | / /_ __/ __/ __ \/ / Mr. Peter Gulutzan <peterg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Software Architect
/_/ /_/\_, /___/\___\_\___/ Edmonton, Canada
<___/ www.mysql.com Office: ### ### ####
=====
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
