"Spectrum nested query error" Redshift error

Question

When I run this query in Redshift:

select sd.device_id
from devices.s_devices sd
left join devices.c_devices cd
on sd.device_id = cd.device_id

I get an error like this:

ERROR:  Spectrum nested query error
DETAIL:  
  -----------------------------------------------
  error:  Spectrum nested query error
  code:      8001
  context:   A subquery that refers to a nested table cannot refer to any other table.
  query:     0
  location:  nested_query_rewriter.cpp:726
  process:   padbmaster [pid=6361]
  -----------------------------------------------

I'm not too sure what this error means. I'm only joining to one table I'm not sure which "other table" it's referring to, and I can't find much info about this error on the web.

I've noticed if I change it from left join to join, the error goes away, but I do need to do a left join.

Any ideas what I'm doing wrong?

botchniaque · Accepted Answer · 2020-08-27 05:53:57Z

2

Redshift reference mentions:

If a FROM clause in a subquery refers to a nested table, it can't refer to any other table.

In your example, you're trying to join two nested columns in one statement.

I would try to first unnest them separately and only then join:

with 
    s_dev as (select sd.device_id from devices.s_devices sd),
    c_dev as (select cd.device_id from devices.c_devices cd)
select 
    c_dev.device_id
from c_dev 
    left join s_dev 
        on s_dev.device_id = c_dev.device_id

edited Aug 27, 2020 at 5:53

answered Aug 26, 2020 at 20:16

botchniaque

5,1844 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

del Over a year ago

I still get the same error. By the way, the tables do contain nested columns, but the device_id columns I'm joining on are both varchars.

botchniaque Over a year ago

Strange - I just ran similar query to yours on a table having 2 array columns, and I LEFT JOINed their elements and did not see any error.

user34977 · Accepted Answer · 2021-04-08 12:31:06Z

1

The solution that worked for me, was to create a temporary table with the nested table's data and then join the temp table with the rest of the tables I needed to.

For example, if the nested table is spectrum.customers, the solution will be:

 DROP TABLE IF EXISTS temp_spectrum_customers;

 CREATE TEMPORARY TABLE
     temp_spectrum_customers AS
 SELECT c.id, o.shipdate, c.customer_id
 FROM spectrum.customers c,
      c.orders o;

 SELECT tc.id, tc.shipdate, tc.customer_id, d.delivery_carrier
 FROM temp_spectrum_customers tc
          LEFT OUTER JOIN orders_delivery d on tc.id = d.order_id;

answered Apr 8, 2021 at 12:31

user34977

134 bronze badges

Collectives™ on Stack Overflow

"Spectrum nested query error" Redshift error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related