Invalid SQL (duplicate columns in select list) generated by include? on not-yet-loaded has_many :through #944

Closed
lighthouse-import opened this Issue May 16, 2011 · 0 comments

Projects

None yet

1 participant

@lighthouse-import

Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6602
Created by ystael - 2011-03-21 19:36:12 UTC

When include? is called on a has_many :through association proxy which has not yet been loaded, the resulting SQL refers to a column twice in the select list, which is invalid at least on MS SQL Server. Example:

class Foo < ActiveRecord::Base
  has_many :foos_bars
  has_many :bars, :through => :foos_bars
end

class Bar < ActiveRecord::Base
  has_many :foos_bars
  has_many :foos, :through => :foos_bars
end

class FoosBar < ActiveRecord::Base
  belongs_to :foo
  belongs_to :bar
end

Now create one Foo, one Bar, and one FoosBar connecting them, then restart the console and try:

> foo = Foo.first
> bar = Bar.first
> foo.bars.include?(bar)

The following exception results:

ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: The column 'id' was specified multiple times for 't'.: SELECT t.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY bars.id) AS _row_num, bars.*, bars.[id] FROM bars INNER JOIN foos_bars ON bars.id = foos_bars.bar_id WHERE bars.[id] = 1 AND ((foos_bars.foo_id = 1))) AS t WHERE t._row_num BETWEEN 1 AND 1

The issue does not arise if before calling foo.bars.include?(bar) one first causes the association to be loaded, for example by asking for foo.bars.length .

On MySQL we get similar SQL generated, but on MySQL it is allowed to name a column twice in the select list:

SELECT `bars`.*, `bars`.`id` FROM `bars` INNER JOIN `foos_bars` ON `bars`.id = `foos_bars`.bar_id WHERE `bars`.`id` = 1 AND ((`foos_bars`.foo_id = 1)) LIMIT 1

Environment: Rails 3.0.5, JRuby 1.6.0, AR-JDBC 1.1.1, Windows 7, MS SQL Server 2008. Note that in this exact environment this issue is masked by another bug in AR-JDBC, issue http://kenai.com/jira/browse/ACTIVERECORD_JDBC-156 . My fork https://github.com/ystael/activerecord-jdbc-adapter contains a tentative fix for that issue which unmasks this one.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment