At 9:40 +0100 2/10/03, Sergei Golubchik wrote:
>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
Sergei,
Thanks, although I'm still wondering about why the NULL-always-sorts-first
change was made for 4.0.2. Wasn't that done for ANSI compliance? Did
the ANSI spec change?
>=====
>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
> <___/
