The following high-CPU stress test query gave an interesting result. SYSCOLUMN contains only 2013 rows.

12.0.1.3298

SELECT COUNT(*) 
  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"

FWIW COUNT(*) seems to have INTEGER as its data type...

SELECT EXPRTYPE ( 'SELECT COUNT(*)', 1 );

EXPRTYPE('SELECT COUNT(*)',1)
int

...so that might explain why 2013 * 2013 * 2013 = 8,157,016,197 causes difficulties (an INTEGER is limited to 2,147,483,647)


When intra-query parallelism was turned off, the query failed in a different fashion: an invalid result -432918395...

SET TEMPORARY OPTION MAX_QUERY_TASKS = '1';

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

Execution time: 2433.895 seconds

COUNT()
-432918395

The first query used 8 CPUs, and took 622 seconds to fail... whether it was "finished" or not is unknown, but it probably was getting close since it had been using fewer and fewer CPUs for the last minute or so.

asked 21 Oct '12, 10:57

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

edited 21 Oct '12, 12:00


COUNT_BIG() - introduced with v12 - is your friend:) It returns a value of type BIGINT, whereas COUNT() is documented to be limited to INT.

That's not to say these error messages are expected:)

permanent link

answered 22 Oct '12, 03:27

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

One should note that in contrast to COUNT(), SUM() is not limited to INT, cf. the following SUM result is of type NUMERIC(27, 0):

select exprtype('select sum(col) from'
   || ' (select cast(1234567890123456789 as bigint) as col) S', 1);
(22 Oct '12, 05:24) Volker Barth

ahhh, yes... what was that about "it's time to read the docs cover to cover"? :)

(22 Oct '12, 08:39) Breck Carter
Replies hidden

Well, in a rare moment, I succeeded in suppressing that remark:)

(22 Oct '12, 08:46) 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:

×101

question asked: 21 Oct '12, 10:57

question was seen: 1,311 times

last updated: 22 Oct '12, 08:46