In() casts str strings differently to ==

[Stuart Bishop]

With Storm 0.18 and PostgreSQL 8.4, the following works as you expect:

>>> store.find(Person, Person.name == 'stub').first()
<Person at 0x7dbd750 stub (Stuart Bishop)>

However, if we use In(), the string is converted to binary rather than text:

>> store.find(Person, In(Person.name, ['stub'])).first()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/store.py", line 1102, in first
    return self._any()
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/store.py", line 1087, in _any
    result = self._store._connection.execute(select)
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/databases/postgres.py", line 249, in execute
    return Connection.execute(self, statement, params, noresult)
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/database.py", line 237, in execute
    raw_cursor = self.raw_execute(statement, params)
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/databases/postgres.py", line 259, in raw_execute
    return Connection.raw_execute(self, statement, params)
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/database.py", line 321, in raw_execute
    self._check_disconnect(raw_cursor.execute, *args)
  File "/home/stub/lp/lp-sourcedeps/eggs/storm-0.18-py2.6-linux-x86_64.egg/storm/database.py", line 366, in _check_disconnect
    return function(*args, **kwargs)
ProgrammingError: operator does not exist: text = bytea
LINE 1: ..., Person.visibility FROM Person WHERE Person.name IN (E'stub...
                                                             ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[Stuart Bishop (stub)]

This form works:

>>> store.find(Person, Person.name.is_in(['stub'])).first()
<Person at 0x8470490 stub (Stuart Bishop)>

This is a problem for us as the issue only recently became apparent in Launchpad after the Lucid upgrade with modern psycopg2.

[Gustavo Niemeyer (niemeyer)]

The conversion in both cases is in the column attribute itself (Person.name), not in the Eq or In expressions. It's consistent, and it's efficient as it means we don't have to arbitrarily navigate the expression tree to look for potential uncovered literals within expressions.

My suggestion is to just use the latter form, as it looks and reads nicely, prevents having to import expressions, is fast, and even works. ;-)

[Stuart Bishop (stub)]

Working around in Launchpad by converting In(Foo.col, iterable) to Foo.col.is_in(iterable). I'll probably need to do Like() too, which seems to be in the same situation.

[Launchpad QA Bot (lpqabot)]

r9959 in db-stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/9959) is part of this bug's fix.

[James Henstridge (jamesh)]

For what it is worth, you should be using unicode strings if you are matching against text columns. Storm treats bye strings as binary data and wraps them in psycopg2.Binary() when passing them to psycopg2. If you are using byte strings to represent text, then that is an error in your code.

It was easy to get this wrong with previous versions of psycopg2 though, since it formatting Binary() objects as text strings in the queries previously.

[James Henstridge (jamesh)]

And the reason why Person.name == 'some string' works is that your Person class is still using the sqlobject compatibility layer that converts the byte string to a unicode string for you. It is the exception to the rule, rather than everything else being broken.