This project is archived and is in readonly mode.

#6602 new
ystael

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

Reported by ystael | March 21st, 2011 @ 07:36 PM

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.

No comments found

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Pages