Wrong Results in simple max() Statement

[Former Member]

Hello,

We use MaxDB 7.8.02.28

On our Datbase I used the following Query :

select max(ROID) FROM READOUT WHERE AMID = 101721 AND STATE IN ( 'K','M','C' )

and got the Result:

EXPRESSION1

2153087

This is wrong what I checked via :

select * FROM READOUT WHERE AMID = 101721 AND STATE IN ( 'K','M','C' )

ORDER BY ROID DESC

my quick solution was to use:

select max(ROID),max(ROWANN) FROM READOUT WHERE AMID = 101721 AND STATE IN ( 'K','M','C' )

with the result:

EXPRESSION1     EXPRESSION2

7235411               2013-11-22 09:02:38.0

And the Value 7235411 was the one I expected and is obviosly greater than 2153087

but what happens there, and how could we make shure that we dont have same effects in other ends of our Application

I guess it might be tha case if alle Values could be catched out if the index.

The Query Plan is:

SCHEMANAME     TABLENAME     COLUMN_OR_INDEX     STRATEGY                                   PAGECOUNT

MDR                    READOUT          RO_STATE                    RANGE CONDITION FOR INDEX;     14229

                                                                                          ONLY INDEX ACCESSED;

                                                                                          MIN/MAX OPTIMIZATION;

                                                                                          AMID;     (USED INDEX COLUMN);

                                                                                          STATE;     (USED INDEX COLUMN);

                             JDBC_CURSOR_218                              RESULT IS COPIED   , COSTVALUE IS;         3

With index :

create index "RO_STATE" on "MDR"."READOUT" ("AMID" ASC,"STATE" ASC)

//

Any Idea ?

[Former Member]

Hello Jens-Peter,

I got another idea - with explain we got the information which index is used for the index only strategie -> RO_STATE                  

Would you please drop and recreate the index and check if you have still wrong results after the index has been dropped and newly created.

Regards, Christiane

[Former Member]

OOPS,

setting EnableMinMaxAggregationOptimizer = NO

generates performance problems at other ends of our Application.

I had to change back to YES !

regards

jph

[Former Member]

Hi,

when you switch of the Index only strategy the system has to read more data. But you cionfirmed that the error is related to the index only strategy which is choosen.

So the workaround would be to set the parameter as recommended.

I will try to reproduce this issue in our development area with the data provided.

I'll let you know our results.

Regards, Christiane

[Former Member]

Hi ,

the schema information is incomplete - to reproduce this issue I need ti have the definition of

"AUTOMAT" as well.

Regards, Christiane

[Former Member]

Ok here it comes:

create table "XYZ"."AUTOMAT"(

  "AMID" INTEGER not null,

  "ATID" INTEGER,

  "AMGRID" INTEGER,

  "SERNR" VARCHAR (16) ASCII,

  "TEL" VARCHAR (32) ASCII,

  "AMNR" VARCHAR (32) ASCII,

  "NAME" VARCHAR (64) ASCII,

  "ORT1" VARCHAR (64) ASCII,

  "ORT2" VARCHAR (64) ASCII,

  "WWSCODE" VARCHAR (32) ASCII,

  "LASTCONTACT" INTEGER,

  "BFID" INTEGER,

  "CTID" INTEGER,

  "BFTS" TIMESTAMP,

  "WANN" TIMESTAMP,

  "WER" INTEGER,

  "STATE" CHAR (1) ASCII,

  "EVA_SERNR" VARCHAR (20) ASCII,

  "EVA_MODNR" VARCHAR (20) ASCII,

  "EVA_BUILDSTD" INTEGER,

  "EVA_LOC" VARCHAR (30) ASCII,

  "EVA_USR1" VARCHAR (12) ASCII,

  "EVA_ASSETNR" VARCHAR (20) ASCII,

  "EVA_VIDTS_STAGE" INTEGER,

  "EVA_VIDTS_REV" INTEGER,

  "EVA_CHAN_SERNR" VARCHAR (20) ASCII,

  "EVA_CHAN_MODNR" VARCHAR (20) ASCII,

  "EVA_CHAN_REV" VARCHAR (4) ASCII,

  "EVA_CHAN_USR1" VARCHAR (12) ASCII,

  "EVA_CHAN_ASSET" VARCHAR (20) ASCII,

  "AEID" INTEGER,

  "SPID" INTEGER,

  "F1" VARCHAR (64) ASCII,

  "F2" VARCHAR (64) ASCII,

  "F3" VARCHAR (64) ASCII,

  "F4" VARCHAR (64) ASCII,

  "F5" VARCHAR (64) ASCII,

  "F6" VARCHAR (64) ASCII,

  "F7" VARCHAR (64) ASCII,

  "F8" VARCHAR (64) ASCII,

  "F9" VARCHAR (64) ASCII,

  "F10" VARCHAR (64) ASCII,

  "PTID" INTEGER,

  "F11" VARCHAR (64) ASCII,

  "F12" VARCHAR (64) ASCII,

  "F13" VARCHAR (64) ASCII,

  "F14" VARCHAR (64) ASCII,

  "F15" VARCHAR (64) ASCII,

  "LASTEXP_ROID" INTEGER,

  "PEXID" INTEGER,

  "PEXTS" TIMESTAMP,

  "SIMNR" VARCHAR (24) ASCII,

  "OSVSTR" VARCHAR (16) ASCII,

  "PEXVSTR" VARCHAR (16) ASCII,

  "LAST_ROID" INTEGER,

  "LAST_ROWANN" TIMESTAMP,

  "WWS_START" DATE,

  "WWS_STOP" DATE,

  "CUPDEPOSIT" INTEGER,

constraint SYSPRIMARYKEY primary key ("AMID"),

  foreign key "FK_AUTO_REFE_BINF" ("BFID") references "XYZ"."BINFILES" ("BFID") on delete restrict,

  foreign key "FK_AUTO_REFE_CFG_" ("CTID") references "XYZ"."CFG_TEXT" ("CTID") on delete restrict,

  foreign key "FK_AUTO_REFE_AMTY" ("ATID") references "XYZ"."AMTYP" ("ATID") on delete restrict,

  foreign key "FK_AUTO_REFE_AMGR" ("AMGRID") references "XYZ"."AMGR" ("AMGRID") on delete restrict,

  foreign key "FK_AUTO_REFE_ADHO" ("AEID") references "XYZ"."ADHOCERR" ("AEID") on delete restrict,

  foreign key "FK_AUTO_REFE_SCHA" ("SPID") references "XYZ"."SCHACHT_PLAN" ("SPID") on delete restrict,

  foreign key "FK_AUTO_REFE_BIN1" ("PEXID") references "XYZ"."BINFILES" ("BFID") on delete restrict)

