26Z10c5

WITH numbers AS (
    SELECT CAST(1 AS INT) AS pos UNION ALL 
    SELECT CAST(2 AS INT) UNION ALL 
    SELECT CAST(3 AS INT) UNION ALL 
    SELECT CAST(4 AS INT) UNION ALL 
    SELECT CAST(5 AS INT) UNION ALL 
    SELECT CAST(6 AS INT) UNION ALL 
    SELECT CAST(7 AS INT) UNION ALL 
    SELECT CAST(8 AS INT) UNION ALL 
    SELECT CAST(9 AS INT) UNION ALL 
    SELECT CAST(10 AS INT)
),
recursive_prefix AS (
    SELECT 
        o.bucket,
        -- CAST를 통해 INT 타입 시그니처 매칭
        array_to_string(
            slice(split(o.prefix, '/'), CAST(1 AS INT), CAST(n.pos AS INT)), 
            '/'
        ) AS prefix,
        o.size_bytes
    FROM object_current_state o
    CROSS JOIN numbers n
    WHERE o.__deleted = 0 
      AND o.prefix IS NOT NULL 
      AND o.prefix != ''
      AND CAST(n.pos AS INT) <= array_length(split(o.prefix, '/'))
)
SELECT 
    bucket,
    prefix,
    COUNT(1) AS total_object_count,
    SUM(size_bytes) AS total_size_bytes
FROM recursive_prefix
GROUP BY bucket, prefix;