search

rails-sqlserver / tiny_tds

TinyTDS - Simple and fast FreeTDS bindings for Ruby using DB-Library.
Other
606 stars 190 forks source link

Issue selecting all when table has a compound primary key #474

Closed AlexJBisping closed 4 years ago

AlexJBisping commented 4 years ago

Environment

Operating System

Running on: Distributor ID: Ubuntu Description: Ubuntu 18.04.3 LTS Release: 18.04 Codename: bionic Linux ip-10-0-6-26 4.15.0-1065-aws #69-Ubuntu SMP Thu Mar 26 02:17:29 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Connecting to: Windows 10 on EC2 running Windows Server 2019 and Sql Server Developer 2019.

FreeTDS Version

1.2.3

Description

Error: TinyTds::Error: Incorrect syntax near the keyword 'ASC'

When a table has a compound primary key and tinytds is selecting all values from the table, it combines the columns of the compound primary key and throws the above error with the below backtrace.

Here is the compound primary key definition: ALTER TABLE [dbo].[Rollup_Records] ADD CONSTRAINT [PK_Rollup_Records] PRIMARY KEY CLUSTERED ( [candidate_id] ASC, [rollup_name] ASC )

I tracked the issue down to /activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:416 in the raw_select method. The log lambda returns:

=> #<ActiveRecord::Result:0x0000555a231ea0a0 @column_types={}, @columns=["name"], @hash_rows=nil, @rows=[["candidate_id"], ["rollup_name"]]>

When it should return those two column names individually. Is the activerecord-sqlserver-adapter repo a better place for this?

Backtrace: "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:450:in each'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:450:inhandle_to_names_and_values_dblib'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:441:in handle_to_names_and_values'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:420:in_raw_select'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:415:in block in raw_select'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:722:inblock (2 levels) in log'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activesupport-6.0.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in block (2 levels) in synchronize'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activesupport-6.0.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:inhandle_interrupt'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activesupport-6.0.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in block in synchronize'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activesupport-6.0.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:inhandle_interrupt'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activesupport-6.0.3.2/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in synchronize'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:721:inblock in log'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activesupport-6.0.3.2/lib/active_support/notifications/instrumenter.rb:24:in instrument'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:712:inlog'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:415:in raw_select'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:304:insp_executesql'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-sqlserver-adapter-6.0.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:35:in exec_query'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:493:inselect_prepared'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:68:in select_all'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:107:inselect_all'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/querying.rb:46:in find_by_sql'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:821:inblock in exec_queries'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:836:in block in skip_query_cache_if_necessary'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:79:inuncached'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/query_cache.rb:21:in uncached'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/delegation.rb:107:inpublic_send'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/delegation.rb:107:in block in method_missing'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:407:inblock in scoping'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:784:in _scoping'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:407:inscoping'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/delegation.rb:107:in method_missing'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:835:inskip_query_cache_if_necessary'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:808:in exec_queries'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:626:inload'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation.rb:250:in records'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/batches.rb:224:inblock in in_batches'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/batches.rb:222:in loop'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/batches.rb:222:inin_batches'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/batches.rb:135:in find_in_batches'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/relation/batches.rb:69:infind_each'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/activerecord-6.0.3.2/lib/active_record/querying.rb:21:in find_each'", "/home/ubuntu/loxo/production/lib/tasks/imports/import_common.rb:275:inreencode_klass!'", "/home/ubuntu/loxo/production/lib/tasks/imports/import_common.rb:268:in block in reencode_klasses!'", "/home/ubuntu/loxo/production/lib/tasks/imports/import_common.rb:267:ineach'", "/home/ubuntu/loxo/production/lib/tasks/imports/import_common.rb:267:in reencode_klasses!'", "/home/ubuntu/loxo/production/lib/tasks/imports/pcrecruiter_6403/pcrecruiter.rake:17:inblock (2 levels) in <top (required)>'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:281:in block in execute'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:281:ineach'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:281:in execute'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:219:inblock in invoke_with_call_chain'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:199:in synchronize'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:199:ininvoke_with_call_chain'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/task.rb:188:in invoke'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:160:ininvoke_task'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:116:in block (2 levels) in top_level'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:116:ineach'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:116:in block in top_level'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:125:inrun_with_threads'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:110:in top_level'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:83:inblock in run'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:186:in standard_exception_handling'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/lib/rake/application.rb:80:inrun'", "/home/ubuntu/.rbenv/versions/2.7.0/lib/ruby/gems/2.7.0/gems/rake-13.0.1/exe/rake:27:in <top (required)>'", "/home/ubuntu/.rbenv/versions/2.7.0/bin/rake:23:inload'", "/home/ubuntu/.rbenv/versions/2.7.0/bin/rake:23:in `

'

AlexJBisping commented 4 years ago

I was able to work around this by using Class.all.each instead of Class.find_each but I would rather use find_each to avoid OOM on larger tables.

AlexJBisping commented 4 years ago

Hi! Although I did find a workaround, it isn't ideal. Is there a more fitting place for the above issue? Can I provide any more details?

Thank you for the incredible library, I really appreciate everyone that has contributed!