This project is archived and is in readonly mode.

#2724 ✓stale
Jónas Tryggvi

Duplicate joins when combining scopes and includes with dots in parameters

Reported by Jónas Tryggvi | May 27th, 2009 @ 12:02 AM | in 3.x

When we combine multiple scopes that join on the same table, use :include to eager load from that table and use a parameter to the scopes that has a dot in it, we get duplicate joins on the same table.

This can be seen in the attached project, by doing:
rake db:migrate
go to: http://localhost:3000/as?s=gaman.saman

The :joins in the model a.rb in the project attached with this ticket should have joins like this;

  :joins => "LEFT OUTER JOIN `bs` ON `bs`.id = `as`.b_id",

The join strings are identical to those generated by rails in :include, but we still get the join twice in the sql.

Also, if you try setting

  :joins => :b

you also get an error, but only when there is a dot in the parameter (like the link with ?s=gaman.saman demonstrates), but not when the parameter doesn't have a dot in it

Comments and changes to this ticket

  • Pete Yandell

    Pete Yandell June 22nd, 2009 @ 03:36 AM

    I'm seeing a similar error. I'm trying to come up with a minimal test to reproduce.

  • Pete Yandell

    Pete Yandell June 22nd, 2009 @ 04:03 AM

    A minimal test is attached, based on a production app in which I'm seeing this issue.

    The guts of it is this:

    # This succeeds.
    publisher.subscribers.tagged_with("a").all(:include => :tags)
    # This generates invalid SQL.
    publisher.subscribers.tagged_with("a.").all(:include => :tags)

    The tagged_with named scope looks like this:

    lambda {|tag| { :joins => :tags, :conditions => [" = ?", tag ] } }

    So, if you include a dot anywhere in the tag you're looking for, ActiveRecord starts spitting out bad SQL!

    The error is:

    SQLite3::SQLException: ambiguous column name: SELECT "subscribers"."id" AS t0_r0, "subscribers"."publisher_id" AS t0_r1, "tags"."id" AS t1_r0, "tags"."name" AS t1_r1 FROM "subscribers"  LEFT OUTER JOIN "taggings" ON ("subscribers"."id" = "taggings"."subscriber_id")  LEFT OUTER JOIN "tags" ON ("tags"."id" = "taggings"."tag_id")   INNER JOIN "taggings" ON ("subscribers"."id" = "taggings"."subscriber_id")  INNER JOIN "tags" ON ("tags"."id" = "taggings"."tag_id")  WHERE ((( = 'a.') AND ("subscribers".publisher_id = 1)) AND ("subscribers".publisher_id = 1))  (ActiveRecord::StatementInvalid)

    Same problem occurs on both MySQL and SQLite.

    If I just do Subscriber.tagged_with (i.e. leave out the parent relationship), the problem doesn't occur.

  • Marcelo Serpa

    Marcelo Serpa April 29th, 2010 @ 07:54 PM

    I have a similar problem, but what triggered it was an actual search string that included a dot.

    The problem lies on lib/active_record/associations.rb#tables_in_string, the following regexp:


    Just searches for anything that has a pattenr like string.string and returns the part before the dot. So, even if you have a search string of "%search." it will think the string "search" is actually a table name and try to join it.

    The solution to my specific problem would be to make the regexp more specific and to search ignore this pattern when preceded by % and followed by %, but since Ruby 1.8.7 doesn't support regexp lookahead or lookbehind, not sure what to do without impacting the API too much.

    I'll try to come up with something and get a patch up.


  • Marcelo Serpa

    Marcelo Serpa April 29th, 2010 @ 07:57 PM

    Another solution, at the application level, is to just sanitize dots from the string. In my case this would work, but not sure it would work for you guys. I think that we are not supposed to include dots in strings we provide to find's parameters. I can live with that, for now.

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • Rohit Arondekar

    Rohit Arondekar October 9th, 2010 @ 04:07 AM

    • Importance changed from “” to “”

    Any updates here?

  • David Bittencourt

    David Bittencourt November 25th, 2010 @ 10:24 PM

    This is still an issue in 3.0.3, causing things like:

    Alpha.where(:name => 'foobar').includes(:bravo)

    To generate normal queries like this:

    SELECT "alphas".* FROM "alphas" WHERE ("alphas"."name" = 'foobar')
    SELECT "bravos".* FROM "bravos" WHERE ("bravos"."id" IN (...))

    While something like:

    Alpha.where(:name => '').includes(:bravo)

    To generate this join query:

    SELECT "alphas"."id" AS t0_r0, "alphas"."name" AS t0_r1, "alphas"."created_at" AS t0_r2, 
    "alphas"."updated_at" AS t0_r3, "alphas"."bravo_id" AS t0_r4, "bravos"."id" AS t1_r0, 
    "bravos"."some" AS t1_r1, "bravos"."other" AS t1_r2, "bravos"."created_at" AS t1_r3, 
    "bravos"."updated_at" AS t1_r4 FROM "alphas" LEFT OUTER JOIN "bravos" ON 
    "bravos"."id" = "alphas"."bravo_id" WHERE ("alphas"."name" = '')

    When you have more complex queries it can go bad very easily (performance-wise and exceptions) just because the user included a dot in their query.

    An example of a crashing issue is if the Bravo model is in a separate database. You can handle it fine in the first case as it does separate queries, but the second one will raise an SQL exception because the table "bravos" doesn't exist in Alpha model's database. Very bad that the user can break your application by just adding a dot in a string.

    Here are other open tickets about this issue:

    Sorry, but I don't know enough about Rails internals to propose a patch, just wanted to give an update as I found this issue today. I had to rewrite my code to not use includes in some queries to avoid this issue.

  • Rohit Arondekar

    Rohit Arondekar November 28th, 2010 @ 12:23 AM

    • Assigned user set to “Neeraj Singh”
  • rails

    rails March 1st, 2011 @ 12:00 AM

    • Tag changed from :include, :joins, activecord, named_scope to activecord, include, joins, named_scope
    • State changed from “new” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • rails

    rails March 1st, 2011 @ 12:00 AM

    • State changed from “open” to “stale”

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=""></a>