LIKE, CHAR(), and trailing spaces - Mailing list pgsql-hackers

I found a little LIKE/CHAR() surprise --- below is a table and query
against a CHAR(10) field:
test=> CREATE TABLE test (x char(10));CREATE TABLE
test=> INSERT INTO test values ('hi');INSERT 0 1
test=> SELECT * FROM test WHERE x = 'hi';     x------------ hi(1 row)

The above works because both sides are converted to 'bpchar';  explain
shows that:
test=> EXPLAIN SELECT * FROM test WHERE x = 'hi';                      QUERY
PLAN------------------------------------------------------Seq Scan on test  (cost=0.00..33.12 rows=9 width=14)
Filter:(x = 'hi'::bpchar)                      ^^^^^^(2 rows)
 

The following does not work:test=> SELECT * FROM test WHERE x LIKE 'hi'; x---(0 rows)


It seems LIKE is considering the trailing CHAR(10) field spaces as
significant, even though our documentations says:
   Values of type <type>character</type> are physically padded   with spaces to the specified width <replaceable>n</>,
andare   stored and displayed that way.  However, the padding spaces are   treated as semantically insignificant.
Trailingspaces are
 
--> disregarded when comparing two values of type <type>character</type>,   and they will be removed when converting a
<type>character</type>value   to one of the other string types.  Note that trailing spaces   <emphasis>are</>
semanticallysignificant in   <type>character varying</type> and <type>text</type> values.
 

It says trailing spaces are not significant for character comparisons
--- the real question is whether LIKE is a comparison.  Obvioiusly '='
is a comparison, but the system does not treat LIKE as a comparison in
terms of trailing spaces.  Is that desired behavior?

I did an EXPLAIN on the query and found '~~' was being used and 'hi' was
being converted to text:
test=> explain select * from test where x like 'hi';                      QUERY
PLAN------------------------------------------------------Seq Scan on test  (cost=0.00..33.12 rows=9 width=14)
Filter:(x ~~ 'hi'::text)                     ^^       ^^^^(2 rows)
 

so I then checked psql \do to see what operators there were for ~~:
test=> \do ~~                                     List of operators   Schema   | Name | Left arg type | Right arg type
|Result type |
Description------------+------+---------------+----------------+-------------+-------------------------pg_catalog | ~~
| bytea         | bytea          | boolean     | matches LIKE expression
 
-->     pg_catalog | ~~   | character     | text           | boolean     | matches LIKE expression pg_catalog | ~~   |
name         | text           | boolean     | matches LIKE expression pg_catalog | ~~   | text          | text
| boolean     | matches LIKE expression(4 rows)
 

The one marked matches the arguments so it seems the comparison being
done is not character and character, but character and text.

I realize trim() could be used to get the desired behavior, but is our
behavior consistent?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +