Bugreport: Outer Join plus correlated subquery is badly broken

[Falk Langhammer]

Bug: Outer Join plus correlated subquery is badly broken

I believe that both features together will not work in most situations, if any.

Example: Consider the following query:
--
select a.x, b.y, c.z from a, b, c
where a.x = b.x (+)
and a.r = c.x (+)
and c.z = 1799
and exists (
select p.x from p, u
where p.x = a.x
and p.s = u.x
and u.t = 'text'
)
--
with tables a,b,c,p,u and all columns have indices.

Only rows with c.z=1799 should appear which works fine without the "and exists" clause.

With the "and exists" clause however, additional rows appear in the result set where values of c.z!=1799 are set to null.

The problem only arises with a correlated subquery ("p.x = a.x" above) and a constraint on an outer join column (c.z). In variations of the above, misleading error messages are printed (about wrong number of outer join tables within the correlated subquery which however, is an inner join).

I need the bug fixed. Please, do not explain how to rewrite the above without a subquery (we know how; in our real world situation, the rewritten query without subquery takes 40 minutes to execute, without correlated subquery 5 minutes, the printed one with correlated subquery 50 ms - it only does not work with outer joins and we need to use Oracle for this reason now. Hope, we can switch back soon).


In any case: A database returning wrong (too many) results in a query raises security issues...

Regards,
Falk
--
Dr. Falk Langhammer
Living Pages Research GmbH
Kolosseumstr. 1a D-80469 Munich, Germany
mailto:[EMAIL PROTECTED] http://www.living-pages.de
Phone +49 (89) 189 207-27 Fax +49 (89) 189 207-29
Mobil +49 (171) 79 39 667
mailto:[EMAIL PROTECTED] (urgent SMS with subject header)

_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general