wrong query result

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;

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;

SaltTan said: > 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. Have you logged a case with technical support? It sounds like a bug. > 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; > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

On Mar 16, 10:14 am, "Obnoxio The Clown" <obno...@serendipita.com> wrote: > SaltTan said: > > > 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. > > Have you logged a case with technical support? It sounds like a bug. Yes we do.

SaltTan said: > On Mar 16, 10:14 am, "Obnoxio The Clown" <obno...@serendipita.com> > wrote: >> SaltTan said: >> >> > 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. >> >> Have you logged a case with technical support? It sounds like a bug. > > Yes we do. And what did they say? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.