From: | Tatsuhito Kasahara <kasahara(dot)tatsuhito(at)oss(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | make_greater_string() does not return a string in some cases |
Date: | 2010-06-21 10:02:03 |
Message-ID: | 4C1F389B.1010606@oss.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi !
make_greater_string() does not return a string when some UTF8 strings
set to str_const.
# Especially UTF8 strings which contains 'BF' in last byte.
Because make_greater_string() only try incrementing the last byte of
the string, and not try same test for upper bytes.
Therefore, some queries which contains "LIKE '<contains 'BF' in last byte>%'"
can not perform (Btree's) index-scan.
# Or may be nearly full-index-scan.
# See follwing example.
===============================================================================
'西' (Japanese Letter) : 0xE8A5BF
[client : UTF8 ⇔ server : EUC_JP]
=# EXPLAIN ANALYZE SELECT * FROM test2 WHERE name LIKE '西%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using test2_name on test2 (cost=0.00..8.28 rows=1 width=3) (actual time=0.077..0.078 rows=1 loops=1)
Index Cond: ((name >= '西'::text) AND (name < '誠'::text)) <-- Index-scan is chosen
Filter: (name ~~ '西%'::text)
Total runtime: 0.110 ms
(4 rows)
[client : UTF8 ⇔ server : UTF8]
=# EXPLAIN ANALYZE SELECT * FROM test2 WHERE name LIKE '西%';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on test2 (cost=0.00..1693.01 rows=1 width=4) (actual time=22.598..22.599 rows=1 loops=1)
Filter: (name ~~ '西%'::text) <-- Seq-scan is chosen !
Total runtime: 22.626 ms
(3 rows)
===============================================================================
Attached patch solve above problem.
Best regards,
--
NTT OSS Center
Tatsuhito Kasahara
Attachment | Content-Type | Size |
---|---|---|
make_greater_string.patch | text/plain | 1.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Yuhui | 2010-06-21 11:40:19 | BUG #5515: Memory grows up problem |
Previous Message | Heikki Linnakangas | 2010-06-21 08:05:43 | Bug in PL/pgSQL FOR cursor variant |