The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Dealing with Subquery Restrictions

A subquery is a SELECT statement embedded within another SELECT statement. The embedded subquery is often referenced as the query's inner statement, while the containing query is typically referenced as the query's statement, or outer query block.
One restriction of a subquery is that you cannot have a correlated expressions with an OR.
Example:
dbadmin=> SELECT * FROM a; c1 | c2 ----+---- 1 | 1 2 | 3 3 | 5 4 | 10 (4 rows) dbadmin=> SELECT * FROM b; c1 | c2 | c3 ----+----+---- 1 | 1 | 1 2 | 1 | 2 3 | 3 | 4 4 | 10 | 20 (4 rows dbadmin=> SELECT * FROM a WHERE EXISTS (SELECT NULL FROM b WHERE a.c1 = b.c1 AND a.c2 NOT BETWEEN b.c2 AND b.c3); ERROR 2787: Correlated subquery expressions under OR not supported
Wait, I didn’t use an OR statement! "OR" did I? My query is re-written internally in Vertica as this SQL statement:
dbadmin=> SELECT * FROM a WHERE EXISTS (SELECT NULL FROM b WHERE b.c1 = a.c1 AND (a.c2 < b.c2 OR a.c2 > b.c3)); ERROR 2787: Correlated subquery expressions under OR not supported
So how do I get around this? Simple. Re-write my query myself:
dbadmin=> SELECT * FROM a WHERE NOT EXISTS (SELECT NULL FROM b WHERE a.c1 = b.c1 AND a.c2 BETWEEN b.c2 AND b.c3); c1 | c2 ----+---- 2 | 3 3 | 5 (2 rows)
Helpful link:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/Queries/Subqueries/SubqueryRestrictions.htm
Have fun!
0