PG applies higher precedence to IS than string concatenation
version 0.8.0b1dev[BR]
A column_property that concatenates two text fields and then is searched to be NULL renders:
SELECT sometable.id_a || sometable.id_b AS fullid,
sometable.id_a AS sometable_id_a,
sometable.id_b AS sometable_id_b
FROM sometable
WHERE sometable.id_a || sometable.id_b IS NULL
This works with Oracle, but crashes with postgres (haven't tested other databases):
sqlalchemy.exc.ProgrammingError: (ProgrammingError) argument of WHERE must be type boolean, not type text
LINE 3: WHERE sometable.id_a || sometable.id_b IS NULL
^
Enclosing the column_property in parentheses makes postgres happy:
SELECT sometable.id_a || sometable.id_b AS fullid,
sometable.id_a AS sometable_id_a,
sometable.id_b AS sometable_id_b
FROM sometable
WHERE (sometable.id_a || sometable.id_b) IS NULL
fullid | sometable_id_a | sometable_id_b
--------+----------------+----------------
(0 rows)
Comments (5)
-
repo owner -
repo owner - changed title to PG applies higher precedence to IS than string concatenation
this patch moves concat/match to be the same precedence as is/like/similar. tests indicate that "add" is consistently higher precedence than IS, and "eq/=" is consistently lower.
diff -r 062f0a202af1185a21b94f33cb7af92cffacfd15 lib/sqlalchemy/sql/operators.py --- a/lib/sqlalchemy/sql/operators.py Fri Sep 14 21:58:19 2012 -0400 +++ b/lib/sqlalchemy/sql/operators.py Fri Sep 14 22:48:47 2012 -0400 @@ -640,31 +640,36 @@ _PRECEDENCE = { from_: 15, getitem: 15, - mul: 7, - truediv: 7, + mul: 8, + truediv: 8, # Py2K - div: 7, + div: 8, # end Py2K - mod: 7, - neg: 7, - add: 6, - sub: 6, + mod: 8, + neg: 8, + add: 7, + sub: 7, + concat_op: 6, match_op: 6, - ilike_op: 5, - notilike_op: 5, - like_op: 5, - notlike_op: 5, - in_op: 5, - notin_op: 5, - is_: 5, - isnot: 5, + + ilike_op: 6, + notilike_op: 6, + like_op: 6, + notlike_op: 6, + in_op: 6, + notin_op: 6, + + is_: 6, + isnot: 6, + eq: 5, ne: 5, gt: 5, lt: 5, ge: 5, le: 5, + between_op: 5, distinct_op: 5, inv: 5,
-
repo owner d345789fa510e0ae0983dec0bc253ac337405543
unfortunately this breaks things in 0.7, and it's not worth changing 0.7 so dramatically as to accommodate this. in 0.8 we just did a big overhaul of operator logic and this change doesn't introduce any breakage. I'm a little surprised by the difference in effect on the two versions. In 0.7 you can use expression.self_group() to force parenthesis around the expression.
mapper(SomeClass, table, properties = { 'fullid': column_property((table.c.id_a + table.c.id_b).label('fullid').self_group()) } )
-
Account Deleted Very good. Thanks for both the 0.8 fix and 0.7 workaround.
-
repo owner - removed milestone
Removing milestone: 0.8.0b1 (automated comment)
- Log in to comment
tricky, as we have the precedence of "concatenation" greater than "is". Might need to make all these precedences equal (5), at the moment "match" and "concat" are 6 vs. 5 for a whole series of comparisons.
(also precedences aren't dialect-specific and there's no quick route to that)