Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Error while running very long SQL query with UNION ALL operator in Vertica

edited August 20 in General Discussion

To have a single query that needs to be passed to another service, our application generated a query string. One portion of the query will have a sub-query string to union output rows like for e.g.

SELECT 1 as ID, 345.45 as PaidAmt FROM DUAL
UNION ALL 
SELECT 2 as ID, 789.45 as PaidAmt FROM DUAL
UNION ALL

There are some cases when this subquery becomes huge as the number of rows with UNION ALL increases. Vertica supports this query to some extent. But when there is let's say 3000 + UNION ALL operator used in a subquery it throws an error.

[SQL Error [4963] [54001]: [Vertica]VJDBC ERROR: The query contains a SET operation tree that is too complex to analyze

I could not find any documents that talk about the limited use of UNION ALL operator in query or length of the query string.

Is there any system parameter in Vertica or Linux for which we can change the value to successfully execute the query?
The error belongs to :
Class 54—Program Limit Exceeded
54001 - ERRCODE_STATEMENT_TOO_COMPLEX

PS. I have an alternate query to fix the issue removing UNION ALL. But I am looking for a solution (if any) to execute a query that went into error.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.