Re: wrong query result
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