WITH (
        SELECT arrayResize([1], 1000000)
    ) AS arr
SELECT length(arr)

Query id: 60eaa526-a0e3-4a56-a54b-75f0e26c6606

   ┌─length(arr)─┐
1. │     1000000 │
   └─────────────┘

1 row in set. Elapsed: 0.967 sec. 

SET allow_experimental_analyzer = 0

Query id: 357f9028-abdf-4d1d-9875-69e4ac0f2af7

Ok.

0 rows in set. Elapsed: 0.001 sec.

WITH (
        SELECT arrayResize([1], 1000000)
    ) AS arr
SELECT length(arr)

Query id: e35bd81a-36cd-427c-aeec-916ff20cb7c6

   ┌─length(arr)─┐
1. │     1000000 │
   └─────────────┘

1 row in set. Elapsed: 0.011 sec.