Re: BUG #18420: Unexpected values appeared in select query statements that should logically imply each other

On Thu, 4 Apr 2024 at 22:44, 王子涵4620 <1290874854@qq.com> wrote:
> create table table_3_utf8_undef (
> id SERIAL PRIMARY KEY,
> "col_varchar(20)_undef_signed" varchar(20)
>
> ) ;
> insert into table_3_utf8_undef values (0,'3
> '),(1,'well'),(2,'-0');

What's going on here is just the same as the following.

postgres=# select '1'::numeric union select '1.00000'::numeric;
 numeric
---------
       1
(1 row)


postgres=# select '1.00000'::numeric union select '1'::numeric;
 numeric
---------
 1.00000
(1 row)

Essentially, numerics 1 and 1.00000 are equal but which one is output
from the query depends on which one was seen first by the code that
eliminates the duplicates.

Something similar can be seen with:

postgres=# select sum(n) from (values('1'::numeric),
('10.00000'::numeric), ('-10'::numeric)) n(n);
   sum
---------
 1.00000
(1 row)

You might expect this returns "1" rather than "1.00000".  This is just
another example of us not reducing the numeric's dscale down to the
minimum value it can be without losing precision.

If we always reduced the numeric down to its minimum dscale, then the
following query would return "1" rather than "1.00000".  The "n"
column type is numeric(8,5) so having 5 digits after the decimal point
seems correct here.

create table n (n numeric(8,5));
insert into n values('1'),('10'),('-10');
select sum(n) from n;
   sum
---------
 1.00000

I'm unsure how we'd change the behaviour of one without changing the
behaviour of the other.

David