select count() works, select max(id) doesn't

[former_SQLA_member1694875]

Hi,
Our customer has a strange problem with a very tiny table (it has only 100-200 rows):

CREATE TABLE test (
    id BIGINT PRIMARY KEY DEFAULT AUTOINCREMENT,
    uid BIGINT NULL
)

These statements do work immediately:

SELECT count() from test;
SELECT min(id) from test;
SELECT FIRST id FROM test ORDER BY id DESC;

This statement doesn't, i. e. runs "forever":

SELECT max(id) from test;

Execution plan for this statement:

( Plan [B]
  ( SingleRowGroupBy 
    ( RowLimit 
      ( IndexOnlyScan test test**(R) )
    )
  )
)

This happens only in one DB instance, other instances work as expected.
The table and its primary key were validated without errors.
Isolation_level is always 0, so locking problems should not be the reason.

Any thoughts, what else should we check or do to make it work?

Version - SA11.

Thanks.

[MarkCulp]

I think I would run a VALIDATE INDEX to determine if the index is corrupted. See http://dcx.sap.com/index.html#1101/en/dbreference_en11/validate-statement.html*d5e50521

[Breck_Carter]

[gentle mockery]

What is it about "The table and its primary key were validated without errors" that doesn't do what VALIDATE INDEX might accomplish? 🙂

[/gentle mockery]

[MarkCulp]

Ah, missed that. My thought was (is) that the max operator will do a reverse lookup on the index (on 'id') to find the largest value and if the index was corrupted it could spin forever trying to find the entry. It could still be that the index is corrupted and the validation process is not detecting it.

As an experiment, it could be insightful to drop the index on 'id' and then recreate it, and see if that resolves the problem.

[VolkerBarth]

That is, to run

ALTER TABLE test DROP PRIMARY KEY;
ALTER TABLE test ADD PRIMARY KEY (id);

which should re-create the index automatically, right?

[Breck_Carter]

Yabbut that might not be foreign-key-friendly...

...sheesh but I can be an a*****e sometimes 🙂

[Former Member]

... being a little Drew-ish this a.m. I C

But the suggestion is still a valid one. The might be an undetected corruption in the compressed index here.

There is also the possiblity of a compressed index issue addressed in 11.0.1#2566+

[former_SQLA_member1694875]

The problem was solved by this experiment (i. e. primary key was dropped and recreated again). Thank you for the hint. So it turns out that we can't trust the validation.
P. s.: if this comment was an answer I could accept it.