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'
