locked
Bug in optimizer giving wrong results or expectations wrong? [2008r2] RRS feed

  • Question

  • Why does the first select give me the wrong order? When I check the execution plan, the order by isn't included.

    In all other selects, I can see the sort in the execution plan

     

    create table #sqlservertest(test int)
    insert into #sqlservertest (test) values (100)
    insert into #sqlservertest (test) values (300)
    insert into #sqlservertest (test) values (200)

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (
            select  top (100) percent test from #sqlservertest order by test
        ) r FOR XML PATH(''), TYPE )
    .value('.[1]','varchar(max)')

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (
            select  top (99.99) percent test from #sqlservertest order by test
        ) r FOR XML PATH(''), TYPE )
    .value('.[1]','varchar(max)')

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (
            select  top (99) test from #sqlservertest order by test
        ) r FOR XML PATH(''), TYPE )
    .value('.[1]','varchar(max)')

    select top (100) test from #sqlservertest order by test
    select top (100) percent test from #sqlservertest order by test

    drop table #sqlservertest

    Tuesday, July 5, 2011 12:30 PM

Answers

  • Did you try my suggestion?

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (select test from #sqlservertest) r 
        order by test
        FOR XML PATH(''), TYPE).value('.[1]','varchar(max)');

     


    AMB

    Some guidelines for posting questions...

    Tuesday, July 5, 2011 1:10 PM

All replies

  • > select (
    >     select cast( test as nvarchar(max) ) + ', '
    >     from (select  top (100) percent test from #sqlservertest order by test) r FOR XML PATH(''), TYPE ).value('.[1]','varchar(max)')

    IMHO, this is not a bug. The only way to ensure that the final result set will be in specific order, is using the "order by" clause in the statement returning the set to the cliente application.

    You are trying same approach as using "order by" clause in a view definition, and then expect an ordered result set when you select from the view.

    create view myVw
    as
    select top (100) percent c1, c2, c3
    from T
    order by c1
    );
    GO
    select * from MyVw;
    GO

    This is wrong, since tables have no predefined order. Move the "order by" clause to the correct position.

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (select test from #sqlservertest) r 
        order by test
        FOR XML PATH(''), TYPE).value('.[1]','varchar(max)');

     


    AMB

    Some guidelines for posting questions...

    Tuesday, July 5, 2011 12:42 PM
  • Simply because the ORDER BY in your first case is ignored by the optimizer as inner ORDER BY's are always removed in SQL Server 2008+. In the other two cases it is not removed as the combination of TOP (n) with n <> 100 tells the optimizer that it needs the ORDER BY to evalute the TOP restriction. In the first case the TOP 100 tells that it should return all rows, this the ORDER BY is not applied.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, July 5, 2011 12:45 PM
  • btw, be careful when using the TOP (n) [PERCENT] syntax, take a look at the row count:

     

    DECLARE @Sample TABLE ( test INT ) ;
    
    WITH Numbers
       AS ( SELECT 1 AS N
        UNION ALL
        SELECT N + 1
        FROM  Numbers
        WHERE N < 101
        )
     INSERT INTO @Sample
       SELECT *
       FROM Numbers
       ORDER BY N DESC ;
    
    SELECT CAST(test AS VARCHAR(4)) + ','
    FROM ( SELECT TOP ( 100 ) PERCENT
         test
       FROM  @Sample
       ORDER BY test
      ) r
    FOR  XML PATH('') ;
    
    SELECT CAST(test AS VARCHAR(4)) + ','
    FROM ( SELECT TOP ( 99.99 ) PERCENT
         test
       FROM  @Sample
       ORDER BY test
      ) r
    FOR  XML PATH('') ;
    
    
    SELECT CAST(test AS VARCHAR(4)) + ','
    FROM ( SELECT TOP ( 99 )
         test
       FROM  @Sample
       ORDER BY test
      ) r
    FOR  XML PATH('') ;
    
    


     


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, July 5, 2011 12:56 PM
  • So how do I sort my table first in the inner select with ALL data and not only 99.99% correctly? I need the result

    100, 200, 300 and not something else. The first select gives me "100, 300, 200" which is wrong.

    The case with the view is clear to me and that's what I expect. But in my case, the result depends on the inner sorting.



    Tuesday, July 5, 2011 12:58 PM
  • Did you try my suggestion?

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (select test from #sqlservertest) r 
        order by test
        FOR XML PATH(''), TYPE).value('.[1]','varchar(max)');

     


    AMB

    Some guidelines for posting questions...

    Tuesday, July 5, 2011 1:10 PM
  • Did you try my suggestion?

    select (
        select cast( test as nvarchar(max) ) + ', '
        from (select test from #sqlservertest) r 
        order by test
        FOR XML PATH(''), TYPE).value('.[1]','varchar(max)');

    Thank you! This works.

    I always put the order by at the wrong location in my previous tests. That's why I added it to the inner select

    Tuesday, July 5, 2011 1:30 PM