Hi!
Sorry, I forgot to mention: It is 7.4.3.17 on W2K.
I tried with a second instance of 7.4.3.17 on a XP machine, there the bug
also "works" :-)
The explain for the query without the index is:
ZIP TABLE SCAN 1
RESULT IS COPIED , COSTVALUE IS 5
After generating the Index, the explain tells me:
DBA ZIP IDX_ZIP_CTRY INDEX SCAN 1
*
DBA RESULT IS COPIED , COSTVALUE IS 3
If you need anything else, just tell me. I can offer to contact one of the
instances on my machines via WAN, if you want.
(Gerald)
----- Original Message -----
From: Becker, Holger
To: 'Gerald Nowitzky' ; SAPDB
Sent: Friday, June 06, 2003 12:09 PM
Subject: RE: Grouping / max() with Index
Gerald Nowitzky wrote:
> Again I stumbled over a bug:
>
> Here are the steps to reproduce:
> First I create a table:
>
> CREATE TABLE "DBA"."ZIP"
> (
> "ZIP" Integer,
> "COUNTRY_ID" Varchar (50) ASCII
> )
>
> and insert some values:
>
> insert into zip values (81549,'D')
> --
> insert into zip values (76133,'D')
> --
> insert into zip values (4540,'A')
> --
> insert into zip values (8867,'A')
>
> now I try to get the max(zip) for each country:
>
> select country_id,max(zip) as maxzip from zip group by country_id
> The correct result is:
>
> country_id maxzip
> A 8867
> D 81549
>
> now I create an Index:
> CREATE INDEX "IDX_ZIP_CTRY" ON "DBA"."ZIP"("ZIP" ASC,
> "COUNTRY_ID" ASC)
> and try again:
>
> select country_id,max(zip) as maxzip from zip group by country_id
> Now I get:
>
> country_id maxzip
> D 81549
>
> I tried with a table with zips for different countries. In
> case I build the
> Index, I only get the absolute max of all zips for all
> countries, not the
> grouped max
>
> I could not find something in the PTS, so I decided to report
> it. Hope it is
> a "new" one :-)
I couldn't reproduce the problem neither in 7.4 nor in 7.3.
What kind of version do you use and how looks the explain output?
Best regards,
Holger
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
