Support for correlated subqueries

[kokosing]

It relates to subqueries used in IN, EXISTS predicates and scalar subqueries.

[kokosing]

Relates to #2878

[matthewwardrop]

Greetings all! I note that the referenced issue was closed for 0.132... just wondering if any progress has been made on supporting correlated subqueries?

[kbajda]

@matthewwardrop : yes, @kokosing is actively working on this. Expect PRs soon!

[kokosing]

see

• Introduce Apply plan node for subqueries #4403
• Logical plan assertion DSL #4404
• Support uncorrelated subqueries in aggregation queries #4407

[matthewwardrop]

Excellent news! Thanks kokosing :). Looking forward to it being deployed :).

[kokosing]

@martint FYI

Once the initial support correlated subqueries (see https://github.com/prestodb/presto/wiki/Correlated-subqueries) is done, our (roadmap) next goals are:

• lateral joins - (Initial support for lateral joins #5879)
• support simple scan-filter-project correlated subqueries (Support correlated simple scan-filter-project queries #6383)
• implement EXISTS execution as SEMI_JOIN (Execute EXISTS predicate as SEMI JOIN #6384)
• support multi column IN predicate (Support multiple columns in IN predicate #6385)

[kokosing]

Also this is on our short term road map: #6638

[GrigorievNick]

I have case like this

select 
(select username from cassandra.raw.players_by_brand as players where brand='brand_name' and players.bucket = buckets.bIndex)
from unnest(sequence(1, 5)) AS buckets (bIndex) limit 10;

It push me same exception.
Will this be fixed too?

[kokosing]

Yes. Your case seems to be a great example to use LATERAL join which is also in flight. I would rewrite your query to something like:

select *
from unnest(sequence(1, 5)) AS buckets (bIndex), 
LATERAL (select username from cassandra.raw.players_by_brand as players where brand='brand_name' and players.bucket = buckets.bIndex)
 limit 10;

[brandynabrams]

Hi All,

Out of curiosity, will a coorelated subquery join such as this work in the future as part of your ongoing support for coorelated subqueries?

from user_people up
left join order_applications oa on oa.id = (select id from order_applications where person_id = up.id order by id desc limit 1)

[kokosing]

@brandynabrams Currently I do work on subqueries which are using LIMIT (and ORDER BY).

Generally, since all the subqueries in TPCH and TPCDS extending support for subqueries become less important and we switched to things that may affect broader user audience (like join reordering).

Anyway, still I feel somehow personally related and engaged with subqueries, push the support for them in my spare time. Once I finish #8435, I could start work on subqueries with LIMIT.

[brandynabrams]

Hey @kokosing,

Thanks for the reply! Yes, I agree tasks like join reordering (which would be awesome) are more important.

I'm very used to Postgres, and subqueries that use limit & order by was used very frequently by myself & my team.

Some of our micro services still use postgres, but our data warehouse (treasure data) uses hive & presto combo.. and this type of unsupported subquery makes its frustrating sometimes.. currently my work around is a window function w/ rank() & partition by or joining the table to itself with a max() function, but these type of subqueries that involve limit / order by seem to be way simpler and provide cleaner code.. (if you use other syntax that is more performant / better to use.. i'm happy to try it out)

But yah, long story short, if you are able to find time to add these types of subqueries.. that would be AWESOME! thanks man.

[kokosing]

Todays support for correlated subqueries is decent in Presto. There are still some subquery patterns which are still not supported, but these should be tracked as separate issues.

[aandis]

hey @kokosing which connectors support correlated subqueries in presto?

[kokosing]

All. Because subqueries are supported by query planner and execution engine.

I kind of do not understand question. If you mean which support subquery pushdown then none.

[aandis]

@kokosing yeah. When I run queries like

select foo from table1 where bar in (select bar from table2 where qux = 'qux')

presto does a full table scan of table1. I am looking for ways to avoid that.

[kokosing]

Can you please open separate issue for that and please attach output of the EXPLAIN (distributed) of the query

[findepi]

select foo from table1 where bar in (select bar from table2 where qux = 'qux')

presto does a full table scan of table1. I am looking for ways to avoid that.

@aandis Presto needs a full table scan because the condition bar = ... is not known upfront.

You have the options:

1. you can split your query into 2 queries, i.e evaluate select bar from table2 where qux = 'qux' in your program and pass to second query as a parameter; this will allow any kind of optimizations for table1 filter
2. this use-case will benefit from dynamic filtering Add support for dynamic filtering #7428, but still would not be as performant as the first option

(No need for new separate issue, since this is already covered by #7428 and #8680)

[aandis]

@findepi yeah I've tried option 1 in the past. It has it's own problems because the parameter list may exceed max query length/parameter length presto allows. So you have to split it in batches and run multiple queries which is additional non core application logic.
Additionally, more parameters means more planning time #10700 (comment) with a connector like cassandra.

It's nice to know #7428 will fix this. I'll keep an eye out.