It is correct in the sense it is what I intended it to be. You are
proposing a change of behavior. I see where you are coming from. Please
open a ticket about this.
I would like to have some more opinions about this. Should None in
is_active be treated as True?
On Friday, 21 December 2012 14:02:25 UTC-6, Cliff Kachinske wrote:
>
> SQL is incorrect.
>
> is: "AND supplier_contacts.is_active = 'T'"
> should be "AND (supplier_contacts.is_active = 'T' OR
> supplier_contacts.is_active IS NULL)"
>
> On Friday, December 21, 2012 1:05:16 PM UTC-5, Massimo Di Pierro wrote:
>>
>> It looks to be the generated SQL is correct. It is possible you enabled
>> record versioning and that added the common_filter is_active==True. Yet
>> perhaps you have records with is_active=None and therefore they showed up
>> before and not now.
>>
>> On Friday, 21 December 2012 10:50:13 UTC-6, Cliff Kachinske wrote:
>>>
>>> Salient details from two tables:
>>>
>>> db.define_table(
>>> 'suppliers',
>>> Field('name', length=256, required=True, notnull=True),
>>> ....
>>>
>>>
>>> db.define_table(
>>> 'supplier_contacts',
>>> Field('supplier_id', db.suppliers),
>>> Field('first_name', length=32, required=True, notnull=True),
>>>
>>>
>>>
>>> The code below worked in 1.99.7. If the supplier had two contacts, it
>>> would return two rows as expected, both with the same supplier data but
>>> each with individual contact data.
>>>
>>> In 2.2.1 it returns no rows.
>>>
>>> def get_approved_suppliers(product_id):
>>>
>>> return db(
>>> (db.product_suppliers.product_id==product_id) &
>>> (db.product_suppliers.supplier_id==db.suppliers.id)
>>> ).select(
>>> db.suppliers.id,
>>> db.suppliers.name,
>>> # more supplier details omitted for brevity,
>>> db.supplier_contacts.id,
>>> db.supplier_contacts.first_name,
>>> # contact details omitted for brevity.
>>> left = db.supplier_contacts.on(
>>> db.supplier_contacts.supplier_id==db.suppliers.
>>> id
>>> )
>>> )
>>>
>>>
>>> This is the query as shown by db._lastsql. (Broken into chunks for
>>> readability)
>>>
>>> SELECT suppliers.id, suppliers.name, suppliers.address, suppliers.
>>> address_2, suppliers.city, suppliers.state, suppliers.zip, suppliers.
>>> land_line, suppliers.fax, suppliers.email,
>>> suppliers.website,supplier_contacts
>>> .id, supplier_contacts.first_name,
>>> supplier_contacts.middle_name,supplier_contacts
>>> .last_name, supplier_contacts.generation,
>>> supplier_contacts.email,supplier_contacts
>>> .mobile, supplier_contacts.land_line, supplier_contacts.fax
>>>
>>> FROM product_suppliers, suppliers
>>>
>>> LEFT JOIN supplier_contacts ON (supplier_contacts.supplier_id =suppliers
>>> .id)
>>>
>>> WHERE (((((product_suppliers.product_id = 340) AND
>>> (product_suppliers.supplier_id
>>> = suppliers.id)) AND (product_suppliers.is_active = 'T')) AND (suppliers
>>> .is_active = 'T')) AND (supplier_contacts.is_active = 'T'));
>>>
>>>
>>>
>>>
>>>
--
