About unary operator consistency in queries

[Alan Etkin]

With trunk, I have tested the behavior of the ~ operator, and doesn't seem consistent to me:

Here Bruno gives a working example of belongs negation
https://groups.google.com/d/msg/web2py/fCB9a4K9FqU/GBLwfzNnkjMJ

But the book uses this example:

rows = db((~db.person.name=='Alex') | (db.person.id>3)).select()

This is wat I get in the console (with SQLite/Python 2.7.3)

>>> q = ~db.auth_user.id.belongs(1,2,3)
>>> q
<Query (NOT (auth_user.id IN (1,2,3)))>

>>> q = ~db.auth_user.id==0
>>> q
<Query (auth_user.id DESC = 0)>

>>> q = ~db.auth_user.id>0
>>> q
<Query (auth_user.id DESC > 0)>

>>> db(q).select()
...
OperationalError: near "DESC": syntax error

Note that the second and third query would return an OperationalError (the INVERT operator is used instead of NOT)

To avoid the error the syntax must be ~(db.auth_user.id==0), with parethesis.

Is this an problem with the sqlite adapter only?

[Alan Etkin]

Sorry, here's the actual error:

[Marin Pranjić]

~ is an unary operator and has precedence over binary operators, that's why the error pops out.

~db.person.name=='Alex' should be db.person.name!='Alex', and ~db.person.name should be used in orderby=...

I don't like the ~(db.auth_user.id==0). I'm not even sure if it works.

Marin

--
 
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Alan Etkin]

It works:

>>> q = ~(db.auth_user.id == 1)
<Query (NOT (auth_user.id = 1))>

I don't like that way either, but is the example in the book correct or it should be fixed?

[Jonathan Lundell]

On 29 Jan 2013, at 8:50 AM, Alan Etkin <spam...@gmail.com> wrote:

With trunk, I have tested the behavior of the ~ operator, and doesn't seem consistent to me:

Here Bruno gives a working example of belongs negation
https://groups.google.com/d/msg/web2py/fCB9a4K9FqU/GBLwfzNnkjMJ

But the book uses this example:

rows = db((~db.person.name=='Alex') | (db.person.id>3)).select()

This is wat I get in the console (with SQLite/Python 2.7.3)

>>> q = ~db.auth_user.id.belongs(1,2,3)
>>> q
<Query (NOT (auth_user.id IN (1,2,3)))>

>>> q = ~db.auth_user.id==0
>>> q
<Query (auth_user.id DESC = 0)>

>>> q = ~db.auth_user.id>0
>>> q
<Query (auth_user.id DESC > 0)>


>>> q.select()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
AttributeError: 'Query' object has no attribute 'select'
>>> db(q).select()
Traceback (most recent call last):


...
OperationalError: near "DESC": syntax error



Note that the second and third query would return an OperationalError (the INVERT operator is used instead of NOT)

To avoid the error the syntax must be ~(db.auth_user.id==0), with parethesis.

Is this an problem with the sqlite adapter only?

It's an issue with the precedence of Python operators. ~ has higher precedence than comparisons, so the parens are required. Python objects can override the functionality of operators, but not their precedence.

A similar caveat applies to the use of | or & in queries.

[Alan Etkin]

A similar caveat applies to the use of | or & in queries.

Ok, thanks for the tips Marin and Jonathan.

I assume then that the book's example should use parenthesis for negation, since web2py cannot change the Python operator precedence.

[Jonathan Lundell]

Yes. Instead of:

rows = db((~db.person.name=='Alex') | (db.person.id>3)).select()

it should read:

rows = db(~(db.person.name=='Alex') | (db.person.id>3)).select()

[Jonathan Lundell]

I pushed a fix, along with adding a note to the existing note on operator precedence.