Issue found on page 'Limits'

[thiagonuic]

Hi,

I was testing the 100000 limit on the arrays to see what happens if an array if bigger than that with the following query:

select len(array_agg(generate_series)) from (select * from generate_series(1, 123456789));

But it seems DuckDB creates the full array:

┌─────────────────────────────────┐
│ len(array_agg(generate_series)) │
│              int64              │
├─────────────────────────────────┤
│                       123456789 │
└─────────────────────────────────┘

Page URL: https://duckdb.org/docs/operations_manual/limits.html

Could you please clarify what is to be expected when the 100000 limit is reached?

Thanks!

[Tishj]

It looks to me like array_agg returns a LIST, as it (array_agg(...)) has historically been an alias for list(...)

D select array_agg(generate_series) as res, len(res), typeof(res) from (select * from generate_series(1, 200_000 + 1));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────┬─────────────┐
│                                                             res                                                              │ len(res) │ typeof(res) │
│                                                           int64[]                                                            │  int64   │   varchar   │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────┼─────────────┤
│ [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33…  │  200001  │ BIGINT[]    │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────┴─────────────┘
D select array_agg(generate_series) as res, len(res), typeof(res) from (select * from generate_series(1, 80_000 + 1));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────┬─────────────┐
│                                                             res                                                              │ len(res) │ typeof(res) │
│                                                           int64[]                                                            │  int64   │   varchar   │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────┼─────────────┤
│ [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33…  │  80001   │ BIGINT[]    │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────┴─────────────┘

This can be seen in the typeof result, it's BIGINT[] instead of BIGINT[200001]

Looking at duckdb/duckdb#8983 this is also mentioned, we don't want to break people's scripts when they upgrade from an old duckdb version that uses LIST to now all of a sudden being ARRAY, with all the implications that that brings.