Issue when using joins with distinct

[samu]

We have a basic user to roles mapping with a roles_users table in between.

User.joins(:roles_users).distinct.where(id: 1).first

would cause the error

ActiveRecord::JDBCError: The multi-part identifier "users.id" could not be bound

while

User.joins(:roles_users).where(id: 1).first

works fine.

We are using an MSSQL Database, therefore we use the activerecord-jdbcmssql-adapter (newest version). Here are the two sql statements that are being generated:

cruby:

EXEC sp_executesql N'SELECT DISTINCT TOP (1) [users].* 

FROM [users] INNER JOIN [roles_users] ON [roles_users].[user_id] = [users].[id] AND [roles_users].[deleted_at] IS NULL WHERE [users].[first_name] = N''abc'

' ORDER BY [users].[id] ASC'

jruby:

SELECT t.* FROM ( SELECT ROW_NUMBER() OVER(ORDER BY [users].[id] ASC) AS _row_num, t.* FROM (SELECT  DISTINCT [users].* 

FROM [users] INNER JOIN [roles_users] ON [roles_users].[user_id] = [users].[id] AND [roles_users].[deleted_at] IS NULL WHERE [users].[first_name] = N'abc'

) AS t ) AS t WHERE t._row_num BETWEEN 1 AND 1

We have tried it with different jruby versions: jruby-1.7.9 and jruby-1.7.13 on Rails 4.1.1 and Rails 4.1.6.

you can support MS-SQL fixes at BountySource

[kares]

Thanks Sam, I unfortunately do not have the time to look into MS-SQL but I'm happy to help out when someone does ... please consider looking into it on your own. We're low on (SQLServer) maintainers, there are several issues open.

[samu]

Thanks @kares for the reply. I'd like to look into this myself, however i don't really know where to start. Could you give me some hints maybe? I need a starting point in the code, as i'm very unfamiliar with it as of now.

[kares]

sure, here you go:

• consider seting up testing (to make sure you changes do not introduce any regressions) rake test_mssql
  https://github.com/jruby/activerecord-jdbc-adapter/blob/master/RUNNING_TESTS.md
  you probably want to set some env variables depending on your test DB https://github.com/jruby/activerecord-jdbc-adapter/blob/master/test/db/mssql_config.rb
• the adapter code is at https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arjdbc/mssql/adapter.rb
  there's also some helpers under the folder that are used from the AREL part as well
• finally the AREL visitor is at https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arel/visitors/sql_server.rb

[samu]

@kares I investigated on this and i think the cause of the problem is on this line:

activerecord-jdbc-adapter/lib/arjdbc/mssql/limit_helpers.rb

Line 42 in 8ce048c

order = order.gsub(/([a-z0-9_])+\./, 't.')

The problem is that the order by sql looks like this

ORDER BY [users].[id] ASC

and the line of code i referenced is supposed to change it to this

ORDER BY t.[id] ASC

However, the REGEX used does not contain [ and ]. Changing the original line

order = order.gsub(/([a-z0-9_])+\./, 't.')

to this

order = order.gsub(/([\[\]a-z0-9_])+\./, 't.')

solves the problem.

I'm not sure if this is a sufficient solution as i'm having a few troubles running the test suite (several tests are failing due to problems that are unrelated to this change). Do you want me to create a pull request anyways?

[kares]

@samu sure, if there are no new failures introduced and you can cover your case with a test I'll merge it.

[samu]

@kares do i understand this correctly that the tests for mssql are only being run locally and not in CI?

[kares]

@samu yes, you do not worry about travis-ci failures unless you touch some shared (non-specific) parts ...
I'll take your word for not seeing any new failures before and after, until someone else says smt else :) !