If I run SET ROWCOUNT 10; and SELECT * FROM roles ORDER BY sort_order (generally spoken a SELECT statement with an ORDER BY clause)
on SQL Anywhere a 12.0.1:3311 database, SQL Anywhere only returns 9 records instead of 10 records. The same (wrong) result happens in Sybase 220.127.116.1169. The wrong result happens when the SELECT statement uses the ORDER BY clause.
Running the above statements in SQL Anywhere 18.104.22.1682 server version, SQL Anywhere returns 10 records as expected.
asked 22 Apr '11, 09:03
Thanks for reporting this. The problem with SET ROWCOUNT is indeed a bug, it was introduced in 11.0.1 EBF build 2541 and in version 12.0.0+ as a result of enhancements to support arithmetic expressions in TOP/START AT and LIMIT/OFFSET. We are tracking this problem as issue 667900.
The problem affects simple SELECT statements for which
The problem is fixed in 11.0.1 build 2599. Fixes for 12.0.x releases will also be available shortly.
Possible workarounds are:
Not as a solution to this problem - but when you do not need to use T-SQL, I would highly recommend to use the "SELECT TOP n * FROM roles" syntax. IMHO, it's the SQL Anywhere way to restrict result sets.
As to the docs, SET ROWCOUNT seems to be based on estimates and seems to restrict the number of fetched rows - as such I am not sure whether it's guaranteed to restrict the computed result set in the same way as TOP n does.
answered 23 Apr '11, 12:56