Observation: DB2 and informix sort nulls differently 
Author Message
 Observation: DB2 and informix sort nulls differently
DB2 7.2 UDB WE   Linux
Informix 9.21.HC5   HP-UX

This is interesting.  I issue an SQL SELECT stmt against a table, and
ORDER BY a column which allows nulls.

In DB2, the rows with nulls sort to the bottom.
In Informix, the rows with nulls sort to the top.

Why?

aj



Wed, 13 Jul 2005 04:39:33 GMT
 Observation: DB2 and informix sort nulls differently

That's the difference for IBM SQL and INFORMIX SQL.
Some dbms vendor designed the NULL values as the highest, others think it
as the lowest on the ordering.
Regards,
Quote:

> DB2 7.2 UDB WE   Linux
> Informix 9.21.HC5   HP-UX

> This is interesting.  I issue an SQL SELECT stmt against a table, and
> ORDER BY a column which allows nulls.

> In DB2, the rows with nulls sort to the bottom.
> In Informix, the rows with nulls sort to the top.

> Why?

> aj



Wed, 13 Jul 2005 05:06:17 GMT
 Observation: DB2 and informix sort nulls differently
Well --- They've got to go somewhere..... ;-)

I'm not sure if there is any ANSI defined location as to where the null
columns should be sorted to.  I suspect that the order placement might have
something to do with history more than anything else.

Is this really an issue?  If it is, we are currently working on getting the
9.5 features nailed down for IDS.  I guess we could have a "NULL_PLACEMENT
<first/last>" parameter if this should be important.   I doubt that we would
want to change the default behavior of the engine as that would risk an
impact on customer applications.

By the way --- You might notice a slightly different sort order on the
EBCIDIC DB2 environments and the UNIX DB2 environments as well.  ;-)

M.Pruet

Quote:

> DB2 7.2 UDB WE   Linux
> Informix 9.21.HC5   HP-UX

> This is interesting.  I issue an SQL SELECT stmt against a table, and
> ORDER BY a column which allows nulls.

> In DB2, the rows with nulls sort to the bottom.
> In Informix, the rows with nulls sort to the top.

> Why?

> aj



Wed, 13 Jul 2005 05:08:09 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> Well --- They've got to go somewhere..... ;-)

> I'm not sure if there is any ANSI defined location as to where the null
> columns should be sorted to.  I suspect that the order placement might have
> something to do with history more than anything else.

The standard says that it is implementation defined whether nulls sort
before or after all valid values, but those are the only valid
options.  Here you have a classic example of two independently
developed products that both conform to the standard but produce
different answers.

Quote:
>[...]

>>DB2 7.2 UDB WE   Linux
>>Informix 9.21.HC5   HP-UX

>>This is interesting.  I issue an SQL SELECT stmt against a table, and
>>ORDER BY a column which allows nulls.

>>In DB2, the rows with nulls sort to the bottom.
>>In Informix, the rows with nulls sort to the top.

>>Why?

--
Jonathan Leffler                   #include <disclaimer.h>

Guardian of DBD::Informix 1.04.PC1 -- http://dbi.perl.org/


Wed, 13 Jul 2005 13:16:28 GMT
 Observation: DB2 and informix sort nulls differently
Quote:

> The standard says that it is implementation defined whether nulls sort
> before or after all valid values, but those are the only valid
> options.  Here you have a classic example of two independently
> developed products that both conform to the standard but produce
> different answers.

There has always been variation betweeen DBMSs which sort NULLs
high, low, always high (regardless of ASC/DESC), and always low.
The DB2/Informix distinction is not news (it's in one of the
charts in our book, for example). Currently the ANSI/ISO SQL:2003
BNF for sort specifications looks like this:

<sort specification list> ::= <sort specification> [ { <comma> <sort
specification> }... ]

<sort specification> ::= <sort key> [ <ordering specification> ] [
<null ordering> ]

<sort key> ::= <value expression>

<ordering specification> ::= ASC | DESC

<null ordering> ::= NULLS FIRST | NULLS LAST

It's true that the null ordering clause isn't intended
for general use in SELECT ... ORDER BY. I only quote the
SQL:2003 draft standard to show that people on the committee
have been thinking about the issue.

Peter Gulutzan
Co-Author of SQL Performance Tuning (http://www.ocelot.ca/tuning.htm)



Thu, 14 Jul 2005 00:37:27 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> Is this really an issue?  If it is, we are currently working on
> getting the 9.5 features nailed down for IDS.  I guess we could have
> a "NULL_PLACEMENT <first/last>" parameter if this should be
> important.   I doubt that we would want to change the default
> behavior of the engine as that would risk an impact on customer
> applications.

Only slightly ;-)

