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"

asked 13 Nov '12, 09:04

Breck%20Carter's gravatar image

Breck Carter
27.4k424585838
accept rate: 21%

retagged 18 Nov '12, 11:54

Nica%20_SAP's gravatar image

Nica _SAP
866722


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

permanent link

answered 13 Nov '12, 10:21

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654
accept rate: 32%

edited 13 Nov '12, 10:26

1

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

(19 Sep '13, 03:34) Volker Barth
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:

×102
×5
×1

question asked: 13 Nov '12, 09:04

question was seen: 1,114 times

last updated: 19 Sep '13, 03:54