Closed ramasoftdev closed 5 months ago
We have the same problem.
I have narrowed it down to this commit: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/pull/988/files
Taking a deeper look it seems to not be able to determine the TableName
here: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/main/lib/active_record/connection_adapters/sqlserver/utils.rb#L86
sql = `( SELECT * FROM [D_docs] ) [D_docs]`
name = SQLServer::Utils.extract_identifiers(sql)
WORKAROUND:
module Extensions
def column_definitions(table_name)
super
rescue ::ActiveRecord::StatementInvalid
[]
end
end
::ActiveRecord::ConnectionAdapters::SQLServer::SchemaStatements.prepend Extensions
In SQL Server if you do not provide ordering in an SQL select query then the results are non-deterministic. So if you run SELECT * FROM [Books] OFFSET 0 FETCH NEXT 10 ROWS ONLY
twice in a row you could get different results.
To make the results deterministic the adapter orders the results by the primary key if ordering is not specified. So the above query might become SELECT * FROM [Books] ORDER BY [books].[id] ASC OFFSET 0 FETCH NEXT 10 ROWS ONLY
, which is deterministic.
The issue above is that when the FROM clause is specified using raw SQL:
str_sql = "( SELECT * FROM [D_docs] ) [D_docs]"
result = Doc.from(str_sql)
Then the adapter needs to parse the raw SQL to try to find the table name so that the primary key of the table can be determined. This is very difficult and outside the scope of the adapter. In previous versions of the adapter it was ignored when the primary key for ordering could not be found.
The fix for your issue will be to use a release that contains https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/pull/1151 and to include an ordering in your query. So the above code will become something like:
str_sql = "( SELECT * FROM [D_docs] ) [D_docs]"
result = Doc.from(str_sql).order(:id)
Fixed in release 7.1.2.
Issue
it's not possible the
limit()
implementation working on versions 6.x.xExpected behavior
result.limit(10)
should be:Actual behavior
It seems that
limit()
doesn't add to the generated subquery which actually is anModel::ActiveRecord_Relation
, instead tries an execution which throws the error:How to reproduce
On rails console if you add a debug between result
doc_collec = from(sql)
anddoc_collec
running
doc_collec = from(sql).to_sql
you will get:it should be noted that
Doc.from(str_sql).to_sql
is giving:SELECT [D_docs].* FROM ( SELECT * FROM [D_docs] ) [D_docs]
a correct and without errors query and older rails versions (6.1.7) and SQL Server adapter version 6.x.x successfully generates the query we're trying to getDetails
7.1.2
7.1.0
2.1.x