Thread: Small bug with numeric in 7.0 (also in 6.5.3)
Hello,
look at this:
pgsql=# \d aocc
Table "aocc"
Attribute | Type | Modifier
------------------+---------------+----------
nordine | integer | not null
anno | integer | not null
cliente | varchar(50) | not null
data_ordine | date | not null
rif_cliente | varchar(50) |
controvalore_ord | numeric(18,4) |
note | varchar(150) |
Indices: aocc_anno_idx,
aocc_pkey
Constraints: (anno > 1900)
(length(cliente) > 0)
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE controvalore_ord = 320.5
00 AND oid = 21624;
ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast
This kind of weird query are generated by MS Query through the ODBC driver.
Furthermore I tried:
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE controvalore_ord =
320.5::numeric(18,4) AND oid = 21624;
UPDATE 1
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE controvalore_ord =
320.5::numeric(17,3) AND oid = 21624;
UPDATE 1
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE
float8(controvalore_ord)= 320.5 AND oid = 21624;
UPDATE 1
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE controvalore_ord =
'320.5' AND oid = 21624;
UPDATE 1
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE controvalore_ord =
numeric(320.5) AND oid = 21624;
ERROR: parser: parse error at or near "320.5"
pgsql=# UPDATE aocc SET controvalore_ord = 320.5 WHERE controvalore_ord =
numeric(320.5,4) AND oid = 21624;
ERROR: parser: parse error at or near "320.5"
I don't know which of these queries is correct and which is wrong, anyway I
hope this could help you track the problem. It looks like there's an
automatic conversion from char to numeric, from numeric to numeric with
different precision, but not from floatX to numeric. Also the 'numeric'
function with float arguments is in the list reported by '\df numeric', but
looks like it isn't used. May be it simply shouldn't be used like i did in
the previous queries.
Bye!
P.S. My compliments for your all *great* work.
--
Denis Sbragion
InfoTecna
Tel, Fax: +39 039 2324054
URL: http://infotecna.home.dhs.org
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