Thread: [Sqlalchemy-tickets] [sqlalchemy] #2564: postgres sometimes needs parentheses around column_propert
Brought to you by:
zzzeek
From: sqlalchemy <mi...@zz...> - 2012-09-14 15:51:16
|
#2564: postgres sometimes needs parentheses around column_properties -----------------------+----------------------------------------- Reporter: kentbower | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: Component: postgres | Severity: no triage selected yet Keywords: | Progress State: awaiting triage -----------------------+----------------------------------------- version 0.8.0b1dev[[BR]] A column_property that concatenates two test 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) }}} -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2564> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-09-14 15:55:33
|
#2564: postgres sometimes needs parentheses around column_properties ----------------------------------+------------------------------------ Reporter: kentbower | Owner: zzzeek Type: defect | Status: new Priority: medium | Milestone: Component: postgres | Severity: no triage selected yet Resolution: | Keywords: Progress State: awaiting triage | ----------------------------------+------------------------------------ Description changed by kentbower: Old description: > version 0.8.0b1dev[[BR]] > > A column_property that concatenates two test 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) > }}} New description: 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) }}} -- -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2564#comment:1> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-09-14 18:36:47
|
#2564: postgres sometimes needs parentheses around column_properties ----------------------------+---------------------------------- Reporter: kentbower | Owner: zzzeek Type: defect | Status: new Priority: high | Milestone: 0.7.9 Component: postgres | Severity: minor - half an hour Resolution: | Keywords: Progress State: in queue | ----------------------------+---------------------------------- Changes (by zzzeek): * priority: medium => high * milestone: => 0.7.9 * severity: no triage selected yet => minor - half an hour * status_field: awaiting triage => in queue 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) -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2564#comment:2> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-09-15 02:50:32
|
#2564: PG applies higher precedence to IS than string concatenation ------------------------------+---------------------------------- Reporter: kentbower | Owner: zzzeek Type: defect | Status: new Priority: high | Milestone: 0.7.9 Component: postgres | Severity: minor - half an hour Resolution: | Keywords: Progress State: needs tests | ------------------------------+---------------------------------- Changes (by zzzeek): * status_field: in queue => needs tests Comment: 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 diff -r 9baa197f2c67 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, }}} -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2564#comment:3> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-09-22 20:56:50
|
#2564: PG applies higher precedence to IS than string concatenation -----------------------------------+---------------------------------- Reporter: kentbower | Owner: zzzeek Type: defect | Status: closed Priority: high | Milestone: 0.8.0 Component: postgres | Severity: minor - half an hour Resolution: fixed | Keywords: Progress State: completed/closed | -----------------------------------+---------------------------------- Changes (by zzzeek): * status: new => closed * status_field: needs tests => completed/closed * resolution: => fixed * milestone: 0.7.9 => 0.8.0 Comment: ra2c6b3101bc2 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 this but in 0.7 you can just use expression.self_group() to force parenthesis around the expression. {{{ #!python mapper(SomeClass, table, properties = { 'fullid': column_property((table.c.id_a + table.c.id_b).label('fullid').self_group()) } ) }}} -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2564#comment:4> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |
From: sqlalchemy <mi...@zz...> - 2012-09-24 10:49:09
|
#2564: PG applies higher precedence to IS than string concatenation -----------------------------------+---------------------------------- Reporter: kentbower | Owner: zzzeek Type: defect | Status: closed Priority: high | Milestone: 0.8.0 Component: postgres | Severity: minor - half an hour Resolution: fixed | Keywords: Progress State: completed/closed | -----------------------------------+---------------------------------- Comment (by kentbower): Very good. Thanks for both the 0.8 fix and 0.7 workaround. -- Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2564#comment:5> sqlalchemy <http://www.sqlalchemy.org/> The Database Toolkit for Python |