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)?

  • 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 '19 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 '19 at 16:58
  • @dmca, have you found any workaround for this? – nicola Aug 23 '19 at 9:15
  • I have not yet. Your workaround is a good solution in the meantime. Thank you – dmca Aug 23 '19 at 19:06
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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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