Question Re: Does COUNT_BIG only work with 64-bit version? in Technology Q&A

Does COUNT_BIG only work with 64-bit version?

Breck_Carter — Tue, 13 Nov 2012 09:04:15 GMT

Here's what happens on the 32-bit version of SQL Anywhere Network Server Version 12.0.1.3298

SELECT COUNT_BIG(*) 
  FROM SYSCOLUMN AS A 
       CROSS JOIN SYSCOLUMN AS B
       CROSS JOIN SYSCOLUMN AS C;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Value SUM() out of range for destination
SQLCODE=-158, ODBC 3 State="22003"

Re: Does COUNT_BIG only work with 64-bit version?

VolkerBarth — Tue, 13 Nov 2012 10:21:22 GMT

Hm, can't tell, that query really takes loooong for one of my test databases (too many columns?).

However, the following simpler query returns no error but a wrong result in dbisqlc and DBISQL 12.0.1.3769:

SELECT COUNT_BIG(*) 
FROM sa_rowgenerator(1, 2048) AS A
   CROSS JOIN sa_rowgenerator(1, 2048) AS B 
   CROSS JOIN sa_rowgenerator(1, 2048) AS C;

It should return 2^11 * 2^11 * 2^11 = 2^33 = 8,589,934,592 but returns (after quite a while...) 0, which seems to be the result of an 2^32 overflow.

However, that query seems to be running one one thread, whereas your COUNT_BIG() query over SYSCOLUMN seems to be running parallel, which might make a difference as to the underlying query execution...

Re: Does COUNT_BIG only work with 64-bit version?

VolkerBarth — Thu, 19 Sep 2013 03:34:48 GMT

which seems to be the result of an 2^32 overflow.

That seems fitting to Breck's current observation, as the return type of COUNT_BIG() is still an int (checked with the freshest 12.0.1.3942 EBF):

COUNT_BIG(*) is delusional