includes(:relation) causing a join when .offset() and .limit() are used

[sphogan]

Basically, the issue I'm having is that calling Person.includes(:emails).limit(10).offset(10) is causing a LEFT OUTER JOIN rather than using Rails' usual method of a second IN query to grab related results.

I've created a small project (https://github.com/sphogan/offsettest) to demonstrate the issue (in case you want a concrete example).

Person.includes(:emails)
Person Load (17.8ms)  EXEC sp_executesql N'SELECT [people].* FROM [people]'
Email Load (3.9ms)  EXEC sp_executesql N'SELECT [emails].* FROM [emails] WHERE [emails].[person_id] IN (N''1'', N''2'', N''3'', N''4'', N''5'', N''6'', N''7'', N''8'', N''9'', N''10'', N''11'', N''12'', N''13'', N''14'', N''15'', N''16'', N''17'', N''18'', N''19'', N''20'', N''21'')'

Perfect! It executes two queries as expected.

Person.limit(10).offset(10)
Person Load (2.7ms)  EXEC sp_executesql N'SELECT TOP (10) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [people].[id] ASC) AS [__rn], [people].* FROM [people] ) AS [__rnt] WHERE [__rnt].[__rn] > (10) ORDER BY [__rnt].[__rn] ASC'

Awesome!

Person.limit(10).offset(10).includes(:emails)
Person Load (3.1ms)  EXEC sp_executesql N'SELECT TOP (10) [__rnt].id FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [people].[id] ASC) AS [__rn], [people].id FROM [people] LEFT OUTER JOIN [emails] ON [emails].[person_id] = [people].[id] GROUP BY [people].id ) AS [__rnt] WHERE [__rnt].[__rn] > (10) ORDER BY [__rnt].[__rn] ASC'
SQL (5.6ms)  EXEC sp_executesql N'SELECT [people].[id] AS t0_r0, [people].[name] AS t0_r1, [people].[created_at] AS t0_r2, [people].[updated_at] AS t0_r3, [emails].[id] AS t1_r0, [emails].[person_id] AS t1_r1, [emails].[address] AS t1_r2, [emails].[created_at] AS t1_r3, [emails].[updated_at] AS t1_r4 FROM [people] LEFT OUTER JOIN [emails] ON [emails].[person_id] = [people].[id] WHERE [people].[id] IN (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)'

This is where the problem is. The adapter works as I assume it would work for Person.includes(:emails) as well as for Person.limit(10).offset(10). However, when the two are combined, it decides to do a LEFT OUTER JOIN to get the emails and I'm not sure why. It isn't using fewer statements since it's still using two SQL statements to load the people and their email addresses, they're just more complex SQL statements. The interesting thing is that calling .to_sql shows me the behavior I expect:

Person.limit(10).offset(10).includes(:emails).to_sql
 => "SELECT TOP (10) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [people].[id] ASC) AS [__rn], [people].* FROM [people] ) AS [__rnt] WHERE [__rnt].[__rn] > (10) ORDER BY [__rnt].[__rn] ASC"

The statement provided by .to_sql runs perfectly against my database.

Just as a comparison (to make sure I wasn't off-base with my expected behavior), I tested it on a MySQL database:

Person.limit(10).offset(10).includes(:emails)
Person Load (0.2ms)  SELECT `people`.* FROM `people` LIMIT 10 OFFSET 10
Email Load (26.8ms)  SELECT `emails`.* FROM `emails` WHERE `emails`.`person_id` IN (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)

Looking at visit_Arel_Nodes_SelectStatementWithOutOffset, the statement it returns is:

SELECT TOP (10) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [people].[id] ASC) AS [__rn], [people].* FROM [people] ) AS [__rnt] WHERE [__rnt].[__rn] > (10) ORDER BY [__rnt].[__rn] ASC

So, at that point, it's the same SQL that .to_sql is showing and that I'm expecting. I'm not sure if this is getting altered by something in Arel::TreeManager or I'm just missing something.

[metaskills]

It is not the adapter that decides to do a LEFT OUTER JOIN, it is ActiveRecord. That is in their eager load association code. That join is what populates the IN statement. I believe other adapter's do not log that for some reason and you will never see it using to_sql.

