upsert_all raises NoMethodError when the table name is a single character

[Alt70155]

Hi there.

We hit this while upgrading an app to Rails 8, and I wanted to report it since the failure
is not obvious from the error. I used AI assistance to narrow down the cause and to check the
patch for regressions, but everything below was verified by running the script and the
adapter's test suite against a local SQL Server.

Issue

upsert_all (and insert_all when it takes the MERGE path) raises NoMethodError when the
target table name is a single character. The same failure happens for temporary tables,
non-ASCII table names, names containing $, and three part (cross database) names.

Expected behavior

upsert_all inserts the rows.

Actual behavior

ActiveRecord::StatementInvalid: NoMethodError: undefined method `[]' for nil
    .../sqlserver/schema_statements.rb:723:in `get_raw_table_name'
    ...
    activerecord-8.0.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:177:in `exec_insert_all'
    activerecord-8.0.5.1/lib/active_record/insert_all.rb:54:in `execute'
    activerecord-8.0.5.1/lib/active_record/relation.rb:921:in `upsert_all'

How to reproduce

Script (also fails on 8-0-stable and 8-1-stable):

# frozen_string_literal: true

require "bundler/inline"

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

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

ActiveRecord::Base.establish_connection(
  adapter: "sqlserver",
  timeout: 5000,
  pool: 5,
  encoding: "utf8",
  database: ENV.fetch("DB_DATABASE", "activerecord_unittest"),
  username: ENV.fetch("DB_USERNAME", "rails"),
  password: ENV.fetch("DB_PASSWORD", ""),
  host: ENV.fetch("DB_HOST", "localhost"),
  port: ENV.fetch("DB_PORT", "1433").to_i
)

ActiveRecord::Base.logger = Logger.new($stdout, level: :error)

ActiveRecord::Schema.define do
  create_table :x, force: true do |t|
    t.string :name
  end

  create_table :products, force: true do |t|
    t.string :name
  end
end

class SingleCharacterName < ActiveRecord::Base
  self.table_name = "x"
end

class Product < ActiveRecord::Base
end

class BugTest < Minitest::Test
  # Passes.
  def test_upsert_all_with_a_normal_table_name
    Product.upsert_all([{id: 1, name: "a"}])

    assert_equal 1, Product.count
  end

  # Fails with: NoMethodError: undefined method `[]' for nil
  def test_upsert_all_with_a_single_character_table_name
    SingleCharacterName.upsert_all([{id: 1, name: "a"}])

    assert_equal 1, SingleCharacterName.count
  end
end

Output:

2 runs, 1 assertions, 0 failures, 1 errors, 0 skips

The only difference between the two tests is the length of the table name.

Cause

The MERGE branch of get_raw_table_name has two identifier slots, and both are +, so they
each need to match at least one character:

activerecord-sqlserver-adapter/lib/active_record/connection_adapters/sqlserver/schema_statements.rb

Line 723 in cce2e38

s.match(/^\s*MERGE\s+INTO\s+(\[?[a-z0-9_ -]+\]?\.?\[?[a-z0-9_ -]+\]?)\s+(AS|WITH|USING)/i)[1]

s.match(/^\s*MERGE\s+INTO\s+(\[?[a-z0-9_ -]+\]?\.?\[?[a-z0-9_ -]+\]?)\s+(AS|WITH|USING)/i)[1]

[products] matches because the engine can split it across the two slots (produc + ts).
[x] cannot be split, so the match fails and nil[1] raises. The same applies to any name whose
characters are not in [a-z0-9_ -], because the slot cannot consume them either.

Since the character class also allows a space and the second slot is reachable without a
preceding dot, an aliased target is swallowed into the captured name as well:

# MERGE INTO [products] AS target USING ...
get_raw_table_name(sql)               # => "[products] AS target"
get_table_name(sql)                   # => "products] AS targe"
query_requires_identity_insert?(sql)  # => raises "Table '[products] AS target' doesn't exist"

This is not reachable from build_sql_for_merge_insert (it always emits
WITH (UPDLOCK, HOLDLOCK) AS target, and WITH terminates the match), but it is reachable from
hand written MERGE passed to execute.

Affected cases

Checked against the released 8.0.11 gem:

Table name	Current	With the fix below
[x] (single character)	NoMethodError	ok
[#tmp] (temporary table)	NoMethodError	ok
[商品] (non-ASCII)	NoMethodError	ok
[my$tab]	NoMethodError	ok
[otherdb].[dbo].[dogs] (three part)	NoMethodError	ok
MERGE INTO [products] AS target USING ... (hand written)	wrong name (see above)	ok
MERGE INTO dbo .products ... (space before the dot)	-	unchanged

The aliased case does not raise inside get_raw_table_name itself; it returns a wrong name that
then fails downstream, as shown in the Cause section above.

INSERT, UPDATE and FROM already handle three part names; only the MERGE branch is limited
to two.

Fix

I have a patch ready and can open a PR if you would like it:

MERGE_TARGET_IDENTIFIER = /(?:\[[^\]]+\]|[a-z0-9_-]+)/i
MERGE_TARGET_TABLE_NAME = /\A\s*MERGE\s+INTO\s+(#{MERGE_TARGET_IDENTIFIER}(?:\s*\.#{MERGE_TARGET_IDENTIFIER}){0,2})\s+(?:AS|WITH|USING)/i

Spaces are allowed only inside brackets, the schema qualifier requires a dot, and up to three
parts are accepted (database.schema.table; a four part remote name is not a valid MERGE
target). Running the script above against this patch gives 2 runs, 2 assertions, 0 failures, 0 errors, and rake test ONLY_SQLSERVER=1 shows no change in failures or errors compared to
main (376 -> 384 runs, same 1 failure / 30 errors, which are pre-existing on main).

The MERGE line is byte identical on main, 8-0-stable and 8-1-stable, so the same patch
applies to all three.

Details

• Rails version: 8.0.5.1

• SQL Server adapter version: 8.0.11

• TinyTDS version: 3.4.0

• FreeTDS details:

  Version: freetds v1.4.26
  freetds.conf directory: /opt/homebrew/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
  

• SQL Server: Microsoft SQL Server 2019 (RTM-CU32-GDR) 15.0.4455.2 (X64) on Linux (Ubuntu 20.04.6 LTS)

[aidanharan]

Please open a PR with the fix and some tests. Thanks