Belongs with empty list fails on postgres

[Marin Pranjić]

The issue is that SQLite allows to do:

something = []
db(db.tablename.fieldname.belongs(something)).select()

However this fails on postgres (not sure what about the others).

I am using SQLite for development so I don't notice the error until I move the code into production.
Not only I have error tickets in production (which should not happen), my code ends up with lots of if-else lines and I don't like it either.

I think DAL should be smarter. Now it passes a query to db and postgres raises a syntax error.
What do you think?

[Niphlod]

uhm.
elif second==[] or second==():
            return '(1=0)'
why this is not reached ?

what is printed when you use db(db.tablename.fieldname.belongs([]))._select() ?

[Marin Pranjić]

I tried this:

db(db.auth_user.id.belongs())._select(db.auth_user.id)

And it prints:

'SELECT  auth_user.id FROM auth_user WHERE (1=0);'

So, it works as expected.
My question is invalid because I have something else in code:

db.tablename.fieldname.belongs(set())

Which is not working:

'SELECT  auth_user.id FROM auth_user WHERE (auth_user.id IN ());'

So the issue is with set, not a list.
Sorry :)

[Niphlod]

ok then post a bug so it doesn't get lost.
PS: I remember asking for the same thing and patching DAL, but it was for empty lists indeed.

[Marin Pranjić]

Done: http://code.google.com/p/web2py/issues/detail?id=1147


Is there a reason for

elif second==[] or second==()

while it could easily be

elif not second

?

This should fix it.

[Niphlod]

really don't know (but probably to ckeck for nulls)

the complete code is

def BELONGS(self, first, second):
        if isinstance(second, str):
            return '(%s IN (%s))' % (self.expand(first), second[:-1])

        elif second==[] or second==():
            return '(1=0)'

        items = ','.join(self.expand(item, first.type) for item in second)
        return '(%s IN (%s))' % (self.expand(first), items)

[Cliff Kachinske]

I've been working around this Postgres/psycopg2 "feature" for ages.

Something like this:

query = db.sometable.somefield==somevalue

avoid = [id1, id2, id3] #could be an empty list

if len(avoid):

    query &= ~(db.sometable.id.belongs(avoid))

[Richard Vézina]

Hello,

I fall on "(1=0)" with this query :

There is these belongs :

db(... & (db.table.field.belongs(('item',))).select(...)

Richard

--
 
 
 

[Richard Vézina]

Seems I can work around this like that :

db(... & (db.table.field.belongs(('item', ''))).select(...)

Richard