TIL: The SQL-92 spec dictates that CHAR/VARCHAR comparisons be made after padding each string with spaces to the same length. That means that using a standard MySQL collation, the two strings ''foo'' and ''foo '' are equal. (ref: https://mariadb.com/kb/en/22-sql-collations-collation/) The problem (which just bit me after all these years) is essentially:> query_name = ''foo'' > Thing.create(:name => ''foo '') > thing = Thing.where(:name => query_name).first # returns created thing > thing.name == query_name # => false ## ouchThat result seems to violate the POLS to me, but I''d like to hear any opinions about if and/or where this behavior should be fixed before I start working on a patch to the MySQL2 gem :-) -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org http://about.me/hassanschroeder twitter: @hassan -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACmC4yAULGhUDK1NS9BS4Kja%2B7RM6j7bUtH0rVC_7hUbBLCgiQ%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
On Sep 2, 2013, at 12:46 PM, Hassan Schroeder <hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> TIL: The SQL-92 spec dictates that CHAR/VARCHAR comparisons > be made after padding each string with spaces to the same length. > > That means that using a standard MySQL collation, the two strings > ''foo'' and ''foo '' are equal. > > (ref: https://mariadb.com/kb/en/22-sql-collations-collation/) > > The problem (which just bit me after all these years) is essentially: > >> query_name = ''foo'' >> Thing.create(:name => ''foo '') >> thing = Thing.where(:name => query_name).first # returns created thing >> thing.name == query_name # => false ## ouch > > That result seems to violate the POLS to me, but I''d like to hear any > opinions about if and/or where this behavior should be fixed before I > start working on a patch to the MySQL2 gem :-)Given that is how the SQL specification works, the MySQL2 adapter is working correctly. While I would consider the above extra space a surprise, certainly, I would consider it *more* of a surprise if the adapter returned anything *but* exactly what the dbms fed it. Going down the path of patching the driver to give something else is a slippery slope. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/536A1F01-4FDA-4B7F-9941-76049A994EC6%40gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
On Mon, Sep 2, 2013 at 11:19 AM, Tamara Temple <tamouse.lists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:>> That result seems to violate the POLS to me, but I''d like to hear any >> opinions about if and/or where this behavior should be fixed before I >> start working on a patch to the MySQL2 gem :-) > > Given that is how the SQL specification works, the MySQL2 adapter is working correctly. While I would consider the above extra space a surprise, certainly, I would consider it *more* of a surprise if the adapter returned anything *but* exactly what the dbms fed it.1) It''s the gem developer''s decision to use `=` without specifying a BINARY comparison, which would *not* return equal, or LIKE without wildcards which would also not return equal. So it''s not a cut-and-dried matter of "working correctly". 2) It breaksActiveRecord DB-agnosticism to have the same data return different results, regardless of who''s "right" about adhering to which standards. The role of an ORM should be to isolate the app from the vagaries of storage engines. If I receive an object based on a requested attribute, and then turn around and find out that that object''s attribute IS NOT EQUAL to the one I requested -- I don''t see how that''s anything but broken. However, while I can''t actually envision a use case where the trailing space was significant, I could see a case for making the gem behavior configurable. Does that make a patch more palatable? :-) -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org http://about.me/hassanschroeder twitter: @hassan -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACmC4yA_AY%3DkF5anuB7JMvYrdeG5nQ2jdht9Y2AatK2ezxppqA%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.