locked
FETCH FIRST/NEXT N ROWS + UNION bug or feature? RRS feed

  • Question

  • Hello,

    I am running SQL Server 2012 RC0 (11.0.1750) LocalDB. I was testing the new paging feature of Denali and found 1 interesting problem.

    This simple query returns me 10 rows as it should:

    select o.OrderID from [Orders] o
    union select h.OrderID from [Order Details] h
    order by 1
    offset 0 rows fetch first 10 rows only

    However, if i want to evaluate the returned rows count as:

    select count(1) from (select o.OrderID from [Orders] o
    union select h.OrderID from [Order Details] h
    order by 1
    offset 0 rows fetch first 10 rows only) a

    I get the total rows count i have in [Orders] (for example, 830).

    I first thought that that "fetch first/next rows" cannot be used in such sub queries but then investigated more.

    This query will return the same 10 rows:

    select o.OrderID from [Orders] o
    order by 1
    offset 0 rows fetch first 10 rows only

    And this query will return 10:

    select count(1) from (select o.OrderID from [Orders] o
    order by 1
    offset 0 rows fetch first 10 rows only) a

    Of course, the UNION used in the above queries is just for simple testing purposes. But for some reasons SELECT from SELECT returns all rows in case UNION is used while it should return just 10 records.

    Did anybody encounter with this? Are there any workarounds?

    Thanks in advance,

    Alexey.

     

    Wednesday, January 18, 2012 6:10 PM

Answers

All replies

  • So this query returns 10:

    select count(1) from
       (select o.OrderID from [Orders] o
        order by 1
        offset 0 rows fetch first 10 rows only
        union
        select h.OrderID from [Order Details] h
        order by 1
        offset 0 rows fetch first 10 rows only) a

    And, no, I cannot say that I like it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 18, 2012 10:25 PM
  • Nope, this query returns 830.

    While this one returns 10:

    select count(1) from (select o.OrderID from [Orders] o
    order by 1
    offset 0 rows fetch first 10 rows only) a

     

    Also if u replace count(1) with the asterisk -> all 830 rows will be returned from [Orders]:

    select * from
       (select o.OrderID from [Orders] o
        order by 1
        offset 0 rows fetch first 10 rows only
        union
        select h.OrderID from [Order Details] h
        order by 1
        offset 0 rows fetch first 10 rows only) a


    Alexey Daryin
    Thursday, January 19, 2012 5:34 AM
  • Do you actually run these queries against Northwind? In such case, which build of SQL 2012 do you have?

    Or did you run against some other tables, and got the analoguous result?

    In a way, it would be great if you got back 830, because then the result would be clearly incorrect, and Microsoft could disapprove the syntax without any concern for backwards compatibility.

    In any case, since I don't like this behaviour, I have filed a bug on Connect:
    https://connect.microsoft.com/SQLServer/feedback/details/719420/unexpected-results-when-using-order-by-fetch-from-in-derived-tables-with-union


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Alexey Daryin Friday, January 20, 2012 6:05 AM
    Thursday, January 19, 2012 8:56 PM
  • Erland, thanks much!

    Yes, this are the Northwind tables ported I think from SQL 2000 via our components. But the schema and data are the same.

    Thanks again for the help.


    Alexey Daryin
    Friday, January 20, 2012 6:05 AM