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

asked 22 Apr '11, 09:03

robert's gravatar image

robert
578293748
accept rate: 0%

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

(26 Apr '11, 08:07) robert

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

(26 Apr '11, 08:26) Breck Carter

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

(26 Apr '11, 08:27) Breck Carter

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.
permanent link

answered 27 Apr '11, 11:33

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

edited 27 Apr '11, 11:40

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665

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.

permanent link

answered 23 Apr '11, 12:56

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

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.

(24 Apr '11, 06:33) robert
Replies hidden

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

(24 Apr '11, 06:47) Volker Barth

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.

(24 Apr '11, 07:49) robert
Replies hidden
1

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

(26 Apr '11, 08:29) Breck Carter

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.

(26 Apr '11, 09:12) robert
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×49
×6

question asked: 22 Apr '11, 09:03

question was seen: 2,726 times

last updated: 27 Apr '11, 11:40