Thread: [BUG REPORT] Unexpected cast while matching CHAR type
Hi, developer team,
We find an unexpected behaviour about query optimizer in PostgreSQL.
More specifically, char type will be transformed into text incorrectly which leads to an unexpected result.
Next, we provide a detial process to reproduce this behaviour as follows:
First step, it initializes a database test:
```sql
-- Create a database and its table:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
\c test;
CREATE TABLE t (
a CHAR(2) DEFAULT NULL
);
-- Load data:
insert into t values ('a');
```
Second step, it executes a queries Q1 on the database test.
```sql
select count(*) from t where a like (select a from t); -- Q1
```
### expect to see
count
-------
1
(1 row)
### see instead
count
-------
0
(1 row)
### query plan
```sql
explain select count(*) from t where a like (select a from t); -- explain Q1
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=71.02..71.03 rows=1 width=8)
InitPlan 1 (returns $0)
-> Seq Scan on t t_1 (cost=0.00..30.40 rows=2040 width=12)
-> Seq Scan on t (cost=0.00..40.60 rows=10 width=0)
Filter: (a ~~ ($0)::text)
(5 rows)
```
According to [1], values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way.
Thus, InitPlan 1 returns results with padding spaces, which is confirmed by our experiments.
However, according to [1], trailing spaces are removed when converting a character value to one of the other string types.
Meanwhile, trailing spaces are semantically significant in character varying and text values, and when using pattern matching, that is LIKE and regular expressions.
Therefore, casting $0 as text type should be an unexpected behaviour. If $0 is not casted, the result set can not be empty.
Further, we replace LIKE operator with = operator, and find that query optimizer does not cast $0.
The query plan is as follows.
```sql
explain select count(*) from t where a = (select a from t);
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=65.92..65.94 rows=1 width=8)
InitPlan 1 (returns $0)
-> Seq Scan on t t_1 (cost=0.00..30.40 rows=2040 width=12)
-> Seq Scan on t (cost=0.00..35.50 rows=10 width=0)
Filter: (a = $0)
(5 rows)
```
We are confused that why casting $0 for LIKE operator instead of = operator, which leads to unexpected results, according to manual.
We test different versions of PostgreSQL and find that this hebaviour exists in all versions we test, including:
```sql
version
------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16beta2 (Debian 16~beta2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
version
---------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
version
------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.16 (Debian 11.16-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
```
[1] https://www.postgresql.org/docs/15/datatype-character.html
Best regards!
Siyang Weng
"=?utf-8?B?57+B5oCd5oms?=" <52275903002@stu.ecnu.edu.cn> writes:
> We find an unexpected behaviour about query optimizer in PostgreSQL.
> More specifically, char type will be transformed into text incorrectly which leads to an unexpected result.
This is not a bug. There is no variant of the LIKE operator that accepts
char(n) as the pattern argument:
=# \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)
Therefore, the parser (not the optimizer) has little choice but to
insert an implicit cast of the sub-select's output to text; the
only other alternative is to throw an error.
We could make this case behave as you expect by adding a variant
of ~~ that takes char(n) on the RHS, but I'm disinclined to do so
because of the high probability that it would cause unwanted
behavioral changes in other cases. Moreover, given that char(n)'s
trailing spaces are considered insignificant, is preserving them in
a LIKE pattern even the right thing to do? I don't think that's
exactly an open-and-shut question. (Arguably, the right fix is
not to add another ~~ variant but to get rid of the char(n) versus
text one, so that both sides get space-stripped in this case.)
The short answer is that char(n) is a legacy type that you should
not use, precisely because it has such odd behavior around trailing
spaces. We support it for pro-forma compliance with the SQL
standard, but consider it deprecated. That being the case, there's
not a lot of interest in changing edge cases in its behavior.
We'd be more likely to get complaints than kudos for doing so.
regards, tom lane