2

I have a query with a CTE that uses a Spectrum table. When I try to union the CTE with itself:

WITH foo AS (
  SELECT col1
  FROM spectrum.bar
)
SELECT * FROM foo UNION ALL
SELECT * FROM foo;

I get the following error:

[XX000][500310] [Amazon](500310) Invalid operation: Relation "spectrum_foo_58abe8db83a3e" already exists Details: ----------------------------------------------- error: Relation "spectrum_foo_58abe8db83a3e" already exists code: 13 ..

Is this a known issue and/or is there any way to work around it (that doesn't involve pasting the original query for every union)?

4
  • This looks like a simplified example and it would be better to post an example that is closer to what you are trying to do.
    – Joe W
    Jun 7, 2019 at 16:52
  • 1
    I assumed that the simplest example would be preferable for the purposes of isolating the issue. My actual query has more complex logic in the CTE but this simplified query throws the same error when pointed at my actual tables.
    – dmca
    Jun 7, 2019 at 16:58
  • @dmca, have you found any workaround for this?
    – Erica
    Aug 23, 2019 at 9:15
  • I have not yet. Your workaround is a good solution in the meantime. Thank you
    – dmca
    Aug 23, 2019 at 19:06

2 Answers 2

3

If this can be helpful to someone, as a workaround I've created a view instead of using CTE.

CREATE VIEW foo_view AS
  SELECT col1
  FROM spectrum.bar
with no schema binding;

SELECT * FROM foo_view UNION ALL
SELECT * FROM foo_view;
1

Another work around if you want to avoid creating views and have everything disposable in the query is to create multiple common table expressions from the same source and union that.

WITH foo1 AS (
  SELECT col1
  FROM spectrum.bar
), foo2 AS (
select col1 
from spectrum.bar)

SELECT * FROM foo1 UNION ALL
SELECT * FROM foo2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.