MIME-Version: 1.0
References: 
 <CAMZ4ZVqq9kHN7DJs5T=zL8jDJuk-3v2-spyYtjd8e2xj_OPM8Q@mail.gmail.com>
 <2404795.1634825164@sss.pgh.pa.us>
In-Reply-To: <2404795.1634825164@sss.pgh.pa.us>
From: Nuno Sousa <nunofgs@gmail.com>
Date: Thu, 21 Oct 2021 16:26:26 +0100
Message-ID: 
 <CAMZ4ZVo-gMBDOvLKBory1yeHjsgV1NjWMtc5R4sswi6vbw8SuQ@mail.gmail.com>
Subject: Re: Numeric Scale Differences
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000092eca705cede8575"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMZ4ZVo-gMBDOvLKBory1yeHjsgV1NjWMtc5R4sswi6vbw8SuQ%40mail.gmail.com>
Precedence: bulk

--00000000000092eca705cede8575
Content-Type: text/plain; charset="UTF-8"

Hi Tom,

Thanks for the quick response. In that case, do you think it's worth
documenting the implementation artifact behavior on the docs?
--
Nuno Sousa


On Thu, Oct 21, 2021 at 3:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Nuno Sousa <nunofgs@gmail.com> writes:
> > I've run into an interesting issue when trying to do numeric math. I
> > expected the following to return the same number of decimal digits in the
> > fractional part:
>
> > select scale(1/0.6::numeric), scale(1/6::numeric);
>
> Um ... why did you expect that?  The inputs to the divisions have
> different scales:
>
> # select scale(0.6::numeric), scale(6::numeric);
>  scale | scale
> -------+-------
>      1 |     0
> (1 row)
>
> so I find it unsurprising that the outputs do too.  Now the fact
> that the output scales differ by 4 not 1 is indeed an implementation
> artifact.  It stems from the numeric type working with base-10000
> digits, so that scales that are multiples of 4 are most efficient,
> hence division will always choose such an output scale.
>
>                         regards, tom lane
>

--00000000000092eca705cede8575
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Tom,<div><br></div><div>Thanks for the quick=C2=A0respo=
nse. In that case, do you think it&#39;s worth documenting the implementati=
on artifact behavior on the docs?</div><div><div><div dir=3D"ltr" class=3D"=
gmail_signature" data-smartmail=3D"gmail_signature">--<br>Nuno Sousa</div><=
/div><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=
=3D"gmail_attr">On Thu, Oct 21, 2021 at 3:06 PM Tom Lane &lt;<a href=3D"mai=
lto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex">Nuno Sousa &lt;<a href=3D"mailto:nu=
nofgs@gmail.com" target=3D"_blank">nunofgs@gmail.com</a>&gt; writes:<br>
&gt; I&#39;ve run into an interesting issue when trying to do numeric math.=
 I<br>
&gt; expected the following to return the same number of decimal digits in =
the<br>
&gt; fractional part:<br>
<br>
&gt; select scale(1/0.6::numeric), scale(1/6::numeric);<br>
<br>
Um ... why did you expect that?=C2=A0 The inputs to the divisions have<br>
different scales:<br>
<br>
# select scale(0.6::numeric), scale(6::numeric);<br>
=C2=A0scale | scale <br>
-------+-------<br>
=C2=A0 =C2=A0 =C2=A01 |=C2=A0 =C2=A0 =C2=A00<br>
(1 row)<br>
<br>
so I find it unsurprising that the outputs do too.=C2=A0 Now the fact<br>
that the output scales differ by 4 not 1 is indeed an implementation<br>
artifact.=C2=A0 It stems from the numeric type working with base-10000<br>
digits, so that scales that are multiples of 4 are most efficient,<br>
hence division will always choose such an output scale.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--00000000000092eca705cede8575--