Thanks!!! That's why I never get to reproduce the problem (and send you
a VTrace)... When I detected this in past, was a delete with subquery...
When I was trying to reproduce, I tried only select with subqueries!
I think this will fix my report too...
Again, very thanks!
Edson Richter
Becker, Holger wrote:
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]
