String Equality in SQL Databases ...

Here's a thing I came across recently, when I wanted to check the correct function of a certain field validation within a Django app. That's what the code for that field looks like:

...
from myproject.models import User
...
# a field that checks whether a given user exists in another table
class CrossUserCheckCharField(forms.Field):
  def validate(self, value):
    super(CrossUserCheckCharField, self).validate(value)
    try:
      user = User.objects.using('mytable').get(id=value)
    except User.DoesNotExist:
      raise ValidationError(u'User %s does not exist.' % value)
...

For my unit tests (run by python manage.py test) I use a lightweight SQLite DB, from the development environment on (e.g. run by python manage.py runserver) MySQL is used. That's where the difference came up - with SQLite the test failed with a raised exception, with MySQL it passed - weird! It boils down to that MySQL thinks that two strings are equal, if they are only different in trailing whitespaces. That's not what I expect, when I check for equality of two strings! BTW, leading whitespaces are matched correctly. MySQL also offers a BINARY operator, which compares byte by byte making trailing whitespaces significant again.
So, I tried to approve this behaviour in a playground:

$ mysql -u someuser -p -D playground
...
Server version: 5.1.63-0ubuntu0.10.04.1 (Ubuntu)
...
mysql> CREATE TABLE mytest (
    -> _key VARCHAR(100) NOT NULL DEFAULT '',
    -> _value VARCHAR(255) NOT NULL DEFAULT '');
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO mytest VALUES ('aaa', 'bbb');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytest VALUES (' aaa ', 'ccc');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytest;
+------+--------+
| _key | _value |
+------+--------+
| aaa  | bbb    |
| aaa  | ccc    |
+------+--------+
2 rows in set (0.00 sec)

mysql> SELECT QUOTE(_key), QUOTE(_value) FROM mytest;
+-------------+---------------+
| QUOTE(_key) | QUOTE(_value) |
+-------------+---------------+
| 'aaa'       | 'bbb'         |
| 'aaa '      | 'ccc'         |
+-------------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT QUOTE(_key), QUOTE(_value) FROM mytest
    -> WHERE _key = 'aaa';
+-------------+---------------+
| QUOTE(_key) | QUOTE(_value) |
+-------------+---------------+
| 'aaa'       | 'bbb'         |
| 'aaa '      | 'ccc'         |
+-------------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT QUOTE(_key), QUOTE(_value) FROM mytest
    -> WHERE BINARY _key = 'aaa';
+-------------+---------------+
| QUOTE(_key) | QUOTE(_value) |
+-------------+---------------+
| 'aaa'       | 'bbb'         |
+-------------+---------------+
1 row in set (0.00 sec)






$ sqlite3 playground
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE mytest (
   ...> _key VARCHAR(100) NOT NULL DEFAULT '',
   ...> _value VARCHAR(255) NOT NULL DEFAULT '');
sqlite> INSERT INTO mytest VALUES ('aaa', 'bbb');
sqlite> INSERT INTO mytest VALUES ('aaa ', 'ccc');
sqlite> SELECT * FROM mytest;
aaa|bbb
aaa |ccc
sqlite> SELECT QUOTE(_key), QUOTE(_value) FROM mytest;
'aaa'|'bbb'
'aaa '|'ccc'
sqlite> SELECT QUOTE(_key), QUOTE(_value) FROM mytest
   ...> WHERE _key = 'aaa';
'aaa'|'bbb'