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.

alt text


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

asked 18 Sep '13, 10:36

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862
accept rate: 20%

edited 18 Sep '13, 16:36

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.

(18 Sep '13, 14:37) nico
Replies hidden

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.

(18 Sep '13, 14:54) Breck Carter

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

(18 Sep '13, 15:17) Volker Barth

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 :)

(18 Sep '13, 16:25) Breck Carter

(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...

(19 Sep '13, 05:09) Volker Barth
Be the first one to answer this question!
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:

×102

question asked: 18 Sep '13, 10:36

question was seen: 1,537 times

last updated: 19 Sep '13, 05:10