Add support in Arel for materialized subqueries in CTEs#47951
Add support in Arel for materialized subqueries in CTEs#4795197jaz wants to merge 1 commit intorails:mainfrom
Conversation
7d0caec to
2e52c16
Compare
Postgres and SQLite both support a non-standard extension to the
CTE syntax to indicate that a CTE subquery should be materialized,
i.e., not folded into the main query but evaluated separately.
This can be useful in cases where the query planner would
otherwise make poor decisions.
The syntax, in both databases, is:
`WITH foo AS MATERIALIZED (...) ...`
This PR adds support by introducing a new unary Materialized node
that can wrap a subquery like so:
```ruby
posts = Arel::Table.new(:posts)
comments = Arel::Table.new(:comments)
good_comments = Arel::Table.new(:good_comments)
subquery = comments.project(Arel.star).where(comments[:rating].gt(7))
materialized_subquery = Arel::Nodes::Materialized.new(subquery)
posts.
project(Arel.star).
with(Arel::Nodes::As.new(good_comments, materialized_subquery)).
where(posts[:id].in(good_comments.project(:post_id))).
to_sql
# "WITH \"good_comments\" AS MATERIALIZED (SELECT * FROM \"comments\" WHERE \"comments\".\"rating\" > 7) SELECT * FROM \"posts\" WHERE \"posts\".\"id\" IN (SELECT post_id FROM \"good_comments\")"
```
2e52c16 to
b572c64
Compare
|
Anyone willing to review this? |
|
Looks good to me and would indeed be useful! 👍 |
|
Thanks @benedikt |
|
Hello! If I understand it well, this adds only to Arel private API and in theory it should not be used outside of the Arel codebase. Are there any plans to expose this on ActiveRecord public API side as well? |
|
@simi That's right, this PR is arel-only. I figure this is a first step towards exposing the functionality in AR. |
|
I could definitely use CTE materialization in my current application, which dynamically constructs deeply nested CTEs on very large data tables. So consider this my vote for merging. |
|
Arel has a bit of a mixture of nodes that represent semantics and nodes that directly correspond to keywords. The node proposed in this PR is the latter, but I'd like to explore the idea of shifting more to prefer the former: keyword-shaped nodes force deeper node trees, and feel like they miss out on the theoretical translatability of the more "original" nodes. (If MySQL were to introduce this feature with a different spelling -- or indeed, if we were to try to implement Oracle's I don't know whether this is a good or bad idea, but I'm wondering about instead introducing a That particular spelling would also leave open the option of This is all very much thinking-out-loud, not Canonical Core Opinion, though... wdyt? |
|
@matthewd I agree that this would be a nicer approach, both because it requires less nesting of nodes and because it rules out more nonsensical syntactic constructions. A couple of questions/comments:
[Edit] |
|
Closing in favor of #48261 |
Motivation / Background
Postgres and SQLite both support a non-standard extension to the CTE syntax to indicate that a CTE subquery should be materialized, i.e., not folded into the main query but evaluated separately. This can be useful in cases where the query planner would otherwise make poor decisions.
The syntax, in both databases, is:
WITH foo AS MATERIALIZED (...) ...Detail
This PR adds support by introducing a new unary
Materializednode that can wrap a subquery like so:MySQL doesn't support this SQL extension. I've taken the approach of simply omitting the
MATERIALIZEDkeyword when generating SQL for MySQL.Checklist
Before submitting the PR make sure the following are checked:
[Fix #issue-number]