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