Hi all, I have interesting problem with compound index.

I have records in one table divided in a groups by field TYP
and each record has its name field TRIDNAZ

TRIDNAZ is varchar(40) character set WIN1250 collation PXW_CSY
TYP is smallint

I had 2 indexes - on TRIDNAZ only, and on TYP, TRIDNAZ

And I have query

select * from FOND where TYP = :typ and TRIDNAZ starting with :text order by TRIDNAZ;

when I set text = 'C' the result set was empty
when I set text = 'CA' the result set was filled with records starting with 'CA'

I can't found the reason why was 'C' empty.

I saw the plan and the server use compound index - TYP, TRIDNAZ

When I dropped it, server us TRIDNAZ only index and - wow - 'C' records exists.

I think there is some foult in compound index smallint + varchar(WIN1250) and characte C
It could be the reason that in Czech languague is 'Ch' one character not two.

When I change it to two separate indexes - TYP and TRIDNAZ
Server use both of them and works correctly.

Please correct this error - it is tested on 2.1 and 2.5 on windows

Tom