sample 50000 rows

//

create index "AUTO_AMGRID" on "XYZ"."AUTOMAT" ("AMGRID" ASC)

//

create index "EVA_SERNR" on "XYZ"."AUTOMAT" ("EVA_SERNR" ASC)

//

create index "WWSCODE" on "XYZ"."AUTOMAT" ("WWSCODE" ASC)

//

[Former Member]

Hi,

now I'm missing the next table definitions.

Please notice that we need a tabledefinitions which are used in foreign key definitions as well to be able to reproduce this issue.

Thanks,

Christiane

[Former Member]

Hi Christiane,

Is there any chance to send that via personal mail ?

I don't want to publisch my whole Data-Modell here in public.

regards

jph

[Former Member]

Hi Jens-Peter,

we can try if you follow me in the SCN that I can get in direct contact with you.

Regards, Christiane

[Former Member]

Hi,

sounds like a problem with the strategy which is used

Please switch off the following parameter EnableMinMaxAggregationOptimizer = NO

Please send us the table and index definition as well.

Regards, Christiane

[Former Member]

Thanks Christiane,

That solves the Problem.

nevertheless I provide the table and Index definition here.

regards

jph

---------

create table "XYZ"."READOUT"(

  "ROID" INTEGER not null,

  "AMID" INTEGER,

  "ROWANN" TIMESTAMP,

  "WANN" TIMESTAMP,

  "WER" INTEGER,

  "STATE" CHAR (1) ASCII,

  "EVA_RONUM" INTEGER,

  "EVA_ROTERM" VARCHAR (20) ASCII,

  "EVA_PROTERM" VARCHAR (20) ASCII,

  "EVA_USR1" VARCHAR (12) ASCII,

  "EMPTY" INTEGER,

  "EVA_SYSTIME" VARCHAR (6) ASCII,

  "EVA_SYSDATE" VARCHAR (8) ASCII,

  "EVA_PROTIME" VARCHAR (6) ASCII,

  "EVA_PRODATE" VARCHAR (8) ASCII,

  "EVA_ROTIME" VARCHAR (6) ASCII,

  "EVA_RODATE" VARCHAR (8) ASCII,

  "EXPORTED" CHAR (1) ASCII,

  "EXPORT_TS" TIMESTAMP,

  "TSERNR" VARCHAR (16) ASCII,

  "MSERNR" VARCHAR (20) ASCII,

constraint SYSPRIMARYKEY primary key ("ROID"),

  foreign key "FK_READ_REFE_AUTO" ("AMID") references "XYZ"."AUTOMAT" ("AMID") on delete restrict)

sample 5 percent

//

create index "RO_AMID" on "XYZ"."READOUT" ("AMID" ASC)

//

create index "RO_AM_ROWANN_EMPTY" on "XYZ"."READOUT" ("AMID" ASC,"ROWANN" DESC,"EMPTY" ASC)

//

create index "RO_NURSTATE" on "XYZ"."READOUT" ("STATE" ASC)

//

create index "RO_ROWANN" on "XYZ"."READOUT" ("ROWANN" DESC)

//

create index "RO_STATE" on "XYZ"."READOUT" ("AMID" ASC,"STATE" ASC)

//

create index "RO_WANN" on "XYZ"."READOUT" ("WANN" ASC)

//