COUNT_BIG(*) is delusional

[Breck_Carter]

COUNT_BIG returns an integer, not a bigint...

SELECT @@VERSION, EXPRTYPE ( 'SELECT COUNT_BIG() FROM dummy', 1 ) AS "COUNT_BIG";

@@VERSION,COUNT_BIG
'12.0.1.3298',int

@@VERSION,COUNT_BIG
'16.0.0.1512',integer

...so it doesn't live up to its promises.

---

Is it time for an EBF? ... SQL Anywhere Network Server Version 16.0.0.1512

The following fails on a database with only 8 user tables and 18 user columns...

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"

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

This fails too, and it shouldn't...

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

SELECT COUNT_BIG(*) * COUNT_BIG(*) * COUNT_BIG(*)
  FROM SYSCOLUMN AS A 

It works if you CAST, but you shouldn't have to...

SELECT CAST ( COUNT_BIG(*) AS BIGINT ) * CAST ( COUNT_BIG(*) AS BIGINT ) * CAST ( COUNT_BIG(*) AS BIGINT ) 
  FROM SYSCOLUMN AS A

cast(COUNT_BIG() as bigint)*cast(COUNT_BIG() as bigint)*cast(COUNT_BIG() as bigint)
9555119848

[Former Member]

I haven't tried in 16.0.0.1512 but the 3 examples with COUNT_BIG above do produce the exact same results in 12.0.1.3942.

[Breck_Carter]

By "exact same" you mean same as reported here... that is also true for 12.0.1.3298, which is bizarre because I swear it used to work OK. The ugly CROSS JOIN is used as a "long running query" test for Foxhound version 2, but it is possible I stopped letting the query run to completion after upgrading to 3298 so the problem was never noticed.

The problem lies in the data type: SELECT EXPRTYPE ( 'SELECT COUNT_BIG() FROM dummy', 1 ) returns 'int' which is wrong wrong wrong.

[VolkerBarth]

For further deja-vus (and it seems to have been less wrong):

http://sqlanywhere-forum.sap.com/search/?q=count_big&Submit=search&t=question

[Breck_Carter]

Forgot about that one, which talked about 32-bit version 12.0.1.3298: Does COUNT_BIG only work with 64-bit version?

In this question, it is the 64-bit version of 16.0.0.1512 with the problem. It also appears the 64-bit version of 12.0.1.3298 has the same problem so that other question presumed something that wasn't true.

In other words, COUNT_BIG is pointless 🙂

[VolkerBarth]

(and it seems to have been less wrong)

I have to correct myself: Apparently, it wasn't any better with older 12.0.1 versions, see my comment on the cited FAQ...