Becker, Holger schrieb:
could you please send me a small example with table and index definition and some data because I want to reproduce the problem here with the development release and I'm not able to get the wrong results with my tests.No problem to reproduce the bug. I have prepared a little example for You (in the text-attachment). One SQL statement per line, empty lines and lines starting with '//' are comments.
I am looking forward to get the bug fixed. It really bites us.
Regards,
Falk
BTW.
The statement can be rewritten as a single select joining 5 tables. You may want to understand why the correlated subquery is 500000 times faster when table population reaches about 100000 rows and one 'OR' gets involved. All columns are indexed.
--
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 7.4 example to illustrate that outer join + correlated subquery is broken:
CREATE TABLE p (x VARCHAR (240), s VARCHAR (240))
INSERT into p (x, s) values ('a', 'r')
INSERT into p (x, s) values ('b', 'r')
INSERT into p (x, s) values ('c', 'r')
CREATE TABLE u (x VARCHAR (240), t VARCHAR (240))
INSERT into u (x, t) values ('r', 'text')
// this should now return 3 entries a, b, c (ok):
SELECT p.x from p, u where p.s = u.x and u.t = 'text'
CREATE TABLE a (x VARCHAR (240), r VARCHAR (240))
CREATE TABLE b (x VARCHAR (240), y INT)
CREATE TABLE c (x VARCHAR (240), z INT)
INSERT into a (x, r) values ('a', 'd')
INSERT into a (x, r) values ('b', 'd')
INSERT into a (x, r) values ('c', 'e')
INSERT into b (x, y) values ('a', 118)
INSERT into b (x, y) values ('b', 3)
INSERT into c (x, z) values ('d', 1799)
// this should now return 3 lines where two lines have z=1799 (ok):
SELECT a.x, b.y, c.z from a, b, c where a.x = b.x (+) and a.r = c.x (+)
// this should now return those 2 lines (ok):
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
// but this returns 3 lines where only up to 2 would be permissable (BUG):
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')
// clean up
DROP table a
DROP table b
DROP table c
DROP table p
DROP table u
