From: Paul DuBois Date: February 10 2003 10:31pm Subject: Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!] List-Archive: http://lists.mysql.com/internals/6913 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" ; format="flowed" 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 >To: "Carsten H. Pedersen" >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 > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Software Architect >/_/ /_/\_, /___/\___\_\___/ Edmonton, Canada > <___/ www.mysql.com Office: ### ### #### > >===== >Regards, >Sergei > >-- >MySQL Development Team > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Sergei Golubchik > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ >/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany > <___/