Integer overflow when calling generate_series
(1) By Suyang Zhong (suyang) on 2023-04-26 14:50:42 [source]
Consider the following query:
SELECT count(*) FROM generate_series(9223372036854775807,9223372036854775807);
I suppose the expected result should be 1. However, it takes a long time to run. I have read the documentation and noticed it's not defined in very detail. I also noticed one post before, saying that the default end value was 9223372036854775807.
For your reference, here are some other queries and their results.
> SELECT count(*) FROM generate_series(9223372036854775806,9223372036854775806); 1 > SELECT count(*) FROM generate_series(1<<64,1<<64); 1 > SELECT * FROM generate_series(9223372036854775807,9223372036854775807) LIMIT 10; 9223372036854775807 -9223372036854775808 -9223372036854775807 -9223372036854775806 -9223372036854775805 -9223372036854775804 -9223372036854775803 -9223372036854775802 -9223372036854775801 -9223372036854775800
I also checked with UBSan, the messages are follows, which indicates that the signed long long value overflows
SQLite version 3.42.0 2023-04-26 13:25:30 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> SELECT count(*) FROM generate_series(9223372036854775807,9223372036854775807); shell.c:4454:18: runtime error: signed integer overflow: 9223372036854775807 + 1 cannot be represented in type 'long long'
I run on the latest version 3051d6a7.
(2.2) By Larry Brasfield (larrybr) on 2023-04-29 15:48:48 edited from 2.1 in reply to 1 [link] [source]
(Edited to reflect developments.)
A cure for this is checked-in.