Re: Ordering of Numbers with NULL records

Hi Mark,

There is no bug. :-)  Query 1 does what you want and behaves correctly.
When a non-NULL number is compared with IS NULL, it returns 0; whereas
NULL IS NULL returns 1.  Since 0 obviously sorts before 1, you can see
how you get those results.  You can verify this with a simple query:

SELECT 123 IS NULL, NULL IS NULL;


Hope that helps.


Matt


----- Original Message -----
From: "Mark Hedges"
Sent: Tuesday, April 13, 2004 4:41 PM
Subject: Ordering of Numbers with NULL records


> Hi,
>
> I have a table of tinyints where these records can also be NULL.  I
want to
> return an ordered list of numbers, placing the NULL values at the end
of the
> list.  By default, MySQL doesn't do this, so I had to modify the query
used.
>
> See below:
>
> mysql> create table test (number tinyint unsigned);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> describe test;
> +--------+---------------------+------+-----+---------+-------+
> | Field  | Type                | Null | Key | Default | Extra |
> +--------+---------------------+------+-----+---------+-------+
> | number | tinyint(3) unsigned | YES  |     | NULL    |       |
> +--------+---------------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
>
> mysql> insert test set number=8;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=2;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=66;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=13;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=null;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert test set number=91;
> Query OK, 1 row affected (0.01 sec)
>
> mysql> select * from test;
> +--------+
> | number |
> +--------+
> |      8 |
> |      2 |
> |     66 |
> |     13 |
> |   NULL |
> |     91 |
> +--------+
> 6 rows in set (0.01 sec)
>
> mysql> select * from test order by number;
> +--------+
> | number |
> +--------+
> |   NULL |
> |      2 |
> |      8 |
> |     13 |
> |     66 |
> |     91 |
> +--------+
> 6 rows in set (0.00 sec)
>
>
> This order is NOT what I want because all NULL values are first in the
list,
> so I tried the other queries below:
>
>
> QUERY 1
>
> mysql> select * from test order by number is null, number;
> +--------+
> | number |
> +--------+
> |      2 |
> |      8 |
> |     13 |
> |     66 |
> |     91 |
> |   NULL |
> +--------+
> 6 rows in set (0.00 sec)
>
> QUERY 2
>
> mysql> select * from test order by number is not null, number;
> +--------+
> | number |
> +--------+
> |   NULL |
> |      2 |
> |      8 |
> |     13 |
> |     66 |
> |     91 |
> +--------+
> 6 rows in set (0.00 sec)
>
> QUERY 3
>
> mysql> select * from test order by number, number is null;
> +--------+
> | number |
> +--------+
> |   NULL |
> |      2 |
> |      8 |
> |     13 |
> |     66 |
> |     91 |
> +--------+
> 6 rows in set (0.00 sec)
>
> QUERY 4
>
> mysql> select * from test order by number, number is not null;
> +--------+
> | number |
> +--------+
> |   NULL |
> |      2 |
> |      8 |
> |     13 |
> |     66 |
> |     91 |
> +--------+
> 6 rows in set (0.00 sec)
>
>
> Query 1 appears to give the result I want (with null values being last
in
> the list and numbers being in order).  However, is this query actually
> correct? - because I would read this as:
>
> -> select all records from table test ordered first by records which
ARE
> NULL (eg, records which contain NO number) and then order by number
(with
> records which DO contain a number).  This would suggest that the
result
> should actually have NULL entires first, just like all the other
results of
> queries 2, 3 and 4 (or am I missing something?).
>
> Can someone clarify what is going on here? (I'm not sure if this is a
bug or
> not).
>
> Regards...
>
> --
> Mark


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]