Professional Web Applications Themes

Failure to obey ORDER BY - Microsoft SQL / MS SQL Server

"Delbert Glass" <com> wrote in message news:phx.gbl... Hi Delbert, I didn't read your whole post so excuse me if I'm off the track, but wouldn't it be best to break the 2 queries down into 2 seperate views and then union those? -- Michael Culley...

  1. #1

    Default Re: Failure to obey ORDER BY

    "Delbert Glass" <com> wrote in message
    news:phx.gbl...

    Hi Delbert,

    I didn't read your whole post so excuse me if I'm off the track, but
    wouldn't it be best to break the 2 queries down into 2 seperate views and
    then union those?

    --
    Michael Culley


    Michael Guest

  2. #2

    Default Re: Failure to obey ORDER BY

    Delbert,

    on my system, the query plans show no ordering before the TOP 1 is
    applied. That explains everything.

    My guess is, that it has something to do with the fact that it is a
    heap, because adding a (clustered) primary key to the table caused
    SQL-Server to return the correct results.

    Nonetheless, the results are incorrect, which means it is a bug.

    Gert-Jan


    Delbert Glass wrote: 
    Gert-Jan Guest

  3. #3

    Default Re: Failure to obey ORDER BY

    Delbert Glass (com) writes: 

    No.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  4. #4

    Default Re: Failure to obey ORDER BY


    "Gert-Jan Strik" <nl> wrote in message
    news:nl...
     

    not necessilary.

    --drop table clusteredtable
    create table clusteredtable (a int primary key clustered)
    insert into clusteredtable values (111)
    insert into clusteredtable values (113)
    insert into clusteredtable values (112)

    select * from clusteredtable

    a
    -----------
    111
    112
    113

    Great.


    But:

    select * from (
    select top 1 a
    from clusteredtable
    order by a desc
    union all
    select 99 as a
    ) dt

    says:

    a
    -----------
    111
    99

    but it should be:

    a
    -----------
    113
    99

    or we should have gotten an error
    for including the ORDER BY that we wrote.

    For comparision:

    select * from (
    select top 1 a
    from clusteredtable
    order by a desc
    ) dt

    says:

    a
    -----------
    113

    --------------------------------------------------------

    If you try rewritting more simply
    than I did in another post:

    select * from (
    (
    select top 1 a
    from clusteredtable
    order by a desc
    )
    union all
    select 99 as a
    ) dt

    you still get the wrong results
    (even using a table clustered on the column):

    a
    -----------
    111
    99

    Perhaps writting it like that makes it clearer to more people,
    that the results are incorrect since it removes the question
    about whether or not the query is even valid.

    Bye,
    Delbert Glass


    Delbert Guest

  5. #5

    Default Re: Failure to obey ORDER BY


    "Erland Sommarskog" <se> wrote in message
    news:0.0.1...
     
    > ... 
    >
    > No.[/ref]

    Are you sure? What not?
    Look at:
    select * from t
    again.

    ------

    If I rewrite it like this:

    select * from (
    (
    select top 1 a
    from t
    order by a desc
    )
    union all
    (
    select top 1 a
    from t
    order by a desc
    )
    ) dt

    I get still get the wrong results:

    a
    -----------
    13
    13

    instead of the recorrect results:

    a
    -----------
    11
    11

    If I create this clustered index:
    create clustered index index_t_a on t(a)

    and try again I get:

    a
    -----------
    11
    11

    for both versions of the query.

    Hey, that's like Gert-Jan Strik said.
    But if you look at my reply to his post
    you will see that I posted an example
    of wrong results using a (different) clustered table.

    Bye,
    Delbert Glass



    Delbert Guest

  6. #6

    Default Re: Failure to obey ORDER BY


    "Delbert Glass" <com> wrote in message
    news:phx.gbl... 
    > > ... 
    > >
    > > No.[/ref]
    >
    > Are you sure? What not?
    > Look at:
    > select * from t
    > again.
    >
    > ------
    >
    > If I rewrite it like this:
    >
    > select * from (
    > (
    > select top 1 a
    > from t
    > order by a desc
    > )
    > union all
    > (
    > select top 1 a
    > from t
    > order by a desc
    > )
    > ) dt[/ref]


    Sorry those 'desc' should have been 'asc'.

     

    Oops, I got missed up too.
    The queries to not work correctly whether or not the table is clustered.
    Of course due to which rows comes first when clustered along
    with other confusion, it can seem like a query is working
    since one happens to get the expected results.

    Also watch out,
    if you add a clustered index and then drop it
    the unordered rows will be left in the order
    of the clustering rather then the original sequence.

    One can drop table the table and insert the data again rather than
    just droping the cluster.

    One may want to data inserted in such a manner that the first and
    the last too rows do not have the largest or smallest values.

    Bye,
    Delbert Glass


    Delbert Guest

  7. #7

    Default Re: Failure to obey ORDER BY

    More clearly.

    select * from (
    (
    select top 1 ColA
    from (
    select 3 as ColA
    union all select 2
    union all select 5
    union all select 4
    ) somedata
    -- order by ColA asc
    -- order by ColA desc
    )
    union all
    select 99
    ) dt

    says:

    ColA
    -----------
    3
    99

    Using either ORDER BY makes no difference.
    But they should!

    The asc one should say a 2 instead of a 3.
    The desc one should say a 5 instead of a 3.

    Bye,
    Delbert Glass


    Delbert Guest

  8. #8

    Default Re: Failure to obey ORDER BY

    > Think about it. 

    Sure, that would be great, but all tools have bugs, what do you expect?

    --
    Michael Culley


    Michael Guest

  9. #9

    Default Re: Failure to obey ORDER BY

    Unless the ORDER BY and TOP clauses are part of
    an aliased derived table, any ORDER BY but the last
    will be ignored.

    In your example, you have a set of parentheses around
    select top 1 ColA
    ....
    order by ColA asc

    but that doesn't make it a derived table. This works
    as expected:

    select * from (
    select * from (
    select top 1 ColA
    from (
    select 3 as ColA
    union all select 2
    union all select 5
    union all select 4
    ) somedata
    order by ColA asc -- within aliased derived table
    ) derived
    union all
    select 99
    ) dt

    Steve Kass
    Drew Uiversity

    Delbert Glass wrote:
     

    Steve Guest

  10. #10

    Default Re: Failure to obey ORDER BY

    "Delbert Glass" <com> wrote in message
    news:phx.gbl... 

    Your software is 100% bug free? Do you fix *everything* that gets reported
    to you?
     

    In this case the fix would break some databases which would be unacceptable.

    --
    Michael Culley


    Michael Guest

  11. #11

    Default Re: Failure to obey ORDER BY


    "Steve Kass" <edu> wrote in message
    news:phx.gbl... 

    Could that be because the vendor can more easily
    get away with white-washing the customer
    rather then correct the bugs?

    Bye,
    Delbert Glass


    Delbert Guest

  12. #12

    Default Re: Failure to obey ORDER BY


    "Michael Culley" <com.au> wrote in message
    news:phx.gbl... 
    >
    > Your software is 100% bug free?[/ref]

    Much closer then is commonly typical.
    If fact so close, that non-software problems expand to fill the void.
    Not uncommonly the non-software problems
    are "manufactered" into existence since people (not the customers btw)
    want to have something to complain about.
     

    I fix every that is reported to me.

    With rare expections, I fix every that is reported to me.

    With rare expections, I fix every that is reported to me
    and also fix many things that are not reported to me;
    infact, typically many more things than things that are reported.

    There that sounds about right.

    Except sometimes it is very necessary to add on:
    that I am allowed too

    But I typically fix many of those too.
    Let's skip going into trying to describe situations
    such that one is wrong for doing one's job
    but also wrong for not doing it too.
     
    >
    > In this case the fix would break some databases which would be[/ref]
    unacceptable.

    Aren't they already broken?

    Bye,
    Delbert Glass



    Delbert Guest

  13. #13

    Default Re: Failure to obey ORDER BY

    I think I am trying to understand what is going on. When using UNION or
    UNION ALL, the ORDER BY is applied to the resultset of the UNION, not to
    any individual query.

    Let's break down the original query.

    select * from (
    select top 1 a
    from t
    order by a asc
    union all
    select top 1 a
    from t
    order by a asc
    ) dt

    Let's remove the (useless) outer select.

    select top 1 a
    from t
    order by a asc
    union all
    select top 1 a
    from t
    order by a asc

    What we have here is a UNION SELECT with one normal ORDER BY clause at
    the end (the second ORDER BY), and an ORDER BY at the wrong position.
    (the first ORDER BY).

    Running the query will result in an error

    Server: Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword 'union'.

    This is, because the first ORDER BY clause is not allowed there. The
    (second) ORDER BY will be applied to the resultset of the union-query.

    To illustrate this even better, run the following query

    select * from (
    select top 1 a
    from t
    order by newid() asc
    union all
    select top 1 a
    from t
    order by a asc
    ) dt

    Server: Msg 104, Level 15, State 1, Line 1
    ORDER BY items must appear in the select list if the statement contains
    a UNION operator.

    It is clear that the ORDER BY is applied at a different level. It is not
    applied to the SELECT TOP 1, but to the resultset of the SELECT UNION
    ALL.

    IMHO the bug is, that SQL-Server does not report this syntax error when
    it is hidden in the derived table.

    BTW, this also means that the query

    select * from (
    select top 1 a
    from t
    union all
    select top 1 a
    from t
    order by a asc
    ) dt

    can return any 2 values from the table, and these values will be
    returned in sorted order...

    Gert-Jan
    Gert-Jan Guest

  14. #14

    Default Re: Failure to obey ORDER BY

    Hey, beat me to it (while part of it).
    I was going to make a post
    pointing out that if you try running the inside
    of the subquery (or dervived table) by itself
    you'll get an error about having the extra ORDER BY-s.

    And then ask:

    Why let me include extra ORDER BY-s only to turn
    around and ignore them? Which encourages one to
    think the issue could be a matter of failing to raise an
    error about the extra ORDER BY-s.

    But then go on to point out, that the above is not so great.
    Via saying a some words about "last one" not being well defined.
    (last one present vs last one possible). And then point out
    one should recall one of my other posts in this tread I
    that talks about whether or not the ORDER BY in the last possible position
    goes with the TOP or with the entire UNION.
    If you look at that other post, you'll see that the ORDER BY
    can use columns from the last portion instead of having to use
    columns names for the entire union.
    Furthermore if the ORDER BY goes to the whole UNION,
    that would make it the very ORDER BY that is not suppose
    to be allowed in the subqueries and views. Thus there should be
    an error raised about including such an ORDER BY.

    So far it looks to me like the ORDER BY-s are allowed
    because they go with the TOP-s but are inappropriately ignored.

    Bye,
    Delbert Glass



    "Gert-Jan Strik" <nl> wrote in message
    news:nl... 


    Delbert Guest

  15. #15

    Default Re: Failure to obey ORDER BY

    Maybe it's a subquery, but it's bad syntax. The statement

    select * from (
    (parenthesized subquery)
    union all
    <select statement>
    ) T

    is not valid, and should not be accepted. A subquery is
    not interchangeable with a select query.


    You need to write it this way to avoid the problem:

    select * from (
    select * from (
    select top 1 ColB
    from (
    select 3 as ColB
    union all select 2
    union all select 5
    union all select 4
    ) somedata
    order by 1 asc
    ) T
    union all
    select 99
    ) dt

    If you expect every instance of incorrect syntax to
    be caught, you should probably give up T-SQL and stick
    to a good ANSI C complier for Unix. It's just not going to
    happen for T-SQL.

    SK



    Delbert Glass wrote:
     
    >
    >Note, those are rules for avoiding the problem.
    >
    >
    >

    >
    >Unless the subquery is being unioned.
    >For example recall:
    >
    >select * from (
    > (
    > select top 1 ColA
    > from (
    > select 3 as ColA
    > union all select 2
    > union all select 5
    > union all select 4
    > ) somedata
    >-- order by ColA asc
    >-- order by ColA desc
    > )
    > union all
    > select 99
    >) dt
    >
    >Notice that TOP and ORDER BY are a problem in a subquery.
    >Well, unless one is able to claim that's not a subquery.
    >
    >Bye,
    >Delbert Glass
    >
    >
    >
    >[/ref]

    Steve Guest

  16. #16

    Default Re: Failure to obey ORDER BY

    Delbert,

    I have to disagree with you. The purpose of an ORDER BY clause is to
    sort the resultset. In a query of the syntax

    SELECT ..
    UNION (ALL)
    SELECT ..

    the resultset is the combined result of the two.

    The TOP ORDER BY syntax is a relatively 'new' feature. If you use TOP
    ORDER BY in combination with UNION, the pr cannot distinguish which
    ORDER BY belongs to what resultset. It is logical that the ORDER BY on
    the UNION Select gets precedence over the proprietary Microsoft added
    feature. Remember that TOP ORDER BY syntax is pretty abnormal for SQL,
    because it is non-relational and it forces the query engine to
    materialize that part of the query.

    As Steve has remarked, the query in your next post is also simply a
    UNION SELECT in disguise. IMO, the SQL pr should throw a syntax
    error there (like I posted earlier).

    Maybe it would have been better if Microsoft had created a special
    clause to specify the order that applies to the TOP keyword, for
    example:

    SELECT TOP 1 MyColumn TOPORDER MyColumn ASC FROM MyTable ORDER BY
    MyColumn DESC

    But of course, that is all academic now. Maybe if the feature had gone
    through the ANSI committee these kinds of issues would have been
    addressed before the feature became implemented.

    BTW: Now I understand why Microsoft made it mandatory to alias a derived
    table. This makes it absolutely clear when a subquery is or is not a
    derived table.

    The bottom line: currently the facts are, that ORDER BY on UNION Select
    has priority over TOP ORDER BY Selects, and that there seems to be a
    minor bug that SQL-Server will not always disallow this conflicting
    syntax.

    The workaround is simple. Simply write derived tables if you want to use
    TOP in combination with UNION.

    Gert-Jan


    Delbert Glass wrote: [/ref]
    Gert-Jan Guest

Similar Threads

  1. #40517 [NEW]: async connections do not obey connect timeout
    By djgrrr+phpbugs at p2p-network dot net in forum PHP Bugs
    Replies: 2
    Last Post: February 19th, 01:18 PM
  2. #39493 [NEW]: simplexml_load_file does not obey default stream context
    By RQuadling at GMail dot com in forum PHP Bugs
    Replies: 2
    Last Post: November 13th, 02:09 PM
  3. Updating records in order (into an order)
    By A Ratcliffe in forum ASP Database
    Replies: 7
    Last Post: October 24th, 06:19 PM
  4. Replies: 7
    Last Post: September 4th, 10:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139