Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLIMw-0006AS-Q0 for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 02:19:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLIMv-0005UL-Is for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 02:19:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLI5w-0007V0-HB for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 02:02:08 +0000 Received: from sonic301-20.consmr.mail.ir2.yahoo.com ([77.238.176.97]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLI5o-0008Kx-TE for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 02:02:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1556589719; bh=R5JI3TCpsjFoIO2A+BBq4G+babo7dxdaWtr9aNCcj7o=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=Te3pW8F6R5H2HiOJi1/EcU+hpd6OikWZFZg8uXkfIh6qN1sFNwZ+D1QLlx1TdMnDNevA60IZUq4nOPL24qZkMKGXLiYJxWePDFA+v1Pq7r08/gCU8Zw0CDfBnBAoo4sqYE+IpgeimuLlTnIqlVAc1awpewEQCnXyTfvNlqr7FI/gU0VbkSF/+Buch3KxlvTCr0sBIMfQ7XAMC5SidD6QqCX26o0w+vByWxllieSbAM8SNZQdU1Az8pRatK4Lv2eqLxVzLADjAQTCVYNxFaj0fdvVWJua8AfKdQNO5hIcA6afRBN+daJZ54mjYm7gPMj/FUxvmTHuYsj5WMHGaa0x3w== X-YMail-OSG: 0A6mv9YVM1k381oAMVb1V6Pe2mhmOQIuKx_ZVmsLeEFr8jT8lijU6bQobdzdH3l AqfzuoFmDKLpKGP4CQMWkJ18AD8wpD6pDKNDHVDCBsGuuWY2UoshAOhDDtA6k4sCwDRu5G8kv_z0 a5dqmQjXq3WKadC0mk5PP3dryPVVHMxX9oNCEoD6KqeuaYwNRBuOOYlmwFD1rDcBNC1CzoY8HMeN 8Xdh57eVgRrgUJ.7ybecuqF5w94nLT0yNJ.0OO_uun8lv9fed1275bH0qRQLJUS66H68kM9qj1cE AX8JTvM0ab_vmgYO49EZzMIpFpJLC2SmAniDnCZQSmCu._va4UpxVUuwalmRp8dvrgpe5JIq5K5r W1L.jUXAN3GNmoQWyz5FI0u.IdzJgHAhkAyGPrFloNeQzBAlxZmCctLARvuBZW4UP3WtRoBIvJbp hS5WeCS.PubM7wEm_t1Zqk1nGZ0ZUgreJ.1Yiwq_ECrcF_xk4mr2HxM1l3u662JI.iaBMpsvjvrq hOeyCeJF7MQpW9Vuo0hn6O6Ei3pACCT03thdNRcmnxfhsN2OLeGnJLxW3bP.H_FP.R4R_r5Nkado KxJ9F44ebWjvuHtQYR5qt4fMwtH8tWysDEWxCmCEK7MGpNmLjmG_XBUx43DJuhge82uy64lndpSJ _Yz7edHbd5oQOKJxb0s1TvmP2PHodcJ6BZe83yXLNnpQ6sib09o1gAHveKB.4fKKYBuzRRuFOQDt QETE1fWaWeV6jPBsSDQJATwKNGLwXfMVL6xmJHGBFitZ5Q5aCVfdA06TR4tR4cEfl2.xrPGJeV8k 3t0oywIs6LwpBJlKcTKEt3uVjcIA7HXZLFS498NYwIvun3jRcQ8HQCWYC.kXh79RLaRwJc.sOm7p wTxpuyjkhrajO0VEQUS6EHbOCIA49TLRjzWLxmv9p6qEMw.TDtguIYZrnU0j_bTd54mlkmiOrCT2 8PJ03nHBCMorXTOniLe6F_ltoHXkhTHMn2rxRwzPdypGaCAFnI3d4WNkH9QPtDHpNe2_qVfS5nSQ GTMQugLRb4AdyR8INiUhkvOY1Dsc7XhoTUekHLFL7.ApUD0wD_L08IfJvIskG68gIaaLPBKmgDUw KfOch5eWy2vLdvD2b_hzC7GUpB7vlcbLpxr9Kg4T21ow8H_TH8GFDIQhwp9LU2DaQdk7eDfPQX9X BfBztRKVGDskplT83kxDOHQ-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.ir2.yahoo.com with HTTP; Tue, 30 Apr 2019 02:01:59 +0000 Date: Tue, 30 Apr 2019 02:01:55 +0000 (UTC) From: Daniil Treshchin To: Bruce Momjian , "David G. Johnston" Cc: pgsql-docs@lists.postgresql.org Message-ID: <1625110667.3383282.1556589715196@mail.yahoo.com> In-Reply-To: <1767763017.3390110.1556587876525@mail.yahoo.com> References: <155639681797.1368.1856073093791885251@wrigleys.postgresql.org> <20190429231305.ra5z5bapif353vdx@momjian.us> <1767763017.3390110.1556587876525@mail.yahoo.com> Subject: Re: COALESCE doccumentation MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3383281_1461677197.1556589715192" X-Mailer: WebService/1.1.13554 YMailNorrin Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:66.0) Gecko/20100101 Firefox/66.0 Content-Length: 17436 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3383281_1461677197.1556589715192 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Oh, I was just playing a little bit with it to make sure how it works with= some nesting involved. SELECT COALESCE(NULL, '12', '3', 1.2, (SELECT (CASE WHEN profile_code =3D '= abc' THEN '2d' ELSE '2d' END) FROM profile LIMIT 1));=20 -- failSELECT COALESCE(NULL, '12', '3', 1.2, (SELECT (CASE WHEN profile_cod= e =3D 'abc' THEN '2d' ELSE '2d' END) FROM profile LIMIT 1) :: numeric);=C2= =A0 -- OK! As said in the doc, COALESCE only evaluates the arguments that are needed Agreed. I take my words back about that statement about evaluation, but the= re is still a problem in here as you can see from this confusion. It evalua= tes the type of the s. Thank you, On Monday, April 29, 2019, 6:31:16 PM PDT, Daniil Treshchin wrote: =20 =20 Hi Bruce, David. Comment: "Without further elaboration as to the shortcomings" I provided a clear description on why, but for some reason it was missed in= this thread.=20 Here it is: The COALESCE(, ..., ) scans the list of= s from left to right, determines the highest data type i= nthe list and returns the first non-NULL in the list, casting it to the hig= hest data type selected in the previous step, but if all the s are NULL, the result is NULL. In case when any of the s in the list cannot be evaluated to the highest data type then except= ion is thrown. Question: What is a highest data type? The first data type? This is not the first datatype but there is an order, for example: numeric integer character varying text If you mix numeric, integers and text in any order, then it will be evaluat= ed to numeric;If you mix integers and text, then it will be evaluated to in= teger; Again, from my example: SELECT COALESCE(NULL, '12', 3, 1, 1); -- OK! evaluated to integer SELECT COALESCE(NULL, '12.2', 3.2, '1', 1.1); -- OK! evaluated to numeric SELECT COALESCE(NULL, '12', 3, 1.2, 1);=C2=A0 -- OK! evaluated to numeric SELECT COALESCE(NULL, '1d2', 3, 1, 1); -- error SELECT COALESCE(NULL, '12', 3, '1d', 1); -- cast error The documentation says nothing about it and the description is obscure.What= the docs are not saying: 1) COALESCE scans the list of s2) COALESCE determines the= highest data type in the list of s3) COALESCE casting t= he first non-NULL to the highest data type4) COALESCE evaluates every s to the highest datatype selected in step (2) In addition the doc says:"Like a CASE expression, COALESCE only evaluates t= he arguments that are needed to determine the result; that is, arguments to= the right of the first non-null argument are not evaluated." This is so not true according to the doc and just breaks a simple test.=20 SELECT COALESCE(NULL, '12', '3', '1.2', (SELECT profile_code FROM profile L= IMIT 1));=C2=A0 -- OK! evaluates to character varying SELECT COALESCE(NULL, '12', 3, '1.2', (SELECT profile_code FROM profile LIM= IT 1));=C2=A0 -- ERROR! failed to evaluate to the highest datatype - intege= r Thank you, On Monday, April 29, 2019, 4:46:39 PM PDT, David G. Johnston wrote: =20 =20 On Mon, Apr 29, 2019 at 4:13 PM Bruce Momjian wrote: On Sat, Apr 27, 2019 at 08:26:57PM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: >=20 > Page: https://www.postgresql.org/docs/11/functions-conditional.html > Description: >=20 > The documentation says: >=20 > "The COALESCE function returns the first of its arguments that is not nul= l. > Null is returned only if all arguments are null. It is often used to > substitute a default value for null values when data is retrieved for > display, for example" >=20 > Which is NOT TRUE. In addition, it is obscure and misleading. >=C2=A0 > Try, for example.=20 >=20 > SELECT COALESCE(NULL, '12', 3, 1, 1); -- OK! > SELECT COALESCE(NULL, '12.2', 3.2, '1', 1.1); -- OK! > SELECT COALESCE(NULL, '1d2', 3, 1, 1); -- error > SELECT COALESCE(NULL, '12', 3, '1d', 1); -- cast error What is wrong about the existing description?=C2=A0 That it doesn't mention mixing data types? The OP's example demonstrates a more basic confusion about types that needs= to be learned independent of COALESCE. The literals surrounded with single quotes have an unknown type as written.= =C2=A0 Since other literals are plain numbers those are typed as integers.= =C2=A0 Since all arguments to COALESCE need to be the same type (same appli= es to other functions or things like UNION) the untyped literal is coerced = to the known typed arguments - which fails when the untyped literals are no= t pure numbers. There is a concept of parent types - though I cannot find the rules for the= m at the moment - but basically most things devolve toward text; the issue = here is there isn't any "devolving" (parent) involved - its a straight assi= gnment of the known integer type to the untyped literals. Without further elaboration as to the shortcomings of the current documenta= tion its hard to add more material that isn't this section of the documenta= tion's place to teach. David J. =20 ------=_Part_3383281_1461677197.1556589715192 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Oh, I was just playing a little bit with it to make sure how i= t works with some nesting involved.

SELE= CT COALESCE(NULL, '12', '3', 1.2, (SELECT (CASE WHEN profile_code =3D 'abc'= THEN '2d' ELSE '2d' END) FROM profile LIMIT 1));
-- fail
SELECT COALESCE(NULL, '12', '3'= , 1.2, (SELECT (CASE WHEN profile_code =3D 'abc' THEN '2d' ELSE '2d' END) F= ROM profile LIMIT 1) :: numeric);  -- OK!

<= div> As said in the doc, COALESCE only evaluates the arguments that are needed

Agreed. I take my words back about t= hat statement about evaluation, but there is still a problem in here as you= can see from this confusion. It evaluates the type of the <value expres= sion>s.

Thank = you,
=20
On Monday, April 29, 2019, 6:31:16 PM PDT, Daniil Tresh= chin <t.dnil@yahoo.com> wrote:


Hi Bruce, David.

Comment: "Without further elaboration as to the shortcomings"
I provided a clear description on why, but f= or some reason it was missed in this thread.
=
Here it is:

The COALESCE(<value expression>, ..., <value expressio= n>) scans the list of <value expression>s from left to right, dete= rmines the highest data type in the list and returns the first non-NULL in the list, casting it to the high= est data type selected in the previous step, but if all the <value expre= ssions>s are NULL, the result is NULL. In case when any of the <value= expressions>s in the list cannot be evaluated to the highest data type = then exception is thrown.

Question:= What is a highest data type? The first data type?

This is not the fir= st datatype but there is an order, for example:

<= /div>
numeric
integer
character varying
text

If you mix numeric, integers and text in any order= , then it will be evaluated to numeric;
If you mix i= ntegers and text, then it will be evaluated to integer;

Again, from my example:

SELECT COALESC= E(NULL, '12', 3, 1, 1); -- OK! evaluated to integer
SELECT COALESCE(NULL, '12.2', 3.2, '1', 1.1); -- OK! evaluated to numeric
SELECT COALESCE(NULL, '12', 3, 1.2, 1);&= nbsp; -- OK! evaluated to numeric
SELECT COALESCE(NULL, '1d2', 3, 1, 1); -- error
SELECT COALESCE(NULL, '12', 3, '1d', 1); -- cast error

The documentation says nothing about= it and the description is obscure.
What the docs are not saying:=

1) COALESCE scans the list o= f <value expression>s
2) COALESCE= determines the highest data type in the list of <value expressions>s=
3) COALESCE casting the first non-NULL to the= highest data type
4) COALESCE evaluates every <value expression>s to th= e highest datatype selected in step (2)<= /span>

