CODEWITHSUNDEEP
sqlsql-server
billmccord
billmccord

Reputation: 98

SQL Server Order By isnull unexpected results

I'm trying to port some SQL to code and I've noticed that SQL Server's ORDER BY (and partition ORDER BY) seem to behave somewhat non-deterministically in some cases, but perhaps I'm just not seeing the pattern. I'm using Azure SQL Database which should be more or less SQL Server 2017 with some limitations.

Here are some examples:

select * from (
    values
        ('2014-01-01', NULL),
        ('2014-01-02', NULL),
        (NULL, '2014-01-01 00:01:00'),
        (NULL, '2014-01-01 23:59:59')
) AS temp (t, u)
order by isnull(t, u);

Produces the following, unexpected, result set where the datetimes are before the date on the same day AND the later datetime is before the earlier one:

t           u
NULL        2014-01-01 23:59:59
NULL        2014-01-01 00:01:00
2014-01-01  NULL
2014-01-02  NULL

However, if I add a few extra rows:

select * from (
    values
        ('2014-01-01', NULL),
        ('2014-01-02', NULL),
        (NULL, '2014-01-01 00:01:00'),
        (NULL, '2014-01-01 23:59:59'),
        (NULL, '2014-01-01 00:00:00'),
        (NULL, '2014-01-02 00:00:00')
) AS temp (t, u)
order by isnull(t, u);

The date is still after the datetimes, but now the datetimes are in order:

t           u
NULL        2014-01-01 00:00:00
NULL        2014-01-01 00:01:00
NULL        2014-01-01 23:59:59
2014-01-01  NULL
NULL        2014-01-02 00:00:00
2014-01-02  NULL

Using partitions produce similar results:

select 
    *,row_number() over (
    partition BY g
    ORDER BY isnull(t, u)
    ) rn
from (
    values
        (123, '2014-01-01', NULL),
        (123, '2014-01-02', NULL),
        (123, NULL, '2014-01-01 00:01:00'),
        (123, NULL, '2014-01-01 23:59:59')
) AS temp (g, t, u);


select 
    *,row_number() over (
    partition BY g
    ORDER BY isnull(t, u)
    ) rn
from (
    values
        (123, '2014-01-01', NULL),
        (123, '2014-01-02', NULL),
        (123, NULL, '2014-01-01 00:01:00'),
        (123, NULL, '2014-01-01 23:59:59'),
        (123, NULL, '2014-01-01 00:00:00'),
        (123, NULL, '2014-01-02 00:00:00')
) AS temp (g, t, u);

Any guidance with regard to what is happening or how I can reproduce this behavior would be appreciated.

Upvotes: 3

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you run this query:

select temp.*, isnull(t, u)    from (values
        ('2014-01-01', NULL),
        ('2014-01-02', NULL),
        (NULL, '2014-01-01 00:01:00'),
        (NULL, '2014-01-01 23:59:59'),
        (NULL, '2014-01-01 00:00:00'),
        (NULL, '2014-01-02 00:00:00')
     ) temp (t, u)
order by isnull(t, u)

You will see what happens. Only the first 10 characters appear. You can see this in this rex tester.

isnull() has to determine a type for the expression. It chooses a string, whose length is the first argument. Hence, the results make perfect sense. This is explained in the documentation:

Returns the same type as check_expression.

Note that the ANSI-standard coalesce() does more work on type-checking, so it returns what you expect.

Upvotes: 6

Related Questions