Thread: ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

Why isn't this casted automatically?

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

> Why isn't this casted automatically?

Oversight. Will look at it.
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Why isn't this casted automatically?

> Oversight. Will look at it.

I believe it's the problem I complained of before: TypeCategory()
doesn't think NUMERIC is a numeric type...
        regards, tom lane

> >> Why isn't this casted automatically?
> > Oversight. Will look at it.
> I believe it's the problem I complained of before: TypeCategory()
> doesn't think NUMERIC is a numeric type...

Right. The "oversight" is a long standing one, and somewhat
intentional.

One hesitation I have is the performance hit in mixing FLOAT and
NUMERIC; I (probably) don't want to make NUMERIC the "best" numeric
type, since it is potentially so slow. I'll have to look to see what
happens in INT/FLOAT mixed arithmetic and make sure it doesn't end up
doing it in NUMERIC instead.
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> One hesitation I have is the performance hit in mixing FLOAT and
> NUMERIC; I (probably) don't want to make NUMERIC the "best" numeric
> type, since it is potentially so slow.

I concur --- I'd be inclined to leave FLOAT8 as the top of the
hierarchy.  But NUMERIC could be stuck in there between int and float,
no?  (int-vs-numeric ops certainly must be promoted to numeric...)
        regards, tom lane

> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> > One hesitation I have is the performance hit in mixing FLOAT and
> > NUMERIC; I (probably) don't want to make NUMERIC the "best" numeric
> > type, since it is potentially so slow.
>
> I concur --- I'd be inclined to leave FLOAT8 as the top of the
> hierarchy.  But NUMERIC could be stuck in there between int and float,
> no?  (int-vs-numeric ops certainly must be promoted to numeric...)

If you cast NUMERIC to FLOAT8, then you would loose precision and it would
be counterintuitive type promotion (at least for a C programmer). If someone
wants speed over correctness, he can always explicitly cast NUMERIC to
FLOAT8. Seems like "correct" should take precedence over "fast", at least as
long as there is a way to do "fast".

Gene Sokolov.

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
>
> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> > One hesitation I have is the performance hit in mixing FLOAT and
> > NUMERIC; I (probably) don't want to make NUMERIC the "best" numeric
> > type, since it is potentially so slow.
>
> I concur --- I'd be inclined to leave FLOAT8 as the top of the
> hierarchy.  But NUMERIC could be stuck in there between int and float,
> no?  (int-vs-numeric ops certainly must be promoted to numeric...)
>

Is this topic related to the fact that 1.1 is an FLOAT8 constant in
PostgreSQL ?
I've not understood at all why it's OK.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

[Charset iso-2022-jp unsupported, skipping...]
>:-{

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane
> >
> > Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> > > One hesitation I have is the performance hit in mixing FLOAT and
> > > NUMERIC; I (probably) don't want to make NUMERIC the "best" numeric
> > > type, since it is potentially so slow.
> >
> > I concur --- I'd be inclined to leave FLOAT8 as the top of the
> > hierarchy.  But NUMERIC could be stuck in there between int and float,
> > no?  (int-vs-numeric ops certainly must be promoted to numeric...)
> >
>
> Is this topic related to the fact that 1.1 is an FLOAT8 constant in
> PostgreSQL ?
> I've not understood at all why it's OK.
   IMHO  a  value  floating  around should be kept NUMERIC or in   it's string representation until it's finally clear
where it   is dropped (int2/4/8, float4/8, numeric or return to client).
 
   This surely has an impact on performance,  but  from  my  PoV   beeing   correct   has   a  higher  priority.   If
you want   performance, buy well sized hardware depending on application   and  workload.  If  you  want  reliability,
choosethe right   software.
 
   Don't force it, use a bigger hammer!


Jan
   BTW: I still intend to redo the NUMERIC type somewhere in the   future. Just haven't found the time though.

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> I concur --- I'd be inclined to leave FLOAT8 as the top of the
>> hierarchy.  But NUMERIC could be stuck in there between int and float,
>> no?  (int-vs-numeric ops certainly must be promoted to numeric...)

> Is this topic related to the fact that 1.1 is an FLOAT8 constant in
> PostgreSQL ?

No, not directly.  At least I don't think the question of how constants
are handled forces our decision about which direction the default
promotion should go.


> I've not understood at all why it's OK.

There's a really, really crude hack in scan.l that prevents a long
numeric constant from being converted to FLOAT8.  Otherwise we'd lose
precision from making the value float8 and later converting it to
numeric (after type analysis had discovered the necessity for it to
be numeric).  I think this is pretty ugly, not to say inconsistent,
since the parser's behavior can change depending on how many digits
you type:

regression=# select * from num_data where val = 12345678901234.56;
ERROR:  Unable to identify an operator '=' for types 'numeric' and 'float8'       You will have to retype this query
usingan explicit cast
 
regression=# select * from num_data where val = 12345678901234.567;id | val
----+-----
(0 rows)

The second case works because it's treated exactly likeselect * from num_data where val = '12345678901234.567';
and here, the resolution of an UNKNOWN-type string constant saves
the day.

I proposed a while back that T_Float tokens ought to carry the value in
string form, rather than actually converting it to float, so that we
behave consistently while taking no precision risks until the target
type is known for certain.  Thomas seems not to want to do it that way,
for some reason.
        regards, tom lane

> I proposed a while back that T_Float tokens ought to carry the value in
> string form, rather than actually converting it to float, so that we
> behave consistently while taking no precision risks until the target
> type is known for certain.  Thomas seems not to want to do it that way,
> for some reason.

Hmm. We should then carry *all* numeric types as strings farther into
the backend, probably deeper than gram.y? Some of the input validation
happens as early as gram.y now, so I guess we would need to do some
conversion at that point for some contexts, and leave the numeric
stuff as a string in other contexts. No fair only doing it for float8;
int4 has the same trouble.

Just seems like a can of worms, but it is definitely (?) the right
solution since at the moment the early interpretation of numerics can
lead to loss of info or precision deeper in the code.

This could be a minor-release kind of improvement...
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> I proposed a while back that T_Float tokens ought to carry the value in
>> string form, rather than actually converting it to float,

> No fair only doing it for float8; int4 has the same trouble.

Au contraire: int representation has no risk of loss of precision.
It does risk overflow, but we can detect that reliably, and in fact
scan.l already takes care of that scenario.

If we allow ints to retain their current representation, then the
manipulations currently done in gram.y don't need to change.  All
that's needed is to invoke the proper typinput function after we've
decided what type we really want to convert a T_Float to.  T_Float
would act kind of like UNKNOWN-type string constants, except that
the knowledge that the string looks numeric-ish could be used in
type selection heuristics.
        regards, tom lane

> > No fair only doing it for float8; int4 has the same trouble.
> Au contraire: int representation has no risk of loss of precision.
> It does risk overflow, but we can detect that reliably, and in fact
> scan.l already takes care of that scenario.

Right, but why bother doing it there and then having to propagate the
"int4 or string" code into the backend? Right now, we mark it as an
string constant of unknown characteristics if it is too large for an
int4, but that isn't the right thing for long numerics since we are
throwing away valuable info. And using the scan.l heuristic to filter
out large values for things like OIDs is probably cheating a bit ;)

> If we allow ints to retain their current representation, then the
> manipulations currently done in gram.y don't need to change.  All
> that's needed is to invoke the proper typinput function after we've
> decided what type we really want to convert a T_Float to.  T_Float
> would act kind of like UNKNOWN-type string constants, except that
> the knowledge that the string looks numeric-ish could be used in
> type selection heuristics.

So a replacement for T_Float would carry the "long string with decimal
point" info, and a replacement for T_Integer would carry the "long
string with digits only" info. And we would continue to use T_Float
and T_Integer deeper in the backend to carry converted values.
                     - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On 2000-02-17, Jan Wieck mentioned:

>     IMHO  a  value  floating  around should be kept NUMERIC or in
>     it's string representation until it's finally clear where  it
>     is dropped (int2/4/8, float4/8, numeric or return to client).

Actually, the hierarchy float8, float4, numeric, int8, int4, int2 might
just be right. The standard specifies that float<x> + numeric = float<y>
(where perhaps x == y, not sure). On the other hand, it is also quite
clear that a constant of the form 123.45 is a numeric literal.


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden