> 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.
P.S.
What is the status of your evaluation?
I found the following annoying message on the mailing list making me resend my mail with the attachment inlined:
From: Z_ANTIGEN_WAR001 <[EMAIL PROTECTED]>--
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>,
"'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
Subject: Antigen found =*.*.txt file
Date: Mon, 6 Jan 2003 19:59:26 +0100
Antigen for Exchange found sapbug.sql.txt matching =*.*.txt file filter.
The file is currently Removed. The message, "Bugreport: Outer Join plus
correlated subquery is badly broken", was
sent from Falk Langhammer and was discovered in Rintsch, Daniel\Inbox
located at balticmail/lwar/WAR001
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)
-- Text attachment follows -------------------------------------------
// 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
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
