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 1hLO9Y-0001gl-Jq for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 08:30:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLO9X-0004Ci-CF for pgsql-docs@arkaria.postgresql.org; Tue, 30 Apr 2019 08:30:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLM46-0002ek-QW for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 06:16:30 +0000 Received: from sonic302-19.consmr.mail.ir2.yahoo.com ([87.248.110.82]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLM40-0007Hg-3l for pgsql-docs@lists.postgresql.org; Tue, 30 Apr 2019 06:16:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1556604981; bh=NA7S/XqSxDwY68dcNMTtBvjgyovYE42kIB1AM+GPD8E=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=WfNI//ilG3Tv9xTKgjmGQmT/FsivVQiUdbQDKG5YngpGRPcgQUQotCFfmKXAWRQC+8/xKvllTIWxm7+gFn/kswz/F9mcGTE9g9792l4ZbQIV2VfWLv1BYIdS6oTREle7Ia/jFPAmjte40TAHN6tx6Ss+UxZfFBa0cNJ1OctAq0nPg5EnUP5pkup0Sxq+HWhAQ0cyZrpYWNdP+y81yCVwFWeNljAsjcUklBA7DlmkXqaiDZcjpByWBrP4rh8UzHCbjVmm80lFAZfI/bt7ohBnT0+C3l2Nq40e12dUPFXrtRA07XtokpUJS1w2NWfo3Q7AQd4ILMh2VyT5oWGHlQmBSQ== X-YMail-OSG: 0hAykGMVM1kS48E158FsERHJKTmhZ229vxiHixymS98TcX4no0o9BM.hQRtCmfs 9ojw8c9AgigOH4yJDOaCs3ekZe1oBdJMcAMn02uaNH0BxIIBAm_7vOV.LQpxBR8qvOpx34EZz99R ERG0VXMdSfDxU3EmfU8VJHmoY_rCsxB873CdQRUlZhzVGPcNkh_3Amm2vlYC8qUT.0y_BRMaW55Z 3XOtHgKRD5oXnFG4jta56h4cK5BXn1psuHK4chmNR1D9VuLZRr6t9Vc0V7lvgQJ2tNnRU0J5cR0z gtu1EB1CvqJrHiZ.c0PWnTJpxQRe4.t.ClM.xFv60QQyWQc2u1AfIihpDfwIW91s6CG0fCwghhQ1 z6BzJuyF41wP7w28.BIZXScv0HvKJMn7agk2_FxMEAZC2318RCxD9LfhFdMC38RDIMat1.zA_B0W b5O6jzjI5l8qQ.2Lond0WYn.DX3ZqFYUX7lH.1Ov5CceN17GAO7KxpCoyG14_.BkOK1Q0Ccefvb1 wJ4GcYkACYSl1UfSGHcll8VfHIf_oCFVVBtvgUgdZDW0gX5uGf6v1sTylC6.GicMLidrMtXCe.aJ CiODt7T_FpijKXWhyLoApnpfUJ2i5kIi4lW8gC2eQEFnE7Rxs_NMyORQF62sWACCNqaem4faxV5w aCGMwSYFhyzDYqsg6S6OQ7.sLgkw5cmq97hc2OH1W5LHmfjHUxjFnNhiFc3V82o8KKsNswGmresv D_pMARI32vIb31vTUUCEg5Km_NITH_jhxJZPY9rj_Ulo1U0G85sWU6TJzio7bPIKKxPxAP4eQW_n HK2exzFK1bChTFe8ydNbPA9CbO._nRZJgm71wDA5jZIh1rrkjmIplPn.nhQBLljmPI3lufIkRFXY FPiX9dIjxDAUWbTKrXgVkd2opmNpmlSpw9NVypHliRDye6mx0K.rjWJT9oA_2a9VVDEP6cPSoAqF Q4ZxjrQR0O7qIdoqMaaBjq4nqySnVVm_EEUTGcn0YIknu7PUNHeMsEvDDgyP5zb.LtmP0Wtpcnov xBTtIPfH650cgOyF_ya4y0syNDU.QM.CqGkjWEcp_JBNdDWen_7rztXnh.Pjnw0N1o2xrw6JeZ.n 1XhWyLOcqyhmryVUfwNMy6a6_r.CgrF3vo9Oj3PbZwFStS2N2voHMRy24sTIKEHZfKLOqHCigUh6 fGcS4fxG2tO_37Q-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.ir2.yahoo.com with HTTP; Tue, 30 Apr 2019 06:16:21 +0000 Date: Tue, 30 Apr 2019 06:16:17 +0000 (UTC) From: Daniil Treshchin To: "David G. Johnston" Cc: Bruce Momjian , pgsql-docs@lists.postgresql.org Message-ID: <1500280966.3430682.1556604977316@mail.yahoo.com> In-Reply-To: References: <155639681797.1368.1856073093791885251@wrigleys.postgresql.org> <20190429231305.ra5z5bapif353vdx@momjian.us> <1767763017.3390110.1556587876525@mail.yahoo.com> <1625110667.3383282.1556589715196@mail.yahoo.com> Subject: Re: COALESCE doccumentation MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3430681_190507138.1556604977314" 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: 7977 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3430681_190507138.1556604977314 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable "SQL is a strongly typed language" =C2=A0- nobody argues with that, that's a common fact everybody knows. "so it is well within its right to require that a query be well-formed such= that all inputs to functions (which COALESCE is in this context) are of th= e same type or raise a compile time error" - they are not of the same type. That's the whole problem from the beginnin= g, I present the values in different data types and it selects the "highest= " based on some internal order and that's the type that is required for all= the other value expressions. In this case the DB engine picks a datatype f= or me when I don't explicitly cast everything. Moreover, it throws an excep= tion if it failed to evaluate the datatype from a value expression even thr= ough it's totally acceptable, it's just doesn't analyze the more complex st= uff. INTEGER is "higher" than CHARACTER VARYING, so the expressions will be vali= dated to INTEGER and not to CHARACTER VARYING. Why, because the engine pick= ed it this way for you. Here is another example: SELECT COALESCE(NULL, '12', 2,=C2=A0 3 :: INTEGER,= 1 :: NUMERIC, 1 :: INTEGER); Why is the result NUMERIC? Anyway, the doc still doesn't give any info on these: 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 type 4) COALESCE evaluates every s to the highest datatype sel= ected in step (2). Correction from the last email I checked, the value expr= ession is not evaluated itself but it is "analyzed" to the highest datatype= or exception is thrown during various, not obvious conditions. Thank you. On Monday, April 29, 2019, 10:26:58 PM PDT, David G. Johnston wrote: =20 =20 On Mon, Apr 29, 2019 at 7:01 PM Daniil Treshchin wrote: but there is still a problem in here as you can see from this confusion. I= t evaluates the type of the s. SQL is a strongly typed language so it is well within its right to require = that a query be well-formed such that all inputs to functions (which COALES= CE is in this context) are of the same type or raise a compile time error. The expression is still not evaluated for each row during runtime which is = all the section on short-circuiting promises. David J. =20 ------=_Part_3430681_190507138.1556604977314 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
"SQL is a strongly typed language"
 - nobody argues with that, that's a common fact everybod= y knows.

"so it i= s well within its right to=20 require that a query be well-formed such that all inputs to functions=20 (which COALESCE is in this context) are of the same type or raise a=20 compile time error"

= - they are not of the same type. That's the whole= problem from the beginning, I present the values in different data types a= nd it selects the "highest" based on some internal order and that's the typ= e that is required for all the other value expressions. In this case the DB= engine picks a datatype for me when I don't explicitly cast everything. Mo= reover, it throws an exception if it failed to evaluate the datatype from a= value expression even through it's totally acceptable, it's just doesn't a= nalyze the more complex stuff.

=
INTEGER is "higher" than CHARACTER VARYING, so the expressions w= ill be validated to INTEGER and not to CHARACTER VARYING. Why, because the = engine picked it this way for you.

Here is another example: SELECT COALESCE(NULL, '12', 2= ,  3 :: INTEGER, 1 :: NUMERIC, 1 :: INTEGER); Why is the result NUMERI= C?

Anyway, the doc = still doesn't give any info on these:

1)= COALESCE scans the list of <value expression>s
2) COALESCE determines the highest data type in the li= st of <value expressions>s
3) COALESCE c= asting the first non-NULL to the highest data type
<= div>4) COALESCE evaluates e= very <value expression>s to the highest datatype selected in step (2)= . Correction from the last email I checked, the value expression is not eva= luated itself but it is "analyzed" to the highest datatype or exception is = thrown during various, not obvious conditions.

Thank you.
=20
=20
On Monday, April 29, 2019, 10:26:58 PM PDT, David G. Jo= hnston <david.g.johnston@gmail.com> wrote:


On Mon, Apr 29, 2019 at 7:01 PM Daniil Treshchin <t.dnil@yahoo.com> wrote:
<= /div>
but there is still a problem in here as you can see from this = confusion. It evaluates the type of the <value expression>s.

SQL = is a strongly typed language so it is well within its right to require that= a query be well-formed such that all inputs to functions (which COALESCE i= s in this context) are of the same type or raise a compile time error.

The express= ion is still not evaluated for each row during runtime which is all the sec= tion on short-circuiting promises.

David J.
------=_Part_3430681_190507138.1556604977314--