List:Internals« Previous MessageNext Message »
From:Paul DuBois Date:February 10 2003 10:31pm
Subject:Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]
View as plain text  
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
>        <___/

Thread
bk commit into 4.0 tree (1.1560)monty6 Feb
  • Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Paul DuBois7 Feb
    • Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Sergei Golubchik10 Feb
      • Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Paul DuBois10 Feb
        • Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Sergei Golubchik11 Feb
          • Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Michael Widenius16 Feb
            • Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Paul DuBois16 Feb
Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Peter Gulutzan11 Feb
Re: bk commit into 4.0 tree (1.1560)[ARRRRRRGGGGGGHHHHHHH!!!!!]Peter Gulutzan11 Feb
Re: bk commit into 4.0 tree (1.1560) [ARRRRRRGGGGGGHHHHHHH!!!!!]Peter Gulutzan17 Feb