[storm] What's the best way to handle Python Boolean values with Oracle?

[Jason Baker]

On Tue, Jun 23, 2009 at 9:30 AM, Drew Smathers<drew.smathers at gmail.com> wrote:
> On Tue, Jun 23, 2009 at 5:16 AM, James Henstridge<james at jamesh.id.au> wrote:
>> On Mon, Jun 22, 2009 at 11:44 PM, Jason Baker<jbaker at zeomega.com> wrote:
>>> There are two tests left that are giving me trouble:
>>>
>>>    def test_is_in_empty_result_set(self):
>>>        result1 = self.store.find(Foo, Foo.id < 10)
>>>        result2 = self.store.find(Foo, Or(Foo.id > 20, Foo.id.is_in(result1)))
>>>        self.assertEquals(result2.count(), 1)
>>>
>>>    def test_is_in_empty_list(self):
>>>        result2 = self.store.find(Foo, Eq(False, And(True, Foo.id.is_in([]))))
>>>        self.assertEquals(result2.count(), 3)
>>>
>>> Both of these tests pass a Python Boolean to the backend.  The problem
>>> is that these both pass a boolean value in the WHERE clause, which
>>> there's really not any direct translation for in Oracle.  I tried it
>>> with this compile function:
>>>
>>> @compile.when(bool)
>>> def compile_bool(compile, expr, state):
>>>    return compile_eq(compile, Eq(1, int(expr)), state)
>>>
>>> This essentially converts a bool to a 1=1 or 1=0 expression.  This
>>> works with the first test, but the second one gives me this:
>>>
>>> [10:27:38.064000] EXECUTE: 'SELECT COUNT(*) FROM foo WHERE 1 = 0 = (1
>>> = 1 AND 1 = 0)', (1, 0, 1, 1, 1, 0)
>>> [10:27:38.111000] ERROR: ORA-00933: SQL command not properly ended
>>>
>>> Which is of course gibberish in Oracle.  I can run the test without a
>>> WHERE clause and have it work, but will anything break if the backend
>>> can't handle an expression of the form FALSE = (TRUE AND FALSE)?  Or
>>> is there a better way to do this that I'm missing?
>>
>> If this is just a case of parentheses, it might be easiest to do the following:
>>
>> @compile.when(bool)
>> def compile_bool(compile, expr, state):
>>    if expr:
>>        return "(1=1)"
>>    else:
>>        return "(1=0)"
>>
>> That should avoid the operator precedence problems.
>>
>> James.
>>
>
> The problem is more than operator precendence with Oracle; Oracle
> simply doesn't have a boolean type (outside of PL/SQL) and can't
> understand a boolean comparison: (1=0) = (1=1).  This would result in
> an ORA-00933.  In that respect, I don't see how it's possible to make
> test_is_in_empty_list pass since it involves a boolean comparison.
> Given that boolean data type is optional in the SQL spec, would it not
> be sufficient to skip this test for oracle?
>
> -Drew
>

I don't think it's necessary to skip the test altogether.  The problem
as I see it is in this function (storm/expr.py line 479 in trunk):

    def is_in(self, others):
        if not isinstance(others, Expr):
            others = list(others)
            if not others:
                return False
            variable_factory = getattr(self, "variable_factory", Variable)
            for i, other in enumerate(others):
                if not isinstance(other, (Expr, Variable)):
                    others[i] = variable_factory(value=other)
        return In(self, others)

Or more specifically, these lines:

            if not others:
                return False

Thus, it makes sense to test that the back end can handle this kind of
value.  However, I think a case can be made that the test as written
is a bit overreaching.  This is because it not only tests that the
back end can handle receiving a False value from this function, but
also that it can handle an expression FALSE = (TRUE AND FALSE).  I can
get the test to pass if it were changed to this:

   def test_is_in_empty_list(self):
       result2 = self.store.find(Foo, Foo.id.is_in([]))
       self.assertEquals(result2.count(), 0)

...which is a more focused (and I would argue better) test.  However,
is there any other code which may require more advanced boolean
handling to even worry about *having* a test for FALSE = (TRUE AND
FALSE)?  I'm no SQL guru, but I don't see that as being a very common
SQL Statement.

Jason