I disagree about it being a feature request.
I was only using substr to demonstrate the problem.
Here is the problem without using any functions:
create table x (a char(1));
insert into x values ('x');
insert into x values (' ');
select length(a) from x;
1
0
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, October 29, 2021 3:40 PM
To: David@calascibetta.com
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: FW: BUG #17258: Unexpected results in CHAR(1) data type
Subject: RE: BUG #17258: Unexpected results in CHAR(1) data type
Ok, but my example was just a simplified version of what is going on.
The actual problem stems from a CHAR(1) column data type that is behaving the same way.
I was just trying to create a super-simple example of the problem.
It still seems to me that a CHAR(1) should never be zero length, regardless of how it's implemented.
This qualifies as a feature request, not a bug. One could write a version of substr that does what you expect (it probably wouldn't be named substr though) and takes in a character data type. It's just no one has, nor is likely to. Thus you are stuck using versions that take in text and you get the char-to-text casting side effects.
If you do octet_length(' ':: character(1)) it will return 1, not zero. So it indeed has a length one.