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

[Drew Smathers]

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