PG applies higher precedence to IS than string concatenation

[Former user]

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)

[Mike Bayer]

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)

[Mike Bayer]

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,

[Mike Bayer]

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())
    }
)

[Former user]

Very good. Thanks for both the 0.8 fix and 0.7 workaround.

[Mike Bayer]

Removing milestone: 0.8.0b1 (automated comment)