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

edited August 2021 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
SELECT 2 as ID, 789.45 as PaidAmt FROM DUAL

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

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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file