Small bug with numeric in 7.0 (also in 6.5.3)

Dirk Lutzebaeck <lutzeb@aeccom.com> writes:

$ pg_dump cs1 -o > /tmp/x.dump
parseNumericArray: bogus number

IIRC, this is what you see when you run a 6.5 pg_dump against a 7.0
database (or perhaps it was vice versa).

There's been talk of putting stricter version checking into pg_dump,
but that might introduce its own problems...

regards, tom lane

Tom Lane wrote:

Raul Chirea <raul@brahms.ro> writes:

So, it seems to be a parser difficulty (bug) to correctly determine
the type of a numeric constant with decimals (like 99.9).

We've been aware of the problem for a while but are unsure as yet
how to solve it without breaking other cases --- ie, cases where
you *do* want such a constant to be treated as float not numeric.

For the moment, we have left the behavior as it's always been in
Postgres (ie, undecorated non-integral constants are taken to be
'float8'), so as not to break existing applications.

Bruce Momjian wrote:

test=> create table tab1(x numeric);
CREATE
test=> insert into tab1 values (1);
INSERT 20937 1
test=> update tab1 set x=4 where x=5;
UPDATE 0
test=> update tab1 set x=4.0 where x=5;
UPDATE 0
test=> update tab1 set x=4 where x=5.0;
ERROR: Unable to identify an operator '=' for types 'numeric'
and
'float8'
You will have to retype this query using an explicit cast

test=>

This is terrible. I can't imagine how people use this without terrible
problems. Why don't we get more bug reports about this?

I don't know nothing about postgres internals but I imagine that it is
possible to treat float and numeric constants in an omogenuous
manner because basicaly numeric and float type are the same, only
the storage mode in a field differs. Or it may be possible to do an
automatic cast between float and numeric type.

I may just have told some stupid things, but it's only an opinion.
:-)

This problem bothers me because if one (me, for example ;-) tries to
use a client program that compose the select automaticaly (i.e. Delphi
table component) and there is no way to manually cast that expression
(i.e. "where <numeric_field> = 12345.67::numeric") this is a real problem !

Bye.

Raul Chirea <raul@brahms.ro> writes:

This problem bothers me because if one (me, for example ;-) tries to
use a client program that compose the select automaticaly (i.e. Delphi
table component) and there is no way to manually cast that expression
(i.e. "where <numeric_field> = 12345.67::numeric") this is a real problem !

Yeah, we know :-(. However, fixing it right --- that is, not simply
transferring the breakage to someone else --- is not all that simple.
We're working on it...

regards, tom lane

Any comments on this one?

Raul Chirea <raul@brahms.ro> writes:

So, it seems to be a parser difficulty (bug) to correctly determine
the type of a numeric constant with decimals (like 99.9).

Yes. You can find more about this in the pgsql-hackers archives.
We've been aware of the problem for a while but are unsure as yet
how to solve it without breaking other cases --- ie, cases where
you *do* want such a constant to be treated as float not numeric.

For the moment, we have left the behavior as it's always been in
Postgres (ie, undecorated non-integral constants are taken to be
'float8'), so as not to break existing applications.

regards, tom lane

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026