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

Question

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 CTE**s?

AWS-User-4315904 · Answer

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)

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

Add your answer

Relevant content