sqlserver adapter 4.2: TinyTds::Error: Column "spree_products.id" is invalid in the ORDER BY clause because ...

[Dan Sadaka]

Gentlemen:

Yesterday I was preparing to do a new eCommerce site with the latest Spree 3.0, which requires rails 4.2, which requires sqlserver gem 4.2.  After getting an OFFSET error, I discovered I needed to upgrade SQL to 2012.  Subsequently, one more fix to a Spree migration and it looked like I was off and running.  

However, when I went to display the home page of my new spree site (with the sample data), I got this log entry:

ActionView::Template::Error (TinyTds::Error: Column "spree_products.id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.: EXEC sp_executesql N'SELECT  MAX([spree_products].[updated_at]) FROM [spree_products] INNER JOIN [spree_variants] ON [spree_variants].[product_id] = [spree_products].[id] AND [spree_variants].[is_master] = 1 AND [spree_variants].[deleted_at] IS NULL INNER JOIN [spree_prices] ON [spree_prices].[variant_id] = [spree_variants].[id] AND [spree_prices].[deleted_at] IS NULL WHERE [spree_products].[deleted_at] IS NULL AND ([spree_products].deleted_at IS NULL or [spree_products].deleted_at >= ''02-16-2015 00:04:40.945'') AND ([spree_products].available_on <= ''02-16-2015 00:04:40.953'') AND (spree_prices.amount IS NOT NULL) AND [spree_prices].[currency] = N''USD''  ORDER BY [spree_products].[id] ASC OFFSET 0 ROWS FETCH NEXT 12 ROWS ONLY'):

Tracing reveals the offending line of code is at:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/bundler/gems/spree-88fc133e85a0/core/app/helpers/spree/products_helper.rb:56

max_updated_at = (@products.maximum(:updated_at) || Date.today).to_s(:number)

Where @products is of type ActiveRecord_Relation

Digging deeper into the maximum (ActiveRecord) method:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/relation/calculations.rb:74

def maximum(column_name, options = {})
  # TODO: Remove options argument as soon we remove support to
  # activerecord-deprecated_finders.
  calculate(:maximum, column_name, options)
end

and then the calculate method:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/relation/calculations.rb:120

def calculate(operation, column_name, options = {})
  # TODO: Remove options argument as soon we remove support to
  # activerecord-deprecated_finders.
  if column_name.is_a?(Symbol) && attribute_alias?(column_name)
    column_name = attribute_alias(column_name)
  end

  if has_include?(column_name)
    construct_relation_for_association_calculations.calculate(operation, column_name, options)
  else
    perform_calculation(operation, column_name, options)
  end
end

and then to perform_calculation:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/relation/calculations.rb:202

and then execute_simple_calculation:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/relation/calculations.rb:240

and skip down a few to:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/relation/calculations.rb:264

result = @klass.connection.select_all(query_builder, nil, bind_values)

and, finally, in select_all:

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/query_cache.rb:67

sql = to_sql(arel, binds)

where to_sql is at

/Users/dsadaka/.rvm/gems/ruby-2.1.5@spree3/gems/activerecord-4.2.0/lib/active_record/connection_adapters/abstract/database_statements.rb:11

# Converts an arel AST to SQL
def to_sql(arel, binds = [])
  if arel.respond_to?(:ast)
    collected = visitor.accept(arel.ast, collector)
    collected.compile(binds.dup, self)
  else
    arel
  end
end

which returns the offending SQL statement.

I'm not sure if the correction to this will happen in the ActiveRecord code or the sqlserver adapter, but I know this error does not occur when run using SQLLite.  

I'm under a deadline but really don't want to get back to Spree 2.4 and lose the performance gains you've achieved in 4.2.

Please advise.

Thanks,

Dan

[Ken Collins]

Hey Dan,

Thanks for all the detail, but it may be a little too much. Can you open an issue on the adapter repo that shows the minimal code to reproduce the issue? I suspect you would need to describe more of the existing scopes on the @products relation. If so, I can help you solve this there.

 - Ken

--
You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rails-sqlserver-a...@googlegroups.com.
To post to this group, send email to rails-sqlse...@googlegroups.com.
Visit this group at http://groups.google.com/group/rails-sqlserver-adapter.
For more options, visit https://groups.google.com/d/optout.

[Dan Sadaka]

Ken,

I posted issue #391 after digging down and--I believe--unearthing the cause of this.

Take a look and let me know...

Thanks,

Dan

To unsubscribe from this group and stop receiving emails from it, send an email to rails-sqlserver-adapter+unsub...@googlegroups.com.