Re: wrong query result

On Mar 16, 8:15 pm, fred <f...@bloomberg.com> wrote: > SaltTan wrote: > > 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)> > I tried it on IDS 10.00.UC4W3 (latest I have running) and in 7.31.UD8XH > and it works consistently in sqlcmd on both. > > I am surprised though. You (or BAAN I guess) are mixing ANSI and > traditional syntax in the same join. I didn't every think to try that > and at first I thought that was your problem. So I reformulated the > query in PURE ANSI and that also works consistently for me. Try it and > see what happens: > > SELECT b.tabid, b.tabname, c.grantee, e.username > FROM systables b > JOIN ( > systabauth AS c > LEFT JOIN sysusers AS e > ON e.username = c.grantee > ) > ON c.tabid = b.tabid > AND b.tabid IN ( > SELECT a.tabid > FROM systables a > WHERE a.tabid < 10 > );> > In ANSI syntax the filters in the WHERE clause (you had c.tabid = > b.tabid and the IN clause in the WHERE clause) are performed post-join > so if the optimizer used ANSI logic it would have had to create a > Cartesian product join between systables and the results of the OUTER > join between systabauth and sysusers and then filter that for the tabid > join condition and the tabid IN condition. > > In the DB I tested with there are few table level privs so my set > explain results may differ from yours, but for your original (and a > version of the pure ANSI with the IN clause still in the WHERE) set > explain reports a cost of 135 with estimated 122 rows returned. With > the corrected ANSI version above it reports cost of 135 but 5 rows > returned which matches better the actual 9 matching rows. YMMV, but on > a much more complex database like BAAN's the difference in cost, the > estimate, and the actual runtime may be significant. Inconsistent > results aside. > > <SNIP> > > Art S. Kagel Yes, Baan uses ANSI syntax for outer joins. I have tried your query with pure ANSI join, but it does not help. The costs are different, but the result is the same. Such a query works ok for us on xC4, we have encountered this bug after upgrade from 10.00.FC4 to 10.00.FC6.