rails-sqlserver/activerecord-sqlserver-adapter

limit() method with subqueries

ramasoftdev opened this issue · 3 comments

ramasoftdev commented

Issue

it's not possible the limit() implementation working on versions 6.x.x

Expected behavior

result.limit(10) should be:

SELECT [D_docs].* FROM ( SELECT * FROM [D_docs] ) [D_docs] OFFSET 0 FETCH NEXT 10 ROWS ONLY

Actual behavior

It seems that limit() doesn't add to the generated subquery which actually is an Model::ActiveRecord_Relation, instead tries an execution which throws the error:

ActiveRecord::StatementInvalid Exception table doesn't exists and returns nil

How to reproduce

class Doc < ApplicationRecord
  has_many :doc_dirs

  has_many :dirs, through: :doc_dirs

  default_scope -> { where(inactive: false, is_version_1: true)}

  scope :doc_collection, -> do
    str_sql = "( SELECT * FROM [D_docs] ) [D_docs]"
    result = Doc.from(str_sql).limit(10)
    doc_collec = from(sql)
    # byebug
    doc_collec
  end
end

On rails console if you add a debug between result doc_collec = from(sql) and doc_collec

running doc_collec = from(sql).to_sql you will get:

*** ActiveRecord::StatementInvalid Exception: Table '( SELECT * FROM [D_docs] ) [D_docs]' doesn't exist

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 get

SELECT [D_docs].* FROM ( SELECT * FROM [D_docs] ) [D_docs] OFFSET 0 FETCH NEXT 10 ROWS ONLY

Details

  • Rails version: 7.1.2
  • SQL Server adapter version: 7.1.0
  • TinyTDS version: 2.1.x
WillemOpperman commented

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
aidanharan commented

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 #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)
  • 👍2