When using an ActiveRecord migration to create a stored procedure, where the procedure involves creating a table if it does not exist and inserting something into it, the adapter interprets the create procedure statement as an insert statement and raises an exception ActiveRecord::StatementInvalid: Table '<table name>' doesn't exist.
The query_requires_identity_insert? method at lib/active_record/connection_adapters/sqlserver/database_statements.rb previously returned false for this database statement but it now raises an error from identity_columns(raw_table_name).
Expected behavior
The table isn't expected to exist at the time the stored procedure is created, the stored procedure should be created regardless of whether or not the table exists.
Actual behavior
The migration raises an exception ActiveRecord::StatementInvalid: Table '<table name>' doesn't exist.
How to reproduce
I've set up a repository stored_procedure_migration_example demonstrating the example, the basic idea is to create a migration, which creates a stored procedure, which creates a table if it doesn't exist and then inserts into it. Running the migration succeeds in v6 of the adapter but fails in v7. Note that the example repo uses Rails 7 on master but also has a rails6 branch demonstrating that the migration previously succeeded.
Details
Rails version: 7.0.8
SQL Server adapter version: 7.0.3.0
TinyTDS version: 2.1.5
FreeTDS details:
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
Issue
When using an ActiveRecord migration to create a stored procedure, where the procedure involves creating a table if it does not exist and inserting something into it, the adapter interprets the create procedure statement as an insert statement and raises an exception
ActiveRecord::StatementInvalid: Table '<table name>' doesn't exist
.The
query_requires_identity_insert?
method atlib/active_record/connection_adapters/sqlserver/database_statements.rb
previously returnedfalse
for this database statement but it now raises an error fromidentity_columns(raw_table_name)
.Expected behavior
The table isn't expected to exist at the time the stored procedure is created, the stored procedure should be created regardless of whether or not the table exists.
Actual behavior
The migration raises an exception
ActiveRecord::StatementInvalid: Table '<table name>' doesn't exist
.How to reproduce
I've set up a repository stored_procedure_migration_example demonstrating the example, the basic idea is to create a migration, which creates a stored procedure, which creates a table if it doesn't exist and then inserts into it. Running the migration succeeds in v6 of the adapter but fails in v7. Note that the example repo uses Rails 7 on
master
but also has arails6
branch demonstrating that the migration previously succeeded.Details
7.0.8
7.0.3.0
2.1.5
FreeTDS details: