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. 

Regards
	Mario Weilguni

Wed, 4 Dec 2002 17:21:50 +0100
"Mario Weilguni" <mario.weilguni@icomedias.com>
interesting difference for queries...
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
04 Dec 2002 11:43:21 -0500
Rod Taylor <rbt@rbt.ca>
Re: interesting difference for queries...
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

Wed, 04 Dec 2002 12:22:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: interesting difference for queries... 
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
04 Dec 2002 12:55:18 -0500
Rod Taylor <rbt@rbt.ca>
Re: interesting difference for queries...