SystemStackError (Infinite Loop) when using .with

[garrettblehm]

Hello! I've found a bug with the .with CTE functionality.

When a relation has a CTE added, and that relation attempts to use .with to add another CTE of itself, it enters an infinite loop that results in a SystemStackError.

Reproduction steps:

# Note: The models don't matter here. I used some that are present in the spec/support/models.rb file for easy reproduction.

# Initial Relation
initial_relation = User.all

# Add CTE to User Relation
cte_relation = Group.all 
initial_relation_with_cte = initial_relation.with('first_cte' => cte_relation)

# When a relation with a CTE adds itself as another CTE a SystemStackError is raised
initial_relation_with_self_cte = initial_relation_with_cte.with('self_cte' => initial_relation_with_cte)
initial_relation_with_self_cte.to_sql # .arel also triggers the loop

I've spent a couple of days looking into this. Here is the infinite loop.

1. build_arel method is called by rails, and since the relation has a CTE, build_with is called.
2. The build_with method calls generate_grouping which calls to_arel_sql.
3. to_arel_sql calls .to_sql in the ActiveRecord::Relation branch.
4. .to_sql of an ActiveRecord::Relation calls build_arel again in step 1.

Notes:

• I've found that it is only an issue when a relation already has a CTE and you try to add that same relation as a CTE.
• The construct of the relation doesn't seem to matter. I was able to recreate the bug with complex relations as well as simple ones (like the reproduction steps).

Workaround:

If I .dup the relation and the associated .cte, the relation behaves as expected.

# Initial Relation
initial_relation = User.all

# Add CTE to User Relation
cte_relation = Group.all 
initial_relation_with_cte = initial_relation.with('first_cte' => cte_relation)

# duplicate the relation and the cte
self_cte = initial_relation_with_cte.dup
self_cte.cte = initial_relation_with_cte.cte.dup

# use duplicated relation
initial_relation_with_self_cte = initial_relation_with_cte.with('self_cte' => self_cte)
initial_relation_with_self_cte.to_sql # works as expected

[nbarthel]

I've seen the same issue complicated by including activerecord-cte. Glad I found your bug report!

[rickychilcott]

This can be closed, FYI!