Hi Albert,
the example with the "not in" is an inner join.
The bug is only related to outer joins.
So do you have an example with outer joins in subquery
in a delete or update command wich works fine?
Kind regards
Holger
> -----Ursprüngliche Nachricht-----
> Von: Albert Steckenborn [mailto:a.steckenborn@xxxxxxxxxxxx]
> Gesendet: Freitag, 23. Juli 2004 19:11
> An: maxdb@xxxxxxxxxxxxxxx
> Betreff: Re: AW: bug in "in" clause ????
>
>
> 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:a.steckenborn@xxxxxxxxxxxx]
> >>Gesendet: Dienstag, 20. Juli 2004 16:30
> >>An: maxdb@xxxxxxxxxxxxxxx
> >>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/maxdb?unsub=holger.becker@xxxxxxx
> >
> >
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/maxdb?unsub=holger.becker@xxxxxxx
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx
|
