Thread: Select (str)::FLOAT8 BUG

Hi!,

I think i have found a bug.

I=B4ve tried it in PgSQL 7.3.5 & 7.4.1

When I do:

atisae_oct_1=3D# SELECT (1000/500/7)::FLOAT8 as v_numero;
 v_numero
----------
        0
(1 row)

atisae_oct_1=3D# SELECT 1000/500/7::FLOAT8 as v_numero;
     v_numero
-------------------
 0.285714285714286
(1 row)

atisae_oct_1=3D# SELECT (1000/500/7)::NUMERIC as v_numero;
 v_numero
----------
        0
(1 row)

atisae_oct_1=3D# SELECT 1000/500/7::NUMERIC as v_numero;
        v_numero
------------------------
 0.28571428571428571429
(1 row)


Why is different: 'SELECT (xxxxx)::FLOAT8' than 'SELECT xxxxx::FLOAT8'

Thanks in advance...

Rafa.

My guess would be...=20

1000/5000/7::FLOAT8 is really 1000::INT/500::INT/7::FLOAT8 (int may be some=
 other integer type) whereas (1000/500/7)::FLOAT8 is (1000::INT/500::INT/7:=
:INT)::FLOAT8 i.e. the resultan integer is casted to type FLOAT8 after the =
maths has been done on integers. Thats what I'd expect.


Iain


> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org
> [mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Rafael Villalobos
> Prats
> Sent: Wednesday, February 18, 2004 3:38 PM
> To: pgsql-bugs@postgresql.org
> Subject: [BUGS] Select (str)::FLOAT8 BUG
>=20
>=20
> Hi!,
>=20
> I think i have found a bug.
>=20
> I=B4ve tried it in PgSQL 7.3.5 & 7.4.1
>=20
> When I do:
>=20
> atisae_oct_1=3D# SELECT (1000/500/7)::FLOAT8 as v_numero;
>  v_numero
> ----------
>         0
> (1 row)
>=20
> atisae_oct_1=3D# SELECT 1000/500/7::FLOAT8 as v_numero;
>      v_numero
> -------------------
>  0.285714285714286
> (1 row)
>=20
> atisae_oct_1=3D# SELECT (1000/500/7)::NUMERIC as v_numero;
>  v_numero
> ----------
>         0
> (1 row)
>=20
> atisae_oct_1=3D# SELECT 1000/500/7::NUMERIC as v_numero;
>         v_numero
> ------------------------
>  0.28571428571428571429
> (1 row)
>=20
>=20
> Why is different: 'SELECT (xxxxx)::FLOAT8' than 'SELECT xxxxx::FLOAT8'
>=20
> Thanks in advance...
>=20
> Rafa.
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>=20
>                http://archives.postgresql.org
>=20

Ok, that's right.

Many thanks....

Rafa-.

-----Mensaje original-----
De: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] En nombre de Fothergill, Iain
Enviado el: miércoles, 18 de febrero de 2004 16:31
Para: pgsql-bugs@postgresql.org
Asunto: Re: [BUGS] Select (str)::FLOAT8 BUG

My guess would be...

1000/5000/7::FLOAT8 is really 1000::INT/500::INT/7::FLOAT8 (int may be
some other integer type) whereas (1000/500/7)::FLOAT8 is
(1000::INT/500::INT/7::INT)::FLOAT8 i.e. the resultan integer is casted
to type FLOAT8 after the maths has been done on integers. Thats what I'd
expect.


Iain


> -----Original Message-----
> From: pgsql-bugs-owner@postgresql.org
> [mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Rafael Villalobos
> Prats
> Sent: Wednesday, February 18, 2004 3:38 PM
> To: pgsql-bugs@postgresql.org
> Subject: [BUGS] Select (str)::FLOAT8 BUG
>
>
> Hi!,
>
> I think i have found a bug.
>
> I´ve tried it in PgSQL 7.3.5 & 7.4.1
>
> When I do:
>
> atisae_oct_1=# SELECT (1000/500/7)::FLOAT8 as v_numero;
>  v_numero
> ----------
>         0
> (1 row)
>
> atisae_oct_1=# SELECT 1000/500/7::FLOAT8 as v_numero;
>      v_numero
> -------------------
>  0.285714285714286
> (1 row)
>
> atisae_oct_1=# SELECT (1000/500/7)::NUMERIC as v_numero;
>  v_numero
> ----------
>         0
> (1 row)
>
> atisae_oct_1=# SELECT 1000/500/7::NUMERIC as v_numero;
>         v_numero
> ------------------------
>  0.28571428571428571429
> (1 row)
>
>
> Why is different: 'SELECT (xxxxx)::FLOAT8' than 'SELECT xxxxx::FLOAT8'
>
> Thanks in advance...
>
> Rafa.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

"Rafael Villalobos Prats" <rvillalobos@asapci.es> writes:
> Why is different: 'SELECT (xxxxx)::FLOAT8' than 'SELECT xxxxx::FLOAT8'

The :: operator binds more tightly than most other operators.  As Iain
replied, most of your divisions are going to be integer divisions that
discard the fractional part.  You can either add casts or add ".0" to
your numbers to cause them to be considered non-integers.

            regards, tom lane

On Wed, 18 Feb 2004, Rafael Villalobos Prats wrote:

> I think i have found a bug.
>
> I´ve tried it in PgSQL 7.3.5 & 7.4.1
>
> When I do:
>
> atisae_oct_1=# SELECT (1000/500/7)::FLOAT8 as v_numero;
>  v_numero
> ----------
>         0
> (1 row)

    This is not a bug and it also has nothing to do with postgresql.
Try the same in C and you will have the same result.
    The expression 1000/500/7 contains operations between variables
of an integer type: 1000/500 = 2, 2/7=0. 0::float=0.
    Probably a more accurate result will be obtained if you modify
this to:
    1000::float/500/7.
    In this case 1000/500/7::float or 1000::float/500/7 will return
the same result, but 1000::float/501/7 and 1000/501/7::float will not
because the first operation will be between variables of an integer
type.

--
Tarhon-Onu Victor
Area Technical Coordinator
RDS Iasi - Network Operations Center
www.rdsnet.ro, www.rdstel.ro, www.rdslink.ro
Phone: +40-232-218385; Fax: +40-232-260099
..........................................................................
Privileged/Confidential Information may be contained in this message. If
you are not the addressee indicated in this message (or responsible for
delivery of the message to such person), you may not copy or deliver this
message to anyone. In such a case, you should destroy this message and
kindly notify the sender by reply e-mail.