AW: Re: "order by" problem with 7.6.00.34

> -----Ursprüngliche Nachricht-----
> Von: Becker, Holger [mailto:[email protected]] 
> Gesendet: Mittwoch, 14. Februar 2007 08:19
> An: Hübschen, Peter
> Cc: [email protected]; Robert Klemme
> Betreff: RE: [LIKELY JUNK]Re: "order by" problem with 7.6.00.34
> 
> > -----Original Message-----
> > From: Robert Klemme [mailto:[email protected]]
> > Sent: Dienstag, 13. Februar 2007 22:51
> > To: Hübschen, Peter
> > Cc: [email protected]
> > Subject: [LIKELY JUNK]Re: "order by" problem with 7.6.00.34
> > 
> > 2007/2/13, Hübschen, Peter <[email protected]>:
> > > Hello,
> > >
> > > I have a strange problem with an "order by"-clause, which
> > doesn't sort as expected. First it appeared in a stored procedure 
> > which works correctly with SAPDB 7.4 on Linux. For testing 
> I created a 
> > local MaxDB-Installation on my PC (WinXP
> > Pro.) with MaxDB 7.6.00.34 and imported a current backup 
> from my 7.4. 
> > database. The "order by" clause looks a little bit 
> "strange", but due 
> > to an old design failure I had to go this way.
> > > Here's the Select-Statement:
> > >
> > > SELECT tbl_Anfrage.Anfragenummer,
> > >        tblKunden.Kundenname
> > > FROM tblKunden,
> > >      tbl_Anfrage,
> > >      tbl_Teile
> > > WHERE tblKunden.Kunden_ID (+) = tbl_Anfrage.Kunden_ID
> > >     AND tbl_Anfrage.lfd_Anfragenummer =
> > tbl_Teile.lfd_Anfragenummer (+)
> > >     AND tbl_Anfrage.Anfragenummer Like 'D-%/07'
> > > GROUP BY tbl_Anfrage.Anfragenummer,
> > >          tblKunden.Kundenname,
> > >          tbl_Anfrage.Anlagedatum
> > > ORDER BY
> > > 
> > SUBSTR(Anfragenummer,1,2)||substr(Anfragenummer,6,2)||'0'||sub
> > str(Anfragenummer,3,2) desc
> > >
> > > The output looks like this:
> > > D-01/07  XYZ
> > > D-03/07  ABC
> > > D-05/07  EFG
> > > D-02/07  HIJ
> > > D-06/07  MNO
> > > D-04/07  STV
> > >
> > > correctly it should be:
> > > D-06/07  MNO
> > > D-05/07  EFG
> > > D-04/07  STV
> > > D-03/07  ABC
> > > D-02/07  HIJ
> > > D-01/07  XYZ
> > >
> > > If I add
> > SUBSTR(Anfragenummer,1,2)||substr(Anfragenummer,6,2)||'0'||sub
> > str(Anfragenummer,3,2) so it is displayed in the result and in the 
> > group by clause, it is ordered correctly, but I get some duplicate 
> > rows of D-01/07. So I don't know where the problem is and I found 
> > nothing in the documenation
> > 
> > One strange thing: you group by a tbl_Anfrage.Anlagedatum 
> but do not 
> > show it anywhere. This might be responsible for duplicates 
> although I 
> > fail to understand how then you don't see the duplicates in your 
> > original query (i.e. the one where you did not add the SUBSTR...).
> > 
> > Did you check the bugtracker?  Maybe this is a known issue. 
> If not I'd 
> > probably try to recreate a simple test case and submit it as a bug 
> > report. I know that in the past there were issues with 
> joins.  So one 
> > thing to try could be to change the DB parameter that controls join 
> > execution.  For me that helped in one case in the past (7.5).
> > 
> > Good luck!
> > 
> > robert
> 
> Hi,
> 
> Roberts hint about some join problems remind me on a known 
> problem with some new aggregation feature.
> 
> You could enable this feature by setting database parameter 
> HASHED_RESULTSET to NO.
> 
> You could change the parameter in online mode. For details 
> see "dbmcli help param_put".
> 
> Please let me know if this solved you problem.
> 
> Kind regards
> Holger
> 
> Holger Becker
> MaxDB&liveCache
> SAP AG
> www.sap.com
> Sitz der Gesellschaft/Registered Office: Walldorf, Germany 
> Vorstand/SAP Executive Board: Henning Kagermann 
> (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, 
> Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des 
> Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso 
> Plattner Registergericht/Commercial Register Mannheim No HRB 350269
> 
> Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder 
> sonstige vertrauliche Informationen enthalten. Sollten Sie 
> diese E-Mail irrtümlich erhalten haben, ist Ihnen eine 
> Kenntnisnahme des Inhalts, eine Vervielfältigung oder 
> Weitergabe der E-Mail ausdrücklich untersagt.
> Bitte benachrichtigen Sie uns und vernichten Sie die 
> empfangene E-Mail. Vielen Dank.
> 
> This e-mail may contain trade secrets or privileged, 
> undisclosed, or otherwise confidential information. If you 
> have received this e-mail in error, you are hereby notified 
> that any review, copying, or distribution of it is strictly 
> prohibited. Please inform us immediately and destroy the 
> original transmittal. Thank you for your cooperation. 
> 

Hello, 

I've set the parameter to no and tested it, but I always get the results mentioned above (not sorted or with duplicate rows - but sorted correctly - if I add my "order"-string to the "output"-section).

Kind regards
Peter 

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]