--
There's nowt wrong wi' owt what mitherin' clutterbucks don't barly grummit!
-
Replies directly to this message will go to an account that may not be
checked for a week or two. For more timely e-mail response, use (only
in an emergency) ahamm sanderson net au with all the usual punctuation.



Thu, 14 Jul 2005 07:56:37 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> DB2 7.2 UDB WE   Linux
> Informix 9.21.HC5   HP-UX

> This is interesting.  I issue an SQL SELECT stmt against a table, and
> ORDER BY a column which allows nulls.

> In DB2, the rows with nulls sort to the bottom.
> In Informix, the rows with nulls sort to the top.

If DB2 supports the NVL(expr, expr) function (hell, I dunno, but Informix
and Orrible does) then you could select NVL(mycol, REALLY_LOW_VALUE) and
slip that into the sort. This way you are nailing down your chosen
positioning of NULL values. Of course, you would still select the column
itself, un{*filter*}erated, because you want it's value...

--
There's nowt wrong wi' owt what mitherin' clutterbucks don't barly grummit!
-
Replies directly to this message will go to an account that may not be
checked for a week or two. For more timely e-mail response, use (only
in an emergency) ahamm sanderson net au with all the usual punctuation.



Thu, 14 Jul 2005 07:59:19 GMT
 Observation: DB2 and informix sort nulls differently

Quote:


> > This is interesting.  I issue an SQL SELECT stmt against a table, and
> > ORDER BY a column which allows nulls.

> > In DB2, the rows with nulls sort to the bottom.
> > In Informix, the rows with nulls sort to the top.

> If DB2 supports the NVL(expr, expr) function (hell, I dunno, but Informix
> and Orrible does) then you could select NVL(mycol, REALLY_LOW_VALUE) and
> slip that into the sort. This way you are nailing down your chosen
> positioning of NULL values. Of course, you would still select the column
> itself, un{*filter*}erated, because you want it's value...

Null is an "out of band" value.  Where would you expect it be in the
sorted output?  Neither first nor last is wrong, IMO.  Portable code
written that expects only one or the other seems kind of misguided to
me.

--
Forte International, P.O. Box 1412, Ridgecrest, CA  93556-1412

President, CEO                             Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084  4A88 8851 E6C8 69E3 B00B



Fri, 15 Jul 2005 05:16:40 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> Null is an "out of band" value.  Where would you expect it be in the
> sorted output?  Neither first nor last is wrong, IMO.  Portable code
> written that expects only one or the other seems kind of misguided to
> me.

I wouldn't expect it to be sorted anywhere unless I thought about it.
Informix sorts it first (as mentioned by OP) most likely because null in
Informix is represented by negative MAXNUM for binary numerics or null bytes
for char and others. I know that some software exists which relies on that
sequencing, and I have vague, genetic memories of wishing occasionally that
NULL sorted last in Informix for the convenience of the algorithm at hand.

So from that, I'd say that there is great merit in being able to nominate
the sorting position of NULL to either the front or the end. Why would you
pick one or the other? Well, I wouldn't necessarily, but the widget
production line manager might have a strong opinion which therefore becomes
immutable truth. If we are good analysts, we might even know the proper
sequencing before (s)he does.

Now you mention portable code. Portable code is as easy as the language on
offer. Consider: if engines all offered EXACTLY the same SQL with no
exclusions and no value-added bullshit, portable would be a given.
Weaknesses in the specification such as leaving ANYTHING including NULL
sorting order as implementation dependent forces work upon us when we
suddenly jump on the portable bandwagon.

How are we expected to know these portability problems before we even meet
another engine? You'd have to be fairly astute to notice that NULL ordering
might be a portability problem when you are under pressure to complete a
large project with a specific engine, along with dozens of other portability
issues which we can only guess at. So now along comes a porting project;
things crop up. Techniques for getting around portability problems are
required. Using NVL is one possible solution to the NULL sorting problem. So
where's the room to complain about that suggestion in the face of so much
neglect from the definers of SQL and builders of engines?

Looking at portability through the retrospectoscope makes it easy to
complain that established programming practices are lacking. Ahhh well, for
the rest of us short-sighted people, we'll continue to make mistakes until
the designers of our tools and machinery start to work together to provide
uniformity instead of trying to differentiate themselves with shiny baubles
and flashing lights.