In addition the doc says:=
"Like a CASE expression, COALESCE only evaluates the arguments that are needed to determine the result;=20 that is, arguments to the right of the first non-null argument are not=20 evaluated."

This is so not true according to the doc and ju= st breaks a simple test.

SELECT COALESCE(NULL, '12', '3', '1.2', (SELECT profile_code = FROM profile LIMIT 1));  -- OK! evaluates to character varying
SELECT COALESCE(NULL, = '12', 3, '1.2', (SELECT profile_code FROM profile LIMIT 1));  -- ERROR= ! failed to evaluate to the highest datatype - integer

Thank you,
=20
=20
On Monday, April 29, 2019, 4:46:39 PM PDT, David G. Joh= nston <david.g.johnston@gmail.com> wrote:


On Mon, Apr 29, 2019 at 4:13 PM Bruce Momjian <bruce@momjian.us> wrote:
On Sat, Apr 27,= 2019 at 08:26:57PM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website: >
> Page: https://www.pos= tgresql.org/docs/11/functions-conditional.html
> Description:
>
> The documentation says:
>
> "The COALESCE function returns the first of its arguments that is not = null.
> Null is returned only if all arguments are null. It is often used to > substitute a default value for null values when data is retrieved for<= br clear=3D"none"> > display, for example"
>
> Which is NOT TRUE. In addition, it is obscure and misleading.
> 
> Try, for example.
>
> SELECT COALESCE(NULL, '12', 3, 1, 1); -- OK!
> SELECT COALESCE(NULL, '12.2', 3.2, '1', 1.1); -- OK!
> SELECT COALESCE(NULL, '1d2', 3, 1, 1); -- error
> SELECT COALESCE(NULL, '12', 3, '1d', 1); -- cast error

What is wrong about the existing description?  That it doesn't mention=
mixing data types?


The OP's example demonstrates a more basic co= nfusion about types that needs to be learned independent of COALESCE.
=

The literals= surrounded with single quotes have an unknown type as written.  Since= other literals are plain numbers those are typed as integers.  Since = all arguments to COALESCE need to be the same type (same applies to other f= unctions or things like UNION) the untyped literal is coerced to the known = typed arguments - which fails when the untyped literals are not pure number= s.

Ther= e is a concept of parent types - though I cannot find the rules for them at= the moment - but basically most things devolve toward text; the issue here= is there isn't any "devolving" (parent) involved - its a straight assignme= nt of the known integer type to the untyped literals.

Without further elaboration = as to the shortcomings of the current documentation its hard to add more ma= terial that isn't this section of the documentation's place to teach.
=

David J.
=

<= /div>
------=_Part_3383281_1461677197.1556589715192--