Why is the subquery inside an iif affecting results?

Question

3 example queries:

SELECT Pl_dscs.cid;
FROM itfwarehouse!pl_dscs;
INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
    iif(.f.,pl_dscs.ldiscontinued=.f.,.t.) AND;
    iif(.f.,.t.,.t.) INTO CURSOR lcresult

SELECT Pl_dscs.cid;
FROM itfwarehouse!pl_dscs;
INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
    iif(.f.,pl_dscs.ldiscontinued=.f.,.t.) AND;
    iif(.f.,.f.,.t.) INTO CURSOR lcresult

SELECT Pl_dscs.cid;
FROM itfwarehouse!pl_dscs;
INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
    iif(.f.,pl_dscs.ldiscontinued=.f.,.t.) AND;
    iif(.f.,NOT pl_dscs.cid in (select cpl_dscs_id FROM itfwarehouse!pl_dscsperfacwsite WHERE pl_dscsperfacwsite.cfacilities_id = ""),.t.) INTO CURSOR lcresult

This is all about the difference between the 2nd example and the 3rd example.

• The 1st example returns 1 record
• The 2nd example returns 1 record
• The 3rd example does not return any records

The only difference between the 2nd and 3rd examples is the 2nd expression in the last iif is a subquery. Because the 1st expression in that iif is false, the subquery should not get executed. Obviously it is somehow effecting the results but how/why?

Thanks, John

Answer 1

OK I could check this on my work machine and I think you found a bug. Since we know that it would never get corrected, here is a workaround for you, instead of using IIF(), use expressions that mimic IIF() - I never liked the idea of using IIF() in a where clause anyway:

LOCAL llState
llState = .F.

SELECT Pl_dscs.cid;
FROM itfwarehouse!pl_dscs;
INNER JOIN itfwarehouse!nonbaggedsys_descs ON Pl_dscs.cnonbaggedsys_descs_id = nonbaggedsys_descs.cid;
WHERE PL_dscs.cnonbaggedsys_descs_id = lcnbsdcid AND;
    ((m.llState AND pl_dscs.ldiscontinued=.f.) OR (!m.llState AND .T.)) ;
    ((m.llState AND ;
        pl_dscs.cid NOT in ;
        (select cpl_dscs_id ;
        FROM itfwarehouse!pl_dscsperfacwsite ;
        WHERE pl_dscsperfacwsite.cfacilities_id = "");
    ) OR  (!m.llState AND .T.)) ;
INTO CURSOR lcresult ;
NOFILTER

As per the bug itself with reproduction (and workaround):

 LOCAL state
state = .F.

Select c.Cust_id, c.Country, o.Order_id ;
    FROM _samples+'data\Customer' c ;
    inner Join _samples+'data\Orders' o On c.cust_id = o.cust_id ;
    where c.Country = 'USA' And ;
    IIF(m.state, ;
        c.Cust_id NOT in (SELECT cust_id FROM _samples+'data\Customer' WHERE Country = 'USA' AND MaxOrdAmt > 1000) ;
        ,.T.) ; 
    ORDER BY c.Country, c.Cust_id, o.Order_id ;
    INTO Cursor crsResult ;
    nofilter


Select crsResult
Browse


Select c.Cust_id, c.Country, o.Order_id ;
    FROM _samples+'data\Customer' c ;
    inner Join _samples+'data\Orders' o On c.cust_id = o.cust_id ;
    where c.Country = 'USA' And ;
    ((m.state AND ;
        c.Cust_id NOT in (SELECT cust_id FROM _samples+'data\Customer' WHERE Country = 'USA' AND MaxOrdAmt > 1000)) ;
        OR ;
     (!m.state AND .T.)) ; 
    ORDER BY c.Country, c.Cust_id, o.Order_id ;
    INTO Cursor crsResult ;
    nofilter


Select crsResult
Browse

With IIF(), it is kind of (but not exact) creating a cross join with the subquery.