Problem with set rowcount

[huber1]

Hi

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 11.0.1.2569. The wrong result happens when the SELECT statement uses the ORDER BY clause.

Running the above statements in SQL Anywhere 11.0.1.2472 server version, SQL Anywhere returns 10 records as expected.

Regards, Robert

[huber1]

Could anyone point me please to the URL where I can report a SQL Anywhere bug, so I could enter the above problem with SET ROWCOUNT n?

Thanks a lot and regards, Robert

[Breck_Carter]

Posting it here as you have done is probably good enough, but here is the URL: http://case-express.sybase.com

[Breck_Carter]

What is the value of the string_rtruncation option? Did it change between your 11.0.1 and 12.0.1 databases?

[Former Member]

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

1. the optimizer was bypassed, and
2. the query contains a SORT TOP N operator.

The problem is fixed in 11.0.1 build 2599. Fixes for 12.0.x releases will also be available shortly.

Possible workarounds are:

1. Use SELECT TOP N rather than SET ROWCOUNT
2. Use the OPTION( FORCE OPTIMIZATION ) clause to avoid optimizer bypass.

[VolkerBarth]

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.

[huber1]

According to the Sybase docu, it seems to me the result for SET ROWCOUNT can be non-deterministic. But this also seems to be the case for SELECT TOP n ...

If I run in ISQL for example SELECT TOP 10 * FROM roles - I get a warning saying "The result returned is non-deterministic". This warning doesn't appear when I add the ORDER BY clause.

[VolkerBarth]

IMHO, it's obvious that TOP n without an ORDER BY has to be non-determinstic - therefore the warning is reasonable.

[huber1]

I agree that TOP n implies an order of rows, therefor "needs" the ORDER BY clause to work properly. But then, TOP can not be the alternative to SET ROWCOUNT, as this does not imply an order (therefor not giving a warning when not using the ORDER BY clause). The background is that we use Servoy as development environment which uses ROWCOUNT (as far as I understand) implicitly to restrict the number of displayed records. And with the above mentioned versions of SQL Anywhere 11 and 12, the whole applications developed are broken.

[Breck_Carter]

You did say that your SELECT statements included ORDER BY, so TOP would be an alternative.

[huber1]

There are some statements with ORDER BY clause and some without using the ORDER BY clause. The problem is that the Servoy IDE (www.servoy.com) does handle this "in the background" as it is "automatically" generating an SQL statement depending on a form bound to a table. So as a developer I have no (or limited) influence to this. PS: I figured out how to make a case with Sybase and did open a case.