This project is archived and is in readonly mode.

#394 ✓resolved
Antonio Cangiano

[PATCH] Fixed non-standard SQL generated by preloading has_and_belongs_to_many associations

Reported by Antonio Cangiano | June 11th, 2008 @ 08:42 PM | in 2.1.1

Preloading has_and_belongs_to_many associations generates non-standard SQL which breaks standard-compliant databases like DB2 and Oracle.

Example:

class Project < ActiveRecord::Base

has_and_belongs_to_many :developers

end

class Developer < ActiveRecord::Base

has_and_belongs_to_many :projects

end

p Project.find(:all, :include => :developers)

The previous code generates queries along the lines of:

SELECT developers.*, t0.project_id as _parent_record_id FROM developers INNER JOIN developers_projects as t0 ON developers.id = t0.developer_id WHERE (t0.project_id IN (1,2))

An SQL identifier cannot begin with an underscore, and this is true for all three versions of the standard (92, 99 and 2003). You can verify the non-conformity of the generated SQL through this validator: http://developer.mimer.com/valid...

In the attached patch, I renamed _parent_record_id with the_parent_record_id.

Please note that no further tests are required given that the test cases in cases/associations_test.rb already cover this specific issue.

Comments and changes to this ticket

  • Jeremy Kemper

    Jeremy Kemper June 12th, 2008 @ 01:28 AM

    • Milestone set to “2.1.1”
    • State changed from “new” to “open”
    • Assigned user set to “Jeremy Kemper”
  • Repository

    Repository June 12th, 2008 @ 01:36 AM

    • State changed from “open” to “resolved”

    (from [4689496b525526a776004c4986d98b0ce62309d5]) Fixed non-standard SQL generated by preloading has_and_belongs_to_many association. [#394 state:resolved]

    http://github.com/rails/rails/co...

  • Don McClean

    Don McClean July 27th, 2008 @ 12:30 AM

    • Tag set to “activerecord, bug, has_and_belongs_to_many, patch, standard-sql”

    I manually patched my source with your fix and still got an

    error. I am running Oracle 9.2. The second portion of the

    above example 'as t0' was still causing an error. Removing the

    'as' fixed the problem.

    Regards,

    Don McClean

  • Antonio Cangiano

    Antonio Cangiano July 28th, 2008 @ 01:18 AM

    Don, this must be an Oracle peculiarity. Using the "as t0" is standard SQL. It's OK to omit it though, if it helps Oracle, because omitting it respects the standard too

  • Don McClean

    Don McClean July 30th, 2008 @ 03:05 PM

    Antonio,

    Since removing the 'as' still would be standard SQL, it

    would be appreciated if you could remove it.

    Thanks and Regards,

    Don McClean

  • Brent Miller

    Brent Miller November 20th, 2008 @ 05:02 PM

    Since this ticket seems to be closed, the "as" issue is being addressed in ticket #1405

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

Attachments

Tags

Referenced by

Pages