Gerald Nowitzky wrote:
>
> I just met a little bug which should have been killed and
> burried already.
> It really sounds to me like PTS 1114232. According to PTS 1114232 the
> solution is "delivered" in 7.4.3.00, already in 2002.
> However this one seems to have some more lives.
> I am using 7.4.3.17 on W2K
> Steps to reproduce:
>
> CREATE TABLE "DBA"."MURXX"
> (
> "MSG_KEY" Integer NOT NULL DEFAULT SERIAL (1),
> "MSG_FROM" Integer NOT NULL,
> "MSG_TO" Integer NOT NULL,
> "MSG_TEXT" Varchar (7500) ASCII NOT NULL,
> "MSG_DATE" Timestamp NOT NULL DEFAULT TIMESTAMP,
> "MSG_DATE_RD" Timestamp,
> "FROM_DEL" Smallint NOT NULL DEFAULT 0,
> "TO_DEL" Smallint NOT NULL DEFAULT 0,
> "MSG_EMAILED" Smallint NOT NULL DEFAULT 0,
> "MSG_READ" Smallint NOT NULL DEFAULT 0,
> "STOP_FROM" Smallint NOT NULL DEFAULT 0,
> "STOP_TO" Smallint NOT NULL DEFAULT 0,
> PRIMARY KEY ("MSG_KEY", "MSG_FROM", "MSG_TO")
> )
> --
> CREATE INDEX "IDX_MURXX_DAT" ON "DBA"."MURXX"("MSG_DATE" DESC)
> --
> insert into murxx values(0,72,811,'blabla','2003-05-01
> 17:00:00.000000','2003-05-03 17:00:00.000000',0,0,0,0,0,0)
> --
> insert into murxx values(0,56,72,'blabla','2003-05-03
> 17:00:00.000000','2003-05-05 17:00:00.000000',0,0,0,0,0,0)
>
> If you now do a:
> SELECT * FROM Murxx WHERE 72 in (msg_to,msg_from) and
> msg_to<>msg_from order
> by msg_date desc
>
> you get the "KB Stack type Illegal".
Thank you for reporting, will be fixed within the next version.
It is the problem, that the columns needed for the qualification
are part of the index (an index always includes the index- PLUS
the primary key-columns) and the transformation into the
index-qualification causes the trouble.
To overcome this, although using the index for qualification, is
to add a stupid, superfluous function.
In your case change msg_to<>msg_from to
msg_to<>fixed(msg_from,10,0)
Sorry for inconveniences.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
