Skip to Content
0
Former Member
Nov 28, 2013 at 11:31 AM

Wrong Results in simple max() Statement

33 Views

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 ?