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 1hLIMl-00069s-8v for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 02:19:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLIMh-0004AH-La for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 02:19:27 +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 1hLHcE-0007Wl-QD for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 01:31:27 +0000 Received: from sonic303-21.consmr.mail.ir2.yahoo.com ([77.238.178.202]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLHcB-0007fo-Lf for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 01:31:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1556587881; bh=vu1XCrzT/DMNkYyWZvllloR9vEclhruxx9KFBh3tRek=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=KOfZ3G+69FQC/6aGgJlK00h1cKZbcTnFE8m3tMdu32zespn4tEn/JookP2Hby5/IWzn6u5y8OcBpYcgPhMuGylL2N3RLypD/4kgc1lDwNjzsGr0Y8TDHDPnq5XajKETI/PLMlmDv8d0loPBASI08Xfd4abZxxrMiJQXCTuR7MybSfgK5zTAGLvZZxikxSdeaWcT1Y8QbcuBoIafk4GR58DWEZVkIlS7daEAb1l0x+LQr5HAr4ZwN00SpeCcR3pa8hDxyDsSAZCxMNnGKst+qwoZS88i6yaMqmKa9JPCZPqSq6U3wBFkkUuOi70hyb/wtTleEGMF6NlqtI/kU25yUKg== X-YMail-OSG: q17cKvUVM1ks26u8CQ9QqmeESBNvVlxxxFg5JCjviJ0j0y7u242_NUkpepdkClX W6JgqjhDS8ohsVsLtn7riLFSIvJy.ucTuKHeOkilBtg5TerMwgvUMtI8vYcgrlFAAeizyKCgvT1I v28wyFXDngqqWgeNjkaqqeyUXqnd.xomGeQvdZtc49u_9uvUGenfarDaWTAhIFq.QIrCUJ6bp.YP yEGEVrhii_..1S4P70jvVvhgYKROajnrQpui7Hw7tkJ6Bu_dYmX6mGcA5ETwX7Zp2PWwXCrloxKp BBw3Yw6ltVwEwpx4ToUaHFVjv1BwJcD4rZkpuXCBvviRC1TFyjy90OeGexg.Wc06aY02SYDbUG2_ 82_W9kfsSTmPZAE3DzmWtM_EG9VnUBS9PEb_3bLh8DvhrgOfPdqj9EsE23wKEmez6GierHEhNE6P Ekj5OfKKxdBE56aysTP5JjBUmmxUrFkgQaNV2Yc8k5UM9bBq9aiRX5q1oZoBnShByES.fSJwJ84o GNdqPiWbTL9xP8rq9fZ7vP1h.UuXy1oN725KD5dX._wnfJ40pHpoEKypTYHhhxoAA31wmWDzBJkF _fSjOkxyW3X6bm1F5vbsWLwWKi_KWKgkzPFnuE0DMz9RNmzRZDfnsoc6rMzw2bQvhdqjO3zgSrn4 RlShBGFn0cot1x.DaKrXgT7fSo5oEf_tsmMSpzkegesHwFTNst2DXihELdQrjakuJHpAwdPUMLp1 jYNGzeBnFzpEweh_SfTweKYIAlg3Eb09F3eAaLemxriqJzXdfdFwhcf_fjiOWEGCsWnyPEnYya3q .bvTjo7tZc0_T4fLx_h80fERG6TpLBlqmdISEk1KVhEduO7UbiIRqeWpnCWL5UyMyvTJBeMFE5ER 1_1UqZfX9hQqhksSDhbsGYGcr6o.TwfoYoRfsQEuqIFVO2sseiUHRsOfQnG8FWGUnB.iJtDOXHVr yDfTwxYW_La4F2yh7gX_hrYp7iNsQUrbakamkhchdmUeucyTIj9Kem5Z8x.Qh6rviwKhJ8SrMKQV KX5ZOQ9jN6GXjUMCJMLNNXmh5dZT7a4pz9dzlnrjLPx8yd86he_neBWjG9eP6m17dW7xRHQizlXr mJn7BLkpFxUzK.L9cbYwSbm5TLHcsFOeHmn1_.S7OxRFRq_pGm33gRvc_tX6qolIXm_IwPnWpke7 nSArLEtwPTv7t0XkgWYBW8A-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic303.consmr.mail.ir2.yahoo.com with HTTP; Tue, 30 Apr 2019 01:31:21 +0000 Date: Tue, 30 Apr 2019 01:31:16 +0000 (UTC) From: Daniil Treshchin To: Bruce Momjian , "David G. Johnston" Cc: pgsql-docs@lists.postgresql.org Message-ID: <1767763017.3390110.1556587876525@mail.yahoo.com> In-Reply-To: References: <155639681797.1368.1856073093791885251@wrigleys.postgresql.org> <20190429231305.ra5z5bapif353vdx@momjian.us> Subject: Re: COALESCE doccumentation MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3390109_1760402550.1556587876522" 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: 14146 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3390109_1760402550.1556587876522 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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_3390109_1760402550.1556587876522 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Bruce, David.

Comment: "W= ithout further elaboration as to the shortcomings"
I provided a clear description on why, but for some reason it was missed i= n 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 first datatype but there is an or= der, for example:

numer= ic
integ= er
character varying
text

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

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);  --= 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 des= cription is obscure.
What the docs are not saying:

=
1) COALESCE scans the list of <value expression>s
2) COALESCE determines the highest data t= ype in the list of <value expressions>s
3) COA= LESCE casting the first non-NULL to the highest data type
4) COALESCE ev= aluates every <value expression>s to the highest datatype selected in= step (2)
=

In addition the d= oc 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."

Thi= s is so not true according to the doc and just breaks a simple test.

SELECT COALESCE(NULL, '12', '3',= '1.2', (SELECT profile_code FROM profile LIMIT 1));  -- OK! evaluates= to character varying
SELECT C= OALESCE(NULL, '12', 3, '1.2', (SELECT profile_code FROM profile LIMIT 1));&= nbsp; -- 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_3390109_1760402550.1556587876522--