Why do two logically similar queries in web2py give different results?

I've been struggling with this for a while and just tried moving the conditions around on a whim. Why does it work one way and not the other?

On this table definition:

db.define_table('bids',
                 Field('body', 'text', label="Application"),
                 Field('selected', 'string', requires=IS_IN_SET(['Yes', 'No']), readable=False, writable=False, default='No', widget=SQLFORM.widgets.radio.widget, label="Select this application"), 
                 Field('confirmed', 'string', requires=IS_IN_SET(['Yes', 'No']), readable=False, writable=False, default='No', widget=SQLFORM.widgets.radio.widget, label="Confirm acceptance"),
                 Field('delivered', 'string', requires=IS_IN_SET(['Yes', 'No']), readable=False, writable=False, default='No'),
                 Field('posted_on', 'datetime', readable=True, writable=False),
                 Field('posted_by', 'reference auth_user', readable=False, writable=False),
                 Field('job_id', 'reference jobs', readable=False, writable=False)
                 )

This query yields the correct data

query = db.bids.job_id == job_id and db.bids.delivered=='No' and db.bids.selected =='Yes' and db.bids.confirmed=='Yes'

while this one doesn't

query = db.bids.job_id == job_id and db.bids.selected =='Yes' and db.bids.confirmed=='Yes' and db.bids.delivered=='No'

Solution

Neither of the queries are correct because you have used and instead of & (and failed to wrap each condition in parentheses). It should be:

((db.bids.job_id == job_id) & (db.bids.delivered == 'No') &
 (db.bids.selected == 'Yes') & (db.bids.confirmed == 'Yes'))

The original query:

db.bids.job_id == job_id and db.bids.delivered=='No' and db.bids.selected =='Yes' and db.bids.confirmed=='Yes'

is simply equivalent to:

True and True and True and db.bids.confirmed == 'Yes'

which results in just a single condition in the final query:

db.bids.confirmed == 'Yes'