combination of and_() and or_() produces unexpected sql output

[Peter Grace]

For the given filter spec, the sql generated does not match what I would expect to see:

.filter(or_( \
            and_(func.len(ExtendedCDR.Source) > 6, ExtendedCDR.DestinationContext=='ael-outbound-dial'), \
            and_(func.len(ExtendedCDR.Destination)>6,ExtendedCDR.DestinationContext=='ael-std-extension') \
        ) \

This produces:

    (len(cdr_extended.[Source]) > ? AND 
    cdr_extended.[DestinationContext] = ? OR 
        len(cdr_extended.[Destination]) > ? AND 
    cdr_extended.[DestinationContext] = ?) 

Note only a single set of parantheses wrapping the logic, whereas I would expect to see:

(
(len(cdr_extended.[Source]) > ? AND cdr_extended.[DestinationContext] = ?)
OR 
(len(cdr_extended.[Destination]) > ? AND cdr_extended.[DestinationContext] = ?)
)

If I am misunderstanding how and_ and or_ are supposed to work together, can you set me straight, or alternatively let me know how I can cause sqlalchemy to emit the query with the proper order of operations?

[Mike Bayer]

Here's a test case that illustrates every possible combination of values for both expressions and shows that on Postgresql at least they are evaluated as equivalent regardless of parenthesization:

from sqlalchemy import create_engine

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')

expr1 = "%(a)s AND %(b)s OR %(c)s AND %(d)s"

expr2 = """
(
(%(a)s AND %(b)s)
OR
(%(c)s AND %(d)s)
)"""


def permutations():
    for a in (False, True):
        for b in (False, True):
            for c in (False, True):
                for d in (False, True):
                    yield {"a": a, "b": b, "c": c, "d": d}

for values in permutations():
    print("\n------------------ %(a)s %(b)s %(c)s %(d)s -----------" % values)
    test_expr1 = expr1 % values
    test_expr2 = expr2 % values

    r1 = e.scalar("SELECT %s" % test_expr1)
    r2 = e.scalar("SELECT %s" % test_expr2)
    assert r1 == r2

    r3 = e.scalar("SELECT (%s) = (%s)" % (test_expr1, test_expr2))
    assert r3

    print("\n---------------------------------------------")
    print("Result: %s" % r1)
    print("\n---------------------------------------------")

here's a gist showing the output: https://gist.github.com/zzzeek/75a5f02fc1549a6d15c5

[Mike Bayer]

confirmed on IRC