Skip site navigation (1) Skip section navigation (2)

make_greater_string() does not return a string in some cases

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 (view raw or flat)
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: make_greater_string.patch
Description: text/plain (1.2 KB)

Responses

pgsql-bugs by date

Next:From: YuhuiDate: 2010-06-21 11:40:19
Subject: BUG #5515: Memory grows up problem
Previous:From: Heikki LinnakangasDate: 2010-06-21 08:05:43
Subject: Bug in PL/pgSQL FOR cursor variant

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group