Dealing with Subquery Restrictions

Jim_Knicely · August 2018

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!