For example, a source that does not support lower nor correlated subqueries and a query like:

SELECT array_agg((select intkey from bqt1.smallb where stringkey = lower(a.stringkey))) from bqt1.smalla a

will not push stringkey = lower(a.stringkey) to the n-many subquery, even though it can be pre-evaluated with the correlated value.