Re: is this a bug or I am blind?

Csaba Nagy wrote:

>[snip]
>
>
>>even is that is true i think you need the "comodin characters" (ie: %.
>>_) to make "like" behave different from simple comparisons
>>
>>
>
>Not entirely true, if the database was initialized in a different locale
>than C, then the direct comparison will probably go for an index on
>username, while "like" will not. Which points to a possible index
>corruption... which might be interesting for the developers to
>investigate, but I would guess a reindex will solve the problem for the
>OP if he has it urgent...
>
>
I thought that it may be a locale problem, but:
- look at my 3rd query
- potyty doesn't contain special chars

# EXPLAIN ANALYZE select * from common_logins where username = 'potyty';
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using common_logins_username_idx on common_logins
(cost=0.00..4.30 rows=1 width=47) (actual time=0.056..0.056 rows=0 loops=1)
Index Cond: ((username)::text = 'potyty'::text)
Total runtime: 0.109 ms
(3 rows)

online=# EXPLAIN ANALYZE select * from common_logins where username like
'potyty';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on common_logins (cost=0.00..63833.88 rows=1 width=47)
(actual time=180.333..262.492 rows=3 loops=1)
Filter: ((username)::text ~~ 'potyty'::text)
Total runtime: 262.551 ms
(3 rows)

I tried it in two databases (dump and load to another one), so I don't
think that we have corrupted indexes.

I can try on a newer version of postgresql on another server.

By the way, if this is a bug then it's a serious one. We have it in
production environment.

Mage