rails-sqlserver/activerecord-sqlserver-adapter

Includes adds DISTINCT and AS alias_0 to query

juice opened this issue · 3 comments

juice commented

Issue

With Rails 7.2 and above a query with includes gives us a Incorrect syntax near '`'.

3.3.5 :040 > TestTable.includes(:test_table_groups).where(id: 111000).first
  SQL (2.1ms)  EXEC sp_executesql N'SELECT DISTINCT `TestTable`.`id` AS alias_0, [TestTable].[id] FROM [TestTable] LEFT OUTER JOIN [TestTableGroup] ON [TestTableGroup].[id] = [TestTable].[id] WHERE [TestTable].[id] = @0 ORDER BY [TestTable].[id] ASC OFFSET 0 ROWS FETCH NEXT @1 ROWS ONLY', N'@0 int, @1 int', @0 = 111000, @1 = 1  [["id", 111000], ["LIMIT", 1]]
  (test):40:in `<main>': TinyTds::Error: Incorrect syntax near '`'. (ActiveRecord::StatementInvalid)
  /Users/test/.rvm/gems/ruby-3.3.5/bundler/gems/activerecord-sqlserver-adapter-8bf060f7ad4b/lib/active_record/connection_adapters/sqlserver/database_statements.rb:433:in `each': Incorrect syntax near '`'. (TinyTds::Error)
    from /Users/test/.rvm/gems/ruby-3.3.5/bundler/gems/activerecord-sqlserver-adapter-8bf060f7ad4b/lib/active_record/connection_adapters/sqlserver/database_statements.rb:433:in `handle_to_names_and_values'
    from /Users/test/.rvm/gems/ruby-3.3.5/bundler/gems/activerecord-sqlserver-adapter-8bf060f7ad4b/lib/active_record/connection_adapters/sqlserver/database_statements.rb:56:in `internal_exec_sql_query'

Expected behavior

The query in Rails 7.1 looked like this:
EXEC sp_executesql N'SELECT [TestTable].[id],...

Actual behavior

The query adds a distinct AS alias after select

How to reproduce

TestTable.includes(:test_table_groups).where(id: 111000).first

Details

  • Rails version: 7.2-8.0rc2
  • SQL Server adapter version: 7.2.1n
  • TinyTDS version: 2.1.7
juice commented

@aidanharan First of all. Thank you for the work you are doing here.

It took me a minute to understand our haunted multi db setup and getting it to work in an inline script version.

I think the MySQL and the capitalization of the table name combination is the problem here, but that is just a guess.

How to reproduce

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "tiny_tds"
  gem "rails", "8.0.0" 
  gem "activerecord-sqlserver-adapter"
  gem "mysql2"
end

require "active_record"
require "minitest/autorun"
require "logger"

config = {
  development: {
   primary: {
     adapter:  "mysql2",
     timeout:  5000,
     pool:     100,
     encoding: "utf8",
     database: "test_database",
     username: "user",
     password: "password",
     host:     "localhost"
   },

   mssql: {
     adapter:  "sqlserver",
     timeout:  5000,
     pool:     100,
     encoding: "utf8",
     database: "test_database",
     username: "user",
     password: "password",
     host:     "localhost"
   }
  },
}

ActiveRecord::Base.configurations = config

ActiveRecord::Base.establish_connection(config[:development][:primary])
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  drop_table :internal_bug_tests rescue nil

  create_table :internal_bug_tests, force: true do |t|
    t.bigint :external_id
  end

  ActiveRecord::Base.establish_connection(config[:development][:mssql])

  drop_table "Bug_tests" rescue nil
  drop_table "Bug_test_tests" rescue nil

  create_table "Bug_tests", force: true do |t|
    t.bigint :external_id
  end

  create_table "Bug_test_tests", force: true do |t|
    t.bigint :external_id
    t.bigint :bug_test_id
  end

  ActiveRecord::Base.establish_connection(config[:development][:primary])
end

class PrimaryAdapter < ActiveRecord::Base
  self.abstract_class = true
end

class MssqlAdapter < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: { writing: :mssql, reading: :mssql }
end

class InternalBugTest < PrimaryAdapter
  self.table_name = 'internal_bug_tests'
end

class BugTest < MssqlAdapter
  self.table_name = 'Bug_tests'

  has_many :bug_test_tests, foreign_key: :bug_test_id, dependent: :destroy
end

class BugTestTest < MssqlAdapter
  self.table_name = 'Bug_test_tests'

  belongs_to :bug_test, foreign_key: :external_id, optional: true
end

class TestBugTest < Minitest::Test
  def setup
    @bug_test2 = BugTest.create!(external_id: 2_032_070_100_001)
    @bug_test3 = BugTestTest.create!(external_id: 2_032_070_100_002, bug_test_id: 2_032_070_100_001)
  end

  def test_count
    bug_test = BugTest.includes(:bug_test_tests).where(external_id: 2_032_070_100_001).first

    assert_equal 1, bug_test.id
  end
end
aidanharan commented

Will be fixed in the next 8.0, 7.2 and 7.1 releases.

  • ❤️2