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