Re: wrong query result

SaltTan wrote: > Update: > Actually with no bind variable query does not work too. > And the query without where clause in subquery works fine. > >> 10.00xC5, 10.00xC6 >> A query with left join and "in" subquery was prepared and executed >> with a bind variable. The query returns some rows. >> Then the same query was executed with another bind variable. At this >> time the query returns no rows. >> If I change "left join" to "outer", the query works. >> If I change "in" to "exists", the query works too. >> >> Test: >> SELECT b.tabid, b.tabname, c.grantee, e.username >> FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON >> e.username = c.grantee) >> WHERE c.tabid = b.tabid >> AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid < 10)>> >> prepare; >> >> open; >> 9 rows returned >> close; >> >> open; >> 0 rows returned ---- !!!!!!!!!!!!!! >> close; >> >> unprepare; >> prepare; >> >> open; >> 9 rows returned >> close; Can we see the exact code, please? In particular, you are not showing any bind variables in the query. And I'd like to see the outer notation that you are using. Note that ANSI outer joins *do* have different semantics from Informix outer joins -- you cannot readily switch between the two and always get the same result, particularly if there is a filter condition on rows in the outer-join tables. You don't need to provide the data in systables, but it would be helpful to have the contents of systabauth and sysusers, at least as far as you think is relevant. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/