[BUG REPORT] Unexpected cast while matching CHAR type - Mailing list pgsql-bugs
| From | 翁思扬 |
|---|---|
| Subject | [BUG REPORT] Unexpected cast while matching CHAR type |
| Date | |
| Msg-id | tencent_57E520E634A739CC1F11E471@qq.com Whole thread Raw |
| Responses |
Re: [BUG REPORT] Unexpected cast while matching CHAR type
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
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
pgsql-bugs by date: