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')); >> >> >> >> >> --