[sphogan]

Thanks for pointing me in the right direction. Any idea why ActiveRecord would be adding it as a join for SQL Server, but not for MySQL (as well as splitting it into two statements)?

[metaskills]

I assert that it is. If it is not, I have found that it also depends on how many records it finds too. So depending on the data, it will do different things, which could also explain it.

[sphogan]

I haven't had a lot of time to look at this, but it does seem to be getting added by ActiveRecord. find_with_associations is doing the join in active_record/relation/finder_methods and I'm going to try and figure out why it's treating SQLServer differently from MySQL.

[metaskills]

Closing this issue. Note, that does not mean conversation can not happen. Just means as of now, this is not an issue to work on or block future releases.

[sphogan]

So, I found what the problem is (and, incidentally, there's even a comment that the Oracle adapter has the same limit/offset issue). In ActiveRecord, there's a method tables_in_string(string) that has an override for the Oracle adapter's use of raw_sql_, but not for the SQL Server adapter's use of __rnt (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation.rb).

Going into https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/arel/visitors/sqlserver.rb and doing a simple find/replace of __rnt with raw_sql_ changes the behavior to be what I expect it to be:

Person Load (0.7ms)  EXEC sp_executesql N'SELECT TOP (10) [raw_sql_].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [people].[id] ASC) AS [__rn], [people].* FROM [people] ) AS [raw_sql_] WHERE [raw_sql_].[__rn] > (10) ORDER BY [raw_sql_].[__rn] ASC'
Email Load (2.8ms)  EXEC sp_executesql N'SELECT [emails].* FROM [emails] WHERE [emails].[person_id] IN (N''11'', N''12'', N''13'', N''14'', N''15'', N''16'', N''17'', N''18'', N''19'', N''20'')'

It even looks like, before the exception got included into ActiveRecord, the Oracle adapter would patch tables_in_string to except raw_sql_ (https://github.com/rsim/oracle-enhanced/blob/master/lib/active_record/connection_adapters/oracle_enhanced_activerecord_patches.rb).

I made the change in https://github.com/sphogan/activerecord-sqlserver-adapter to use the same name as the Oracle adapter and it's working nicely for me.

[metaskills]

@sphogan Really impressive investigative work! I'll be fixing this in a commit and new release very soon.

[mikesnare]

I am experiencing what I think is this same issue even after upgrading to activerecord-sqlserver-adapter 3.2.10. I'm running rails version 3.2.6 and tiny_tds 0.5.1.

Running a rails query using the exact same syntax as that in the offset_and_limit_test_sqlserver.rb file:

Model.limit(N).offset(N).include(:relation).all

results in SQL code that does a join with the relation.

Any thoughts would be appreciated.

Update: I upgraded rails to 3.2.9 and this still happens.

[sphogan]

I can confirm that I'm seeing the same behavior as @mikesnare with 3.2.10 of the activerecord-sqlserver-adapter (Rails 3.2.9 and tiny_tds 0.5.1).

I've actually been running off of my fork at https://github.com/sphogan/activerecord-sqlserver-adapter. The only thing that should be different in my fork is this: https://github.com/sphogan/activerecord-sqlserver-adapter/commit/6f6a71708cfab297686135ec549bdb8f97a67fcb. Basically, the raw_sql_ name is used by the Oracle adapter for limit/offset and is part of ActiveRecord (https://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation.rb).

The patch https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/1edea97a41c610c44b775807a3dbb7f2884b550f/lib/active_record/connection_adapters/sqlserver/core_ext/relation.rb should similarly exclude the __rnt that this adapter uses since it just subtracts the _rnt just like relation.rb subtracts raw_sql.

Test out my fork and see if it works for you. I probably won't be able to look at this much until after finals.

[mjthompsgb]

I am having the same issue with 3.2.10 of the activerecord-sqlserver-adapter. I am trying to do a limit and offset with includes and it isn't working. Is this still a known issue?

[sphogan]

Hi! I can't spend a lot of time tracing the issue right now, but might have time over the summer. In the meantime, try using https://github.com/sphogan/activerecord-sqlserver-adapter and see if that solves your problem.

[mjthompsgb]

Thanks for getting back to me. I tried that fork and still had the same issue.