Hi everyone,
I was surprised by the behavior of LIKE and the CHAR type. Consider
the following statements:
CREATE TABLE t0(c0 CHAR(2)) ;
INSERT INTO t0(c0) VALUES('a');
SELECT * FROM t0 WHERE c0 LIKE c0; -- expected: fetches the row,
actual: does not fetch the row
According to the docs, CHAR values are padded, and the padding is
supposed to be semantically significant in "LIKE and regular
expressions" [1], which is why I would expect the query to be
equivalent to the following:
SELECT * FROM t0 WHERE 'a ' LIKE 'a '; -- fetches the row
It seems that the trailing spaces on the right hand side are
disregarded, but not on the left hand side:
SELECT 'a' LIKE c0 FROM t0; -- unexpected: TRUE
SELECT 'a ' LIKE c0 FROM t0; -- unexpected: FALSE
SELECT c0 LIKE 'a' FROM t0; -- FALSE
SELECT c0 LIKE 'a ' FROM t0; -- TRUE
Is this behavior expected or is this a bug? By the way, this is unlike
what happens in MySQL and SQLite3, where the row would be fetched.
[1] https://www.postgresql.org/docs/11/datatype-character.html
Best,
Manuel
