Marcin Pytel wrote:
> A strange thing happened to me:
> As I was trying to find duplicates of identifiers in my table
> I forgot, that
> the field I was looking duplicates in was declared as UNIQUE.
> What a big
> surprise it was, when I found that in those circumstances the
> query returned
> all rows from the table. I know - it is silly to look for
> duplicates when
> the field is declared as unique (bad experiences with MS
> Access ;-) ),but I
> also think this behaviour of SAP isn't "the state of the
> Art". Below is the
> exaple of this behaviour.
>
>
> CREATE TABLE "DBA"."GIRLS"
> (
> "NAME" Varchar (20) UNIQUE NOT NULL,
> "DESCRIPTION" Varchar (200) NOT NULL
> )
> //
> INSERT INTO "DBA"."GIRLS"(NAME,DESCRIPTION)
> VALUES ('Victoria','The Blondie')
> //
> INSERT INTO "DBA"."GIRLS"(NAME,DESCRIPTION)
> VALUES ('Paula','The Red One')
> //
> INSERT INTO "DBA"."GIRLS"(NAME,DESCRIPTION)
> VALUES ('Gina','The Sweet One')
> //
> INSERT INTO "DBA"."GIRLS"(NAME,DESCRIPTION)
> VALUES ('Tina','The Intelligent One')
> //
> SELECT NAME
> FROM "DBA"."GIRLS"
> GROUP BY NAME
> HAVING COUNT(NAME)>1
Oops, thank you for reporting, will be corrected with the next kernel
version.
The problem occurs if an index is scanned to COUNT. Without Index
(if not all columns needed for select-list, GROUP, and in your case HAVING
can be found in one index) and without UNIQUE (which behaves like an index)
this problem does not occur.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
