Join in queries fail with `Not implemented error`

[k-anshul]

CREATE TABLE hello AS FROM VALUES ('world') v(s);
CREATE TABLE yellow AS FROM VALUES ('world') v(s);
select * from yellow join hello on yellow.s = hello.s; // works on server
select * from remote.yellow join remote.hello on yellow.s = hello.s; // fails in client

Error:

Not implemented Error:
Multiple streaming scans or streaming scans + CTAS / insert in the same query are not currently supported

[Mytherin]

Thanks for reporting! This currently happens because the join gets executed client-side, #161 will make this work by instead pushing the join to be executed server side. That will fix this query in quack.

Having multiple streaming scans work is a separate issue that we'll also pick up, but that won't be necessary anymore for queries that only target remote tables.

[k-anshul]

Thanks 👍

[wakeless]

I spent some time building out an implementation of it here: wakeless#1 thought I'd post it here rather than just putting up an uninvited PR as a) I'm no expert and the AI has heavily helped and b) wasn't really sure about the flush-to-buffers approach etc.

I'm gonna try getting this up and running our environment, as I'm really keen to see if I can move the compute inter-regional on our data center and I think duckdb & quack are a great approach.

[Mytherin]

I think keeping multiple results is a good idea (and one of the reasons why the result_uuid is there) so the core approach looks fine to me. The main challenge there is when to close the results. Relying on the client to send close requests is not reliable, and can lead to potentially lots of results being leaked if the client doesn't send the proper messages or if the messages don't arrive.

My idea for closing results was to associate every result with a given "result batch id", and then allowing multiple results per result batch id. If a new batch id is used all previous result sets that belong to other batch ids are automatically dropped. On the client side, we would then associate a batch either with a transaction id or with a query id. This would cause a new query client-side to automatically drop the results server-side which mirrors the current behavior.