Thread: interesting difference for queries...

I noticed an interesting difference in query behaviour:

cms=# CREATE TABLE foo(bar int);
CREATE
cms=# SELECT * from foo where bar=1.7;bar
-----
(0 rows)

cms=# SELECT * from foo where bar='1.7';
ERROR:  pg_atoi: error in "1.7": can't parse ".7"

Is this the same problem as index usage with/without quotes? However, one
would expect the same output from both queries, either the error message, or
better the 0 rows result.

RegardsMario Weilguni

On Wed, 2002-12-04 at 11:21, Mario Weilguni wrote:
> I noticed an interesting difference in query behaviour:
>
> cms=# CREATE TABLE foo(bar int);
> CREATE
> cms=# SELECT * from foo where bar=1.7;

This is a numeric to integer coercion, which rounds

rbt=# select 1.7::int;int4
------   2
(1 row)

>  bar
> -----
> (0 rows)
>
> cms=# SELECT * from foo where bar='1.7';
> ERROR:  pg_atoi: error in "1.7": can't parse ".7"

This is a text to integer coercion, which doesn't round.


I guess the question is whether or not a numeric -> integer conversion
should 'wedge' numbers into the int, or throw an error.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt@rbt.ca> writes:
>> cms=# CREATE TABLE foo(bar int);
>> CREATE
>> cms=# SELECT * from foo where bar=1.7;

> This is a numeric to integer coercion, which rounds

No, it's an integer to numeric promotion (the var is promoted, not the
constant).  Obviously the '=' can never return true in this case.

>> cms=# SELECT * from foo where bar='1.7';
>> ERROR:  pg_atoi: error in "1.7": can't parse ".7"

> This is a text to integer coercion, which doesn't round.

And should not, IMHO.  This is effectively the same as
... where bar = '1.7'::int

which it seems to me is quite correct to throw a bad-input error.
        regards, tom lane

On Wed, 2002-12-04 at 12:22, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> >> cms=# CREATE TABLE foo(bar int);
> >> CREATE
> >> cms=# SELECT * from foo where bar=1.7;
>
> > This is a numeric to integer coercion, which rounds
>
> No, it's an integer to numeric promotion (the var is promoted, not the
> constant).  Obviously the '=' can never return true in this case.

Ahh, sorry. I see I changed the query slightly when doing the verbose
explain.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc