Extra parentheses in view definitions cause an exception in the table name regex

[dhockett-dmp]

Issue

When a view is defined with the entire FROM clause wrapped in parentheses, the table name parsing (ActiveRecord::ConnectionAdapters::SQLServer::SchemaStatements#get_raw_table_name) throws an exception NoMethodError: undefined method '[]' for nil.

Expected behavior

The view is queried and returned without error.

Actual behavior

NoMethodError: undefined method '[]' for nil

How to reproduce

Tested with Ruby 3.3.6.

require "bundler/inline"

case ENV['TEST_VER']
when '6.1'
  gemfile(true) do
    source "https://rubygems.org"
    gem "tiny_tds"
    gem "activerecord", "6.1.7.10"
    gem "activerecord-sqlserver-adapter", "6.1.3.0"
  end
when '7.1'
  gemfile(true) do
    source "https://rubygems.org"
    gem "tiny_tds"
    gem "activerecord", "7.1.5"
    gem "activerecord-sqlserver-adapter", "7.1.8"
  end
when '7.2'
  gemfile(true) do
    source "https://rubygems.org"
    gem "tiny_tds"
    gem "activerecord", "7.2.2"
    gem "activerecord-sqlserver-adapter", "7.2.3"
  end
end

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

ActiveRecord::Base.establish_connection(
  adapter:  "sqlserver",
  timeout:  5000,
  pool:     100,
  encoding: "utf8",
  database: "test_database",
  username: ENV['username'],
  password: ENV['password'],
  host:     ENV['host'],
  port:     1433,
)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  drop_table :test_table_as rescue nil
  drop_table :test_table_bs rescue nil

  create_table :test_table_as, force: true

  create_table :test_table_bs, force: true do |t|
    t.bigint :test_table_a_id
  end

  execute 'DROP VIEW IF EXISTS test_views'

  execute <<~SQL.squish
    CREATE VIEW test_views (
      test_table_a_id,
      test_table_b_id
    ) AS SELECT
      test_table_as.id as test_table_a_id,
      test_table_bs.id as test_table_b_id
    FROM (test_table_as with(nolock)
    LEFT JOIN test_table_bs with(nolock) ON (test_table_as.id = test_table_bs.test_table_a_id))
  SQL
end

class TestTableA < ActiveRecord::Base
  has_many :test_views, foreign_key: :test_table_a_id
  has_many :test_table_bs
end

class TestTableB < ActiveRecord::Base
  belongs_to :test_table_a
end

class TestView < ActiveRecord::Base
  self.primary_key = :test_table_a_id
end

class TestBugTest < Minitest::Test
  def setup
    @object_a = TestTableA.create!
    @object_b = TestTableB.create!(test_table_a: @object_a)
  end

  def test_count
    assert_equal 1, @object_a.test_views.count
  end
end

Note the following monkey patch seems to resolve the issue (though I can't verify that this handles all necessary cases):

module ActiveRecord
  module ConnectionAdapters
    module SQLServer
      module SchemaStatements
        def get_raw_table_name(sql)
          return if sql.blank?

          s = sql.gsub(/^\s*EXEC sp_executesql N'/i, "")

          if s.match?(/^\s*INSERT INTO.*/i)
            s.split(/INSERT INTO/i)[1]
              .split(/OUTPUT INSERTED/i)[0]
              .split(/(DEFAULT)?\s+VALUES/i)[0]
              .match(/\s*([^(]*)/i)[0]
          elsif s.match?(/^\s*UPDATE\s+.*/i)
            s.match(/UPDATE\s+([^\(\s]+)\s*/i)[1]
          # Check for a match before accessing []
          elsif s.match?(/FROM\s+((\[[^\(\]]+\])|[^\(\s]+)\s*/i)
            s.match(/FROM\s+((\[[^\(\]]+\])|[^\(\s]+)\s*/i)[1]
          # Return nil if no match found
          else
            return
          end.strip
        end
      end
    end
  end
end

Details

• Rails version: 7.1.5
• SQL Server adapter version: 7.1.8
• TinyTDS version: 2.1.7
• FreeTDS details:

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

[dhockett-dmp]

Please note this is a regression from previous versions; I updated the test script to use different versions via the TEST_VER environment variable. This works for 6.1 but fails for 7.1 and 7.2.

[aidanharan]

Thanks for the updated script. Didn't realise it was a regression. Issue will be fixed in next 7.1 and 7.2 releases.