> > > SELECT key FROM t WHERE key ~* '^somestring'
> > >
> > > returns no tuples and explain says it will use the index on key. Why is th
> is?
> >
> > That's strange. It should be seq scan in this case?
>
>I forgot to mention, that if 'somestring' is all uppercase, everything works
>(the key field in the table is all uppercase). It still says index scan will
>be used.
Hmm... I am totally confused by this. Sould be a bug.
>To summarize the problem. If key contains (equivalent cyrillic letters) 'ABC',
>'ABCD', 'DAB' and 'ABX' and the query is:
>
>SELECT key FROM t WHERE key ~* '^AB';
>
>index scan will be used and the correct tuples ('ABC', 'ABCD' and 'ABX') will
>be returned. If the query is
>
>SELECT key FROM t WHERE key ~* '^ab';
>
>index scan will be used and no tuples will be returned. With the query
Should be bug.
>SELECT key FROM t WHERE key ~* 'ab';
>
>sequential scan will be used and the correct tuples will be returned (all of
>the above).
Seems correct result.
> > Can you test following case:
> >
> > SELECT key FROM t WHERE key ~* '^Xsomestring'
> >
> > where X is one of an ASCII character.
>
>Explain says it will use sequential scan and if I insert proper key in the
>table it will be returned.
Expected result.
>From line 5388 of parser/gram.y: (strcmp(opname,"~*") == 0 && isalpha(n->val.val.str[pos])))
I suspect isalpha() does not return true if n->val.val.str[pos] is a
non ascii char. Probably that's why
SELECT key FROM t WHERE key ~* '^somestring'
does not work.
If its argument is a cyrillic char and cyrillic locale enabled then
isalpha() should return true. Can you check this?
Or: (strcmp(opname,"~*")
== 0 && isalpha((unsigned char)n->val.val.str[pos])))
works for you?
--
Tatsuo Ishii
