Doc Shock: Is SELECT TOP really limited to 32766?

[Breck_Carter]

Egad! Zounds! Can this be true?

Why?

SQLCODE = -674, SQLSTATE = 09W07, ERRORMSG() = Statement's size limit is invalid

"The size limit must be a constant integer greater than 0 and less than 32767."

http://dcx.sybase.com/index.html#1101en/saerrors_en11/errm674.html

...same thing in V12 docs.

---

Yes, I actually got the message... not sure yet what the TOP value actually was, still trying to get over the Doc Shock!

[MarkCulp]

I think this is a mistake in the docs. I tried "select top 100000 * from systable" with SA12 and did not get an error. I checked the code and it would appear that at one time there was an upper limit check but it has been removed. I will double check and then get the docs fixed.

[MarkCulp]

BTW: How did you get the error? What version/build & what tool did you use? The limit check was removed in 2003.

[Breck_Carter]

@Mark: I haven't had time to check, but I suspect it was SELECT @top with a variable that was NULL... that would do it, right? 🙂

[johnsmirnios · Accepted solution]

The problem in dbisqlc stems from the database server reporting that it "definitely knows the row count for this query will be 26" (or whatever value was in the TOP phrase) by reporting a positive value in the SQLCOUNT field of the SQLCA. dbisqlc is easily fixed by just never letting it trust the count from the server (in which case is does just a little bit of extra work); however, I also wonder if trusting the TOP clause represents an server bug too.

[VolkerBarth · Accepted solution]

So this is a bug that will be fixed for dbisqlc in current versions?

[Breck_Carter · Accepted solution]

@John: "will not exceed 26" has more truthiness than "will be 26".

[Breck_Carter · Accepted solution]

@Volker: Maybe, under SAP's direction, dbisqlw.exe will be created... an actual Windows version of ISQL. I hate to say it, and I am not taking a shot at the developers, but as a corporate entity "Watcom Doesn't Give Good GUI". It's the only major exception to this rule that I know of: "Watcom Does Things The Way They Should Be Done."

[Breck_Carter · Accepted solution]

@Everyone: That was my Evil Twin speaking. My Good Twin is a big fan of the current dbisql.com/.exe... except that I never know which one to execute 🙂

[johnsmirnios · Accepted solution]

@Volker: Yes, assuming I've made the final change it should be fixed in 12.0.1GA, 12.0.0.2602, 11.0.1.2514, and 10.0.1.4139.

[VolkerBarth · Accepted solution]

@John: Could you post the CR number here? - You know, we love more documentation:)

[johnsmirnios · Accepted solution]

CR# 645986. It fixes the problem where dbisqlc may show a subset of the result set, fixes the reporting of DSNs in the Login tab (show DSNs from all SA verions, 64-bit used to not show any DSNs), and fixes the handling of connection parameters that did not have a short form (eg, the new "Server" parameter).

[VolkerBarth]

As Mark has pointed out, that seems to be a doc mistake. I can use TOP and START AT values way bigger than 32.766 and get correct results, both when using constant values and variables.

I have tested with an 11.0.1.2427 engine and a table with 2.353.744 rows. Column 1 is the PK.

SELECT TOP 2353744 * FROM MyTable ORDER BY 1 ASC;
-- works, starts with first PK
SELECT TOP 2353744 * FROM MyTable ORDER BY 1 DESC;
-- works, starts with last PK

SELECT TOP 1 START AT 2353744 * FROM MyTable ORDER BY 1 ASC;
-- works, shows last PK
SELECT TOP 1 START AT 2353744 * FROM MyTable ORDER BY 1 DESC;
-- works, shows first PK

However, when the sum of TOP and START AT seems to be larger than the table count, the effect seems wrong when using dbisqlc (11.0.1.2452 and 12.0.0.2566):

SELECT TOP 10 START AT 2353744 * FROM T_VertragDuplikat ORDER BY 1 DESC;
-- works, shows first PK 
SELECT TOP 25 START AT 2353744 * FROM T_VertragDuplikat ORDER BY 1 DESC;
-- works, shows first PK
SELECT TOP 26 START AT 2353744 * FROM T_VertragDuplikat ORDER BY 1 DESC;
-- doesn't give an error but returns an empty result set -> wrong

The incorrect empty result set is shown for any TOP value greater >= 26. (Interestingly enough, dbisqlc 12.0.0.2566 starts to show the empty set for TOP >= 25.)

In contrast, when using DBISQL (11.0.1.2452 and 12.0.0.2566), any increate of TOP n does still show the result set with the first PK.

Resume: So the TOP problem seems to be restricted to dbisqlc.

[Former Member]

I did remember having hit that 32k limit myself too. In my db code (v9 compatible) I see this:

// select top n has a max number of 32767 
StringBuilder sSQL= new StringBuilder(); 
sSQL.append("select "); 
if (rowCount >= 0 && rowCount <= 32767) 
{ 
  sSQL.append("TOP "); 
  sSQL.append(Long.toString(rowCount)); 
  sSQL.append(" "); 
}

I think the limit was raised in V10 and upward.

André

[reimer_pods]

It also works correctly for 9.0.2.3480. I've tried
select top 1234567 start at 2345678 *
from mytable
order by id
and get no error, the result set looks good.

[Breck_Carter]

@Everyone: Yay! Everyone gets an upvote!