Forum: Ruby on Rails MySQL2/Arel/ActiveRecord and PAD SPACE

Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2013-09-02 19:47
(Received via mailing list)
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 :-)

--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
Aa082c8b00a50928e5860dcd70bf2368?d=identicon&s=25 tamouse m. (tamouse_m)
on 2013-09-02 20:20
(Received via mailing list)
On Sep 2, 2013, at 12:46 PM, Hassan Schroeder
<hassan.schroeder@gmail.com> wrote:

>> 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.
Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2013-09-02 21:36
(Received via mailing list)
On Mon, Sep 2, 2013 at 11:19 AM, Tamara Temple <tamouse.lists@gmail.com>
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@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.