[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sapdb-general
Subject:    RE: AW: bug in outer join with (+)
From:       "Stoedtler, Mathias" <mathias.stoedtler () hp ! com>
Date:       2004-06-29 12:13:37
Message-ID: 878F763D1523BA48A192A94F58C03C3F0339B0A8 () rtoexc01 ! emea ! cpqcorp ! net
[Download RAW message or body]


Hi Holger,

Thanx a lot for your quick answer. 

The statement you sent seems to work fine and returns the expected result \
(count=205). What's very interesting about it is that it works fine in Oracle and \
Internal SQL mode. But it does not work under Oracle 8 because of an invalid syntax. 

So If I understand this right that the original statement isn't working results in a \
bug of MaxDB?

Regards,
  Mathias

-----Original Message-----
From: Becker, Holger [mailto:holger.becker@sap.com] 
Sent: Tuesday, June 29, 2004 1:48 PM
To: Stoedtler, Mathias
Subject: AW: AW: bug in outer join with (+)

Hi Mathias,

looks like a known but not fixwd design problem 
within (+) outer joins syntax in MaxDB.

Could you please try this statement and let me know 
the result.

SELECT  t1.ent_name
FROM   ifc_entities T1 
inner join rep_labels T4 on T4.lbl_oid = T1.ent_lbl_oid 
left outer join ifc_attributes T6 on  T1.ent_atr_oid = T6.atr_oid
left outer join rep_labels T7 on T6.atr_lbl_oid = T7.lbl_oid
left outer join ifc_attributes T10 on T1.ent_search_atr_oid = T10.atr_oid
left outer join rep_labels T11 on T10.atr_lbl_oid = T11.lbl_oid

Kind regards
Holger
SAP Labs Berlin

> -----Ursprüngliche Nachricht-----
> Von: Stoedtler, Mathias [mailto:mathias.stoedtler@hp.com] 
> Gesendet: Dienstag, 29. Juni 2004 12:16
> An: maxdb@lists.mysql.com
> Betreff: Re: AW: bug in outer join with (+)
> 
> 
> 
> Hello,
> 
> Is my problem the same as in http://lists.mysql.com/maxdb/21965 ?
> 
> I'd like to do a select statement with lots of outer joins:
> 
> SELECT  t1.ent_name
> FROM   ifc_entities T1
> ,ifc_attributes T10
> ,rep_labels T11
> ,ifc_attributes T6
> ,rep_labels T7
> ,rep_labels T4
> WHERE (T4.lbl_oid = T1.ent_lbl_oid)
> AND (T6.atr_oid (+) = T1.ent_atr_oid)
> AND (T7.lbl_oid (+) = T6.atr_lbl_oid)
> AND (T10.atr_oid (+) = T1.ent_search_atr_oid)
> AND (T11.lbl_oid (+) = T10.atr_lbl_oid)
> 
> On Oracle this works fine and on a database with the same contents it
> returns 205 results. Funny thing about it is that on MaxDB it only
> returns one. Does this belong to the bug described in
> http://lists.mysql.com/maxdb/21965 ? 
> 
> 
> > As a workaround you could enable new outer join handling 
> > via command "diagnose optimize join outer on".
> 
> I tried this and it hadn't made any effect to the statement results.
> 
> I also tried to set the Parameter 
> JOIN_OPERATOR_IMPLEMENTATION to YES in
> Database Manager, but I wasn't able to do this because if I 
> typed in YES
> still NO appears for the new Value.
> 
> Greets,
> Mathias
> 
> 
> 
> -- 
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:    
> http://lists.mysql.com/maxdb?unsub=holger.becker@sap.com
> 

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=sapdb-general@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]