The SQL Server adapter makes select queries deterministic by ordering by the primary key if an ordering is not provided.
If the FROM clause is specified using:
Book.from("(SELECT [books].* FROM [books]) [books]")
Then this would require the adapter to parse the raw SQL string (SELECT [books].* FROM [books]) [books] to find the table being queried so that its primary key could then be determined. Parsing raw SQL is very difficult and outside the requirements of the adapter. I don't think the Rails project parses raw SQL either because of the difficulty.
To fix the above query the ordering should be given so that the adapter does not have to try to find the primary key:
Book.from("(SELECT [books].* FROM [books]) [books]").order(:id)
This does require a small fix to the adapter so that the primary key is only looked for if an ordering has not already been specified.
The SQL Server adapter makes select queries deterministic by ordering by the primary key if an ordering is not provided.
If the FROM clause is specified using:
Then this would require the adapter to parse the raw SQL string
(SELECT [books].* FROM [books]) [books]to find the table being queried so that its primary key could then be determined. Parsing raw SQL is very difficult and outside the requirements of the adapter. I don't think the Rails project parses raw SQL either because of the difficulty.To fix the above query the ordering should be given so that the adapter does not have to try to find the primary key:
This does require a small fix to the adapter so that the primary key is only looked for if an ordering has not already been specified.
This PR fixes https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/1148