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

[James Henstridge]

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.