Optimization Hints Are Placed in SQL Output Before Order

[cherez]

Issue

When using optimizer hints, SQL Server expects the ORDER clause to come before the OPTION clause, as shown in the official docs. When combining optimizer_hints and any query that produces an ORDER clause, the ORDER clause instead comes after the OPTION clause, creating a syntax error.

Expected behavior

> User.optimizer_hints("FAST 1").order(:id).to_sql
 => "SELECT [users].* FROM [users] ORDER BY [users].[id] ASC OPTION (FAST 1)" 

> User.optimizer_hints("FAST 1").first
 => #<User ...

Actual behavior

> User.optimizer_hints("FAST 1").order(:id).to_sql
 => "SELECT [users].* FROM [users] OPTION (FAST 1) ORDER BY [users].[id] ASC" 

> User.optimizer_hints("FAST 1").first
TinyTds::Error: Incorrect syntax near the keyword 'ORDER'. (ActiveRecord::StatementInvalid)

How to reproduce

Combine any optimizer hint with an order, first, or last query

> User.optimizer_hints("Fast 1").order(:id).all
TinyTds::Error: Incorrect syntax near the keyword 'ORDER'. (ActiveRecord::StatementInvalid)
> User.optimizer_hints("Label='FindUser'").first
TinyTds::Error: Incorrect syntax near the keyword 'ORDER'. (ActiveRecord::StatementInvalid)

Details

• Rails version: 7.2.1
• SQL Server adapter version: 7.2.1
• TinyTDS version: 2.1.7
• FreeTDS details:

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.3.18
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: no
                             GnuTLS: no
                               MARS: yes