Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mgZtw-0001FX-ON for pgsql-bugs@arkaria.postgresql.org; Fri, 29 Oct 2021 21:59:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mgZtv-00019h-Ak for pgsql-bugs@arkaria.postgresql.org; Fri, 29 Oct 2021 21:59:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mgZ4m-0003aL-H9 for pgsql-bugs@lists.postgresql.org; Fri, 29 Oct 2021 21:06:13 +0000 Received: from mail225c38.carrierzone.com ([66.175.56.225] helo=mail171c38.carrierzone.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mgZ4g-000775-4x for pgsql-bugs@lists.postgresql.org; Fri, 29 Oct 2021 21:06:10 +0000 X-Authenticated-User: david@calascibetta.com DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=carrierzone.com; s=mailmia; t=1635541563; bh=fFpgxJKUP6sTFZnEvXRUHO6O/itjobk9YZaicojAdjs=; h=Reply-To:From:To:Cc:References:In-Reply-To:Subject:Date:From; b=M+tLaM2SqveBi2HsPVInx95Zqz+xdwDOmXY0C4rImuW154SqVNSVtNq6M2DoJszo2 RMMfvlHTi+9SlzSUlmCmeZoh/tXrQzFQdrkTydcU0r1anw7fi5IuHn5V6AD42ZYsry ABqvynHN3DwFhVYhx0t4iNoJHwXjf7m+UYPvpP48= Feedback-ID: david@calascibe Received: from Workstation (99-16-240-20.lightspeed.mrgvil.sbcglobal.net [99.16.240.20]) (authenticated bits=0) by mail171c38.carrierzone.com (8.14.9/8.13.1) with ESMTP id 19TL614I016375; Fri, 29 Oct 2021 21:06:03 +0000 Reply-To: From: "David M. Calascibetta" To: "'David G. Johnston'" Cc: "'PostgreSQL mailing lists'" References: <17258-a7c164c5fb955221@postgresql.org> <19D58355-E7C3-47D7-9C87-51EC751AE2BB@enterprisedb.com> <003701d7ccfb$c2a85bb0$47f91310$@calascibetta.com> In-Reply-To: Subject: RE: FW: BUG #17258: Unexpected results in CHAR(1) data type Date: Fri, 29 Oct 2021 16:06:02 -0500 Message-ID: <004b01d7cd08$c8f54a70$5adfdf50$@calascibetta.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004C_01D7CCDE.E01F4270" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQLB1uYWs1aJVPkX1BZhIF63SkMLgwH/iU5GAnFecvcCzbCF16ncboDA Content-Language: en-us X-CTCH-RefID: str=0001.0A742F18.617C623B.0060:SCFSTAT57938998,ss=1,re=-4.000,recu=0.000,reip=0.000,cl=1,cld=1,fgs=0 X-CTCH-VOD: Unknown X-CTCH-Spam: Unknown X-CTCH-Score: -4.000 X-CTCH-Rules: X-CTCH-Flags: 0 X-CTCH-ScoreCust: 0.000 X-CSC: 0 X-CHA: v=2.4 cv=S8G6fKgP c=1 sm=1 tr=0 ts=617c623b a=4+o7z3fL63oxpBr+4DDEQg==:117 a=4+o7z3fL63oxpBr+4DDEQg==:17 a=DAwyPP_o2Byb1YXLmDAA:9 a=pGLkceISAAAA:8 a=pCOydsRIAAAA:8 a=epTmVMiNAAAA:8 a=R_nBC5pU0HhiJKkIWF0A:9 a=QEXdDO2ut3YA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=R7qgkMUvHdYwCrOHTqkA:9 a=E05M5hZHz7K5ByM9:21 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10 a=frz4AuCg-hUA:10 a=4YKM6X3TZtkG47b_JkB7:22 a=ndEWmUVY6Yapc0oHF_P4:22 X-Origin-Country: US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_004C_01D7CCDE.E01F4270 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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: =20 create table x (a char(1)); insert into x values ('x'); insert into x values (' '); select length(a) from x; 1 0 =20 =20 =20 =20 =20 =20 From: David G. Johnston =20 Sent: Friday, October 29, 2021 3:40 PM To: David@calascibetta.com Cc: PostgreSQL mailing lists Subject: Re: FW: BUG #17258: Unexpected results in CHAR(1) data type =20 On Fri, Oct 29, 2021 at 1:17 PM David M. Calascibetta = > wrote: 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. =20 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. =20 If you do octet_length(' ':: character(1)) it will return 1, not zero. = So it indeed has a length one. =20 David J. ------=_NextPart_000_004C_01D7CCDE.E01F4270 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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

 

On Fri, = Oct 29, 2021 at 1:17 PM David M. Calascibetta <david@calascibetta.com> = wrote:


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.

 

David = J.

------=_NextPart_000_004C_01D7CCDE.E01F4270--