search

rails-sqlserver / activerecord-sqlserver-adapter

SQL Server Adapter For Rails
MIT License
972 stars 558 forks source link

Error on GROUP BY clause while using SQLServer, which was working 100% fine with Postgres before #953

Closed Rajanpandey closed 2 years ago

Rajanpandey commented 2 years ago

Issue

I have a Rails repo that was working 100% fine with Postgres before. I migrated the database to SQL Server, then all the group queries (with ORM framework) has started giving this error: TinyTds::Error: Column "<column-name>" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

Expected behavior

Since there is no change in any query, and the database is migrated 100% properly, it should work just fine with SQL Server. There is no hard-coded query. ORM framework ActiveRecord is running the queries.

Actual behavior

For the ORM query: TABLE1.joins(:Table2).group('TABLE1.id')

How to reproduce

Write a group query with active record that works in Postgres. Switch the database to SQL Server, and it starts throwing this error.

Details

aidanharan commented 2 years ago

This issue is caused by SQL Server rather than by the adapter. Postgres allows you to group by a column that is not present listed in the SELECT clause. SQL Server does not.

If you take your SQL and run it manually against SQL Server using Azure Data Studio (https://azure.microsoft.com/en-us/services/developer-tools/data-studio/) or any other database tool you will see the same error. If you edit the SQL to include the column in the ORDER BY clause then the query should work:

SELECT vaccine_schedules.id, vaccine_schedules.name FROM...

To get the query to run successfully using the SQL Server adapter try something like the following. It should include the vaccine_schedules.name column in the SELECT so it can be used by the ORDER BY clause.

VaccineSchedule.select(:id, :name).joins(schedule_age_ranges: :vaccine_doses).includes(...
aidanharan commented 2 years ago

Removing the GROUP clause would change the results from your query and so the code that relies on those results would probably break.

I tried adding select and order to the ORM query, but it's still not working

Was the generated SQL and error message still the same?