Do Redshift tables with SUPER data type support joins with recursive CTEs?

This sql in Redshift

WITH RECURSIVE

item_array AS (
    SELECT JSON_PARSE('[7, 8, 9]') AS items
),

idx_array(idx) AS (
SELECT 1 AS idx
UNION ALL
SELECT idx + 1 AS idx
FROM idx_array
WHERE idx < 2
)

SELECT
    items[0]
FROM item_array
CROSS JOIN idx_array

produces the error

[XX000] ERROR: Query unsupported due to an internal error. Detail: Unsupported query. Where: RTE kind: 11.

But if CROSS JOIN idx_array line removed, it works.

Can we not join tables with SUPER types in Recursive CTEs?

주제

분석

태그

아마존 Redshift

언어

English

vida

질문됨 2년 전1385회 조회

1개 답변

최신
최다 투표
가장 많은 댓글

이 답변이 도움이 되었나요?커뮤니티가 여러분의 지식을 활용할 수 있도록 정답을 찬성하세요.

Hi, The problem seems to be in the select statement and not cross join. If I use the below statement instead, cross join works as expected.

WITH RECURSIVE

item_array AS ( SELECT JSON_PARSE('[7, 8, 9]') AS items ),

idx_array(idx) AS ( SELECT 1 AS idx UNION ALL SELECT idx + 1 AS idx FROM idx_array WHERE idx < 2 )

SELECT * FROM item_array CROSS JOIN idx_array;

It gives me

items | idx

---------+-----

[7,8,9] | 1

[7,8,9] | 2

(2 rows)

지원 엔지니어

AWS-User-4315904

답변함 2년 전

vida
2년 전
Firstly, thank you for looking into this! It works with SELECT * or SELECT items. But when you index items as in SELECT items[0], it fails. So, does that mean items is no longer SUPER?; which goes back to the original question whether SUPER types are not supported with recursive CTEs. Thank you!

Do Redshift tables with SUPER data type support joins with recursive CTEs?

관련 콘텐츠