PS - SQL 2003 is said by another respondant to include a NULL positioning
clause. I hope it is clearly defined in relation to DESC sorts too,
otherwise there is still a major hole in the new spec which is almost as bad
as the old hole. OTC and Impy, bite your tongues...
--
Tommy, for a raving lunatic, you're pretty naive!
-
Replies directly to this message will go to an account that may not be
checked for a week or two. For more timely e-mail response, use (only
in an emergency) ahamm sanderson net au with all the usual punctuation.



Fri, 15 Jul 2005 08:03:46 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> How are we expected to know these portability problems before we
> even meet another engine?

By understanding the SQL standard, and recognizing that null is still
an "out of band" value whose actual value is implementation defined.
If it were a requirement that I retrieve nulls first or last, then I
would use two queries: one for the null values (no need to sort them)
and another for the non-null values, and order the queries
appropriately.

Quote:
> You'd have to be fairly astute to notice that NULL ordering might be
> a portability problem when you are under pressure to complete a
> large project with a specific engine, along with dozens of other
> portability issues which we can only guess at.

Thank you!  Maybe I should raise my rates, then.  ;)

--
Forte International, P.O. Box 1412, Ridgecrest, CA  93556-1412

President, CEO                             Fax: (760) 499-9152
My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084  4A88 8851 E6C8 69E3 B00B



Fri, 15 Jul 2005 09:59:13 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> I hope it is clearly defined in relation to DESC sorts too,
> otherwise there is still a major hole in the new spec which is almost as
> bad as the old hole. OTC and Impy, bite your tongues...

I'd certainly rather bite it than stick it in there!


Fri, 15 Jul 2005 15:40:32 GMT
 Observation: DB2 and informix sort nulls differently
Andrew Hamm wrote on Sunday 26 January 2003 00:59:

Quote:

>> DB2 7.2 UDB WE   Linux
>> Informix 9.21.HC5   HP-UX

>> This is interesting.  I issue an SQL SELECT stmt against a table, and
>> ORDER BY a column which allows nulls.

>> In DB2, the rows with nulls sort to the bottom.
>> In Informix, the rows with nulls sort to the top.

> If DB2 supports the NVL(expr, expr) function (hell, I dunno, but Informix
> and Orrible does) then you could select NVL(mycol, REALLY_LOW_VALUE) and
> slip that into the sort.

This function is callod COALESCE() in DB2 and the SQL standard.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Fri, 15 Jul 2005 18:06:45 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> DB2 7.2 UDB WE   Linux
> Informix 9.21.HC5   HP-UX

> This is interesting.  I issue an SQL SELECT stmt against a table, and
> ORDER BY a column which allows nulls.

> In DB2, the rows with nulls sort to the bottom.
> In Informix, the rows with nulls sort to the top.

This is one work around in DB2 V8.1

select i from t
order by row_number() over(order by i nulls first)

Regards
Paul Vernon
Business Intelligence, IBM Global Services



Fri, 15 Jul 2005 20:21:35 GMT
 Observation: DB2 and informix sort nulls differently

Quote:

> PS - SQL 2003 is said by another respondant to include a NULL positioning
> clause. I hope it is clearly defined in relation to DESC sorts too,
> otherwise there is still a major hole in the new spec which is almost as bad
> as the old hole. OTC and Impy, bite your tongues...

From the most recent SQL-2003 draft (Dec 2002):

<sort specification> ::= <sort key> [ <ordering specification> ] [
<null
     ordering> ]

<sort key> ::= <value expression>

<ordering specification> ::=
    ASC
  | DESC

<null ordering> ::=
    NULLS FIRST
  | NULLS LAST

Note that this is not Core SQL 2003, the <null ordering> clause
belongs to the extension "Feature T611, Elementary OLAP operations".

Check out the SQL-2003 validator:
http://developer.mimer.com/validator/parser200x/

Regards,
Jarl



Sun, 17 Jul 2005 19:09:56 GMT
 
 [ 14 post ] 

 Relevant Pages 

1. Observation: DB2 and informix sort nulls differently

2. Why are ADOX collections sorting differently in .net?

3. Nulls handled differently if using ODBC...

4. Stored Procedure handles LEFT OUTER JOIN nulls differently than the query

5. MS SQL vs Informix Online Workgroup (7.12) on NT observations

6. Sorting nulls

7. Null sort order


 
Powered by phpBB® Forum Software