Return of the N'' query issue on non unicode varchar columns

[gnarfle]

Issue

Recently we began the process of updating a rails 5.1 application to rails 5.2 and found that each time we tried, we would see horrible database performance and 7+ second queries slowing things way down. Eventually we traced this back to an issue we had in rails 4.x but had seemed to be fixed in 5.x, which is the adapter using N'' quoting on varchar (not nvarchar) columns. It seems that in this case we're seeing this happen when we add a where clause on a joined relation and not when we query a single table.

Expected behavior

We should use N'' quoting only on nvarchar columns and '' on varchar columns

Actual behavior

We are seeing N'' quoting used on varchar columns, leading to slow performance on large tables.

How to reproduce

I've managed to narrow this down to a case where it's going through two joins...

With the following three tables:

CREATE TABLE [dbo].[test1] (
        [id] int IDENTITY(1,1) NOT NULL,
        [name] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

CREATE TABLE [dbo].[test2] (
        [id] int IDENTITY(1,1) NOT NULL,
        [test1_id] int NOT NULL,
        [name] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

CREATE TABLE [dbo].[test3] (
        [id] int IDENTITY(1,1) NOT NULL,
        [test2_id] int NOT NULL,
        [name] varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

I then made the following 3 models for these tables:

class Test1 < ActiveRecord::Base
  self.table_name = "test1"
  has_many :test2
end

class Test2 < ActiveRecord::Base
  self.table_name = "test2"
  belongs_to :test1
  has_many :test3
end

class Test3 < ActiveRecord::Base
  self.table_name = "test3"
  belongs_to :test2
end

With those models you can now run the following 3 queries and see that they all work ok, until you add in two joins:

[1] pry(main)> Test3.where(name: 'asdf').to_sql
=> "SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'"
[2] pry(main)> Test2.joins(:test3).where(test3: {name: 'asdf'}).to_sql
=> "SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'"
[3] pry(main)> Test1.joins(test2: :test3).where("test3" => { name: 'asdf' }).to_sql
=> "SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = N'asdf'"

Details

• Rails version: 5.2.4.3
• SQL Server adapter version: 5.2.1
• TinyTDS version: 1.3.0
• FreeTDS details: 1.1.15

[TinyTds][v1.3.0][tsql]: /usr/local/bin/tsql
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.1.15
                            freetds.conf directory: /usr/local/etc
                            MS db-lib source compatibility: no
                            Sybase binary compatibility: yes
                            Thread safety: yes
                            iconv library: yes
                            TDS version: 7.3
                            iODBC: no
                            unixodbc: yes
                            SSPI "trusted" logins: no
                            Kerberos: yes
                            OpenSSL: yes
                            GnuTLS: no
                            MARS: yes

[wpolicarpo]

Thanks for the detailed report. I'll try to look into it this week.

[cyberbruce]

I'm having the same problem, but with the 5.0.7 version of the gem.

[cyberbruce]

I did notice, that IF I used symbol syntax when constructing my query in rails it was correct. (no N'')

If I had a query that used a sql string, I had the incorrect meta type of N''

What are your thoughts?

[gnarfle]

Any update? We're still seeing this issue. In addition to the example I posted I'm also finding it on WHERE IN(field: [array]) queries too, oddly just on the first item in the array.

[aidanharan]

This issue isn't present in version 6.0 or 6.1 of the SQL Server adapter. It does happen in v5.2.1 as detailed in the original description.

SQL Server Adapter: 5.2.1 Rails: 5.2.6

SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'
SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = N'asdf'

SQL Server Adapter: 6.0.2 Rails: 6.0.4

SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'
SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'

SQL Server Adapter: 6.1.0.0 Rails: 6.1.4

SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'
SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'

[aidanharan]

Closing this issue as it affects version 5.2 of the adapter which is no longer supported.