Hi Holger,
but the misterioes is, the delete with the "not in" statement is working.
So it is not only related by the joins. There is a conjunction with self referencing in the query like "from ag_num where id in(select id from ag_num left join". Without this self reference the query is working.
regards
Albert
Becker, Holger schrieb:
Hi,
with a vtrace from Albert and some testing I figured out that not the in is the problem but deletes or updates with subqueries and outer joins.
Thanks to Albert for reporting this Problem.
We will fix it with one of the next releases. (detailed information see http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1130722)
regards Holger SAP Labs Berlin
-----Urspr�ngliche Nachricht-----
Von: Albert Steckenborn [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 20. Juli 2004 16:30
An: [EMAIL PROTECTED]
Betreff: Re: bug in "in" clause ????
Hi folks,
it seems as if the bug is related in the reference from "id in(select id".
I've have use a construction with "not in" at other tables that is working without any problems.
right way:
delete from bas_orgmem where cnt_id not in(select a.id from bas_org a, bas_orgmem b where a.id=b.cnt_id)
wrong way:
delete from bas_orgmem where cnt_id in(select cnt_id from bas_orgmem a, bas_org b where b.id(+)=a.cnt_id and b.id is null)
That means you cannot use the same key used for the "in clause" for your subquery.
In that case you have to use not in.
with best regards
Albert
Albert Steckenborn schrieb:
Hi folks,
following situation:
KERNEL 7.5.0 BUILD 015-121-074-675 updated yesterday from
008 because
join problems. SuSE Linux 8.2, P4 2600, 1GB RAM, 2 Ultra160
raw devices.
I want to delete all rows from table A (150.000 rows) without a reference in table B (380.000 rows).
At first i have done a select to make shure for creating
the right result.
statement: bas_agnums.id=Primary key
select id from bas_agnums where id in(select distinct id
from bas_agnums
b left join va_ums on id=ag_id where ag_id is null) result is ok with 55865 rows. Time for request about 13sec.
where starting the following delete
delete from bas_agnums where id in(select distinct id from
bas_agnums b
left join va_ums on id=ag_id where ag_id is null)
No rows updated or deleted. No Result. Thats definitive a bug.
When trying to do the same with "exists" or "all" clause
request needs
more than 4 hours and the database is no more responding for other request to table bas_agnums. Thats not acceptable for my customers.
with best rgds.
Albert Steckenborn
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
