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 (...) ...

Similarly, they support an explicit hint that a CTE should not be materialized:
WITH foo AS NOT MATERIALIZED (...) ...

Detail

This Pull Request adds Arel::Nodes::Cte, which represents a CTE query and contains an alias, a relation, and a materialization flag. The latter is either true to request materialization, false to request no materialization, or nil which omits any materialization hint. The node can be used like so:

    posts = Arel::Table.new(:posts)
    comments = Arel::Table.new(:comments)

    good_comments_query = comments.project(Arel.star).where(comments[:rating].gt(7))
    cte = Arel::Nodes::Cte.new(:good_comments, good_comments_query, materialized: true)

    query = posts.
      project(Arel.star).
      with(cte).
      where(posts[:id].in(cte.to_table.project(:post_id))).

    puts query.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")

As and TableAlias nodes continue to be supported as arguments to SelectManager#with.

Additional information

This is an alternative to #47951, following @matthewd's suggestion of adding a Cte node that contains both an alias and a relation, instead of adding a node just to represent the addition of the MATERIALIZED keyword. This keeps the syntax tree shallower and allows us easily to support NOT MATERIALIZED hints without needing to resort to yet another keyword-specific node type or deeper nesting of nodes.

Checklist

Before submitting the PR make sure the following are checked:

• This Pull Request is related to one change. Changes that are unrelated should be opened in separate PRs.
• Commit message has a detailed description of what changed and why. If this PR fixes a related issue include it in the commit message. Ex: [Fix #issue-number]
• Tests are added or updated if you fix a bug or add a feature.
• CHANGELOG files are updated for the changed libraries if there is a behavior change or additional feature. Minor bug fixes and documentation changes should not be included.