I''ve noticed a rather insidious 3.0.4 change to the way that chained where methods that reference the same attribute are handled. Does anyone know where I can find more information about where/when this change came in, and the justification for it? I haven''t been able to find a changelog mentioning this modification. Essentially, when you chain two where methods together that reference the same attribute, the SQL query generated uses an OR on that attribute, rather than AND. Example: Rails 3.0.3 State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` ''TX'') AND (`states`.`abbreviation` = ''NE'') Rails 3.0.4 State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` ''TX'' OR `states`.`abbreviation` = ''NE'') -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 14 February 2011 21:03, Jeremy <jmcnevin-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I''ve noticed a rather insidious 3.0.4 change to the way that chained > where methods that reference the same attribute are handled. Does > anyone know where I can find more information about where/when this > change came in, and the justification for it? I haven''t been able to > find a changelog mentioning this modification. > > Essentially, when you chain two where methods together that reference > the same attribute, the SQL query generated uses an OR on that > attribute, rather than AND. > > Example: > > Rails 3.0.3 > State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') > SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` > ''TX'') AND (`states`.`abbreviation` = ''NE'') > > Rails 3.0.4 > State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') > SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` > ''TX'' OR `states`.`abbreviation` = ''NE'')I can confirm I see the same issue on 3.0.4 (not tried on 3.0.3). It looks like a bug to me. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Mark Kremer
2011-Feb-16 05:42 UTC
Re: 3.0.4 change to chained where methods. Documentation?
It may seem a bit odd with the where method chained twice like that, but the query with the OR (Rails 3.0.4) will probably get you the result you want whereas the query with the AND (Rails 3.0.3) will return 0 records at all times (because the same attribute can''t have two values). On 15-2-2011 10:07, Colin Law wrote:> On 14 February 2011 21:03, Jeremy<jmcnevin-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> I''ve noticed a rather insidious 3.0.4 change to the way that chained >> where methods that reference the same attribute are handled. Does >> anyone know where I can find more information about where/when this >> change came in, and the justification for it? I haven''t been able to >> find a changelog mentioning this modification. >> >> Essentially, when you chain two where methods together that reference >> the same attribute, the SQL query generated uses an OR on that >> attribute, rather than AND. >> >> Example: >> >> Rails 3.0.3 >> State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') >> SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` >> ''TX'') AND (`states`.`abbreviation` = ''NE'') >> >> Rails 3.0.4 >> State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') >> SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` >> ''TX'' OR `states`.`abbreviation` = ''NE'') > I can confirm I see the same issue on 3.0.4 (not tried on 3.0.3). It > looks like a bug to me. > > Colin >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 16 February 2011 05:42, Mark Kremer <mark-35I9eEnyf3ce/i+EH++dVqxOck334EZe@public.gmane.org> wrote:> It may seem a bit odd with the where method chained twice like that, but the > query with the OR (Rails 3.0.4) will probably get you the result you want > whereas the query with the AND (Rails 3.0.3) will return 0 records at all > times (because the same attribute can''t have two values).I disagree that OR is the result one would want if one were to code this. I agree it is apparently a useless query and obviously one would not code it exactly like this. If the sql for this is incorrectly generated however, it is likely that a more subtle, and useful, query may also be encoded wrongly. The principle of chained where is that the first one is performed, then the next is performed on the results of the first. At least that is how I understand it. Consider: State.where(:abbreviation => ''TX'').where("abbreviation = ''NE''") This (correctly in my view) uses AND in the query. As 3.0.4 stands at the moment the above yields a different result to State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') which seems incorrect to me. I will post on rails-core to see what the response is there. Colin> > On 15-2-2011 10:07, Colin Law wrote: >> >> On 14 February 2011 21:03, Jeremy<jmcnevin-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >>> >>> I''ve noticed a rather insidious 3.0.4 change to the way that chained >>> where methods that reference the same attribute are handled. Does >>> anyone know where I can find more information about where/when this >>> change came in, and the justification for it? I haven''t been able to >>> find a changelog mentioning this modification. >>> >>> Essentially, when you chain two where methods together that reference >>> the same attribute, the SQL query generated uses an OR on that >>> attribute, rather than AND. >>> >>> Example: >>> >>> Rails 3.0.3 >>> State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') >>> SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` >>> ''TX'') AND (`states`.`abbreviation` = ''NE'') >>> >>> Rails 3.0.4 >>> State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') >>> SQL: SELECT `states`.* FROM `states` WHERE (`states`.`abbreviation` >>> ''TX'' OR `states`.`abbreviation` = ''NE'') >> >> I can confirm I see the same issue on 3.0.4 (not tried on 3.0.3). It >> looks like a bug to me. >> >> Colin >> > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 16 February 2011 09:44, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> On 16 February 2011 05:42, Mark Kremer <mark-35I9eEnyf3ce/i+EH++dVqxOck334EZe@public.gmane.org> wrote: >> It may seem a bit odd with the where method chained twice like that, but the >> query with the OR (Rails 3.0.4) will probably get you the result you want >> whereas the query with the AND (Rails 3.0.3) will return 0 records at all >> times (because the same attribute can''t have two values). > > I disagree that OR is the result one would want if one were to code > this. I agree it is apparently a useless query and obviously one > would not code it exactly like this. If the sql for this is > incorrectly generated however, it is likely that a more subtle, and > useful, query may also be encoded wrongly. The principle of chained > where is that the first one is performed, then the next is performed > on the results of the first. At least that is how I understand it. > > Consider: > State.where(:abbreviation => ''TX'').where("abbreviation = ''NE''") > This (correctly in my view) uses AND in the query. > As 3.0.4 stands at the moment the above yields a different result to > State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') > which seems incorrect to me. > > I will post on rails-core to see what the response is there.I posted to rails-core and apparently it is by design rather than a bug. The question seems to have stirred up a bit of a furore however, see http://groups.google.com/group/rubyonrails-core/browse_thread/thread/407f746fd1de6636 Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Mark Kremer
2011-Feb-16 21:17 UTC
Re: 3.0.4 change to chained where methods. Documentation?
Thanks for posting that link :) After reading a bit more I tend to agree with you that chaining where multiple times should result in an AND, and not in an OR (even though the example of doing a .where on the same attribute twice is a little silly) On 16-2-2011 12:43, Colin Law wrote:> On 16 February 2011 09:44, Colin Law<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> On 16 February 2011 05:42, Mark Kremer<mark-35I9eEnyf3ce/i+EH++dVqxOck334EZe@public.gmane.org> wrote: >>> It may seem a bit odd with the where method chained twice like that, but the >>> query with the OR (Rails 3.0.4) will probably get you the result you want >>> whereas the query with the AND (Rails 3.0.3) will return 0 records at all >>> times (because the same attribute can''t have two values). >> I disagree that OR is the result one would want if one were to code >> this. I agree it is apparently a useless query and obviously one >> would not code it exactly like this. If the sql for this is >> incorrectly generated however, it is likely that a more subtle, and >> useful, query may also be encoded wrongly. The principle of chained >> where is that the first one is performed, then the next is performed >> on the results of the first. At least that is how I understand it. >> >> Consider: >> State.where(:abbreviation => ''TX'').where("abbreviation = ''NE''") >> This (correctly in my view) uses AND in the query. >> As 3.0.4 stands at the moment the above yields a different result to >> State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') >> which seems incorrect to me. >> >> I will post on rails-core to see what the response is there. > I posted to rails-core and apparently it is by design rather than a > bug. The question seems to have stirred up a bit of a furore however, > see http://groups.google.com/group/rubyonrails-core/browse_thread/thread/407f746fd1de6636 > > Colin >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 16 February 2011 11:43, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> On 16 February 2011 09:44, Colin Law <clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> On 16 February 2011 05:42, Mark Kremer <mark-35I9eEnyf3ce/i+EH++dVqxOck334EZe@public.gmane.org> wrote: >>> It may seem a bit odd with the where method chained twice like that, but the >>> query with the OR (Rails 3.0.4) will probably get you the result you want >>> whereas the query with the AND (Rails 3.0.3) will return 0 records at all >>> times (because the same attribute can''t have two values). >> >> I disagree that OR is the result one would want if one were to code >> this. I agree it is apparently a useless query and obviously one >> would not code it exactly like this. If the sql for this is >> incorrectly generated however, it is likely that a more subtle, and >> useful, query may also be encoded wrongly. The principle of chained >> where is that the first one is performed, then the next is performed >> on the results of the first. At least that is how I understand it. >> >> Consider: >> State.where(:abbreviation => ''TX'').where("abbreviation = ''NE''") >> This (correctly in my view) uses AND in the query. >> As 3.0.4 stands at the moment the above yields a different result to >> State.where(:abbreviation => ''TX'').where(:abbreviation => ''NE'') >> which seems incorrect to me. >> >> I will post on rails-core to see what the response is there. > > I posted to rails-core and apparently it is by design rather than a > bug. The question seems to have stirred up a bit of a furore however, > see http://groups.google.com/group/rubyonrails-core/browse_thread/thread/407f746fd1de6636I see this has now been fixed in the Rails 3.0.5, though I have not tried it myself yet. Thanks to Jeremy for finding it in the first place. Colin -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.