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]
