Horton

Thread

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

    Michael Meskes <meskes@postgresql.org> — 2000-02-16T10:15:14Z

    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!

  2. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-16T14:14:36Z

    > Why isn't this casted automatically?

Oversight. Will look at it.

                      - Thomas

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

  3. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-16T14:30:14Z

    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

  4. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-16T14:46:08Z

    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

  5. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-16T14:47:01Z

    > >> 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

  6. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Gene Sokolov <hook@aktrad.ru> — 2000-02-16T16:00:05Z

    > 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.

  7. RE: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Hiroshi Inoue <inoue@tpf.co.jp> — 2000-02-17T02:37:23Z

    > -----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

  8. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Jan Wieck <wieck@debis.com> — 2000-02-17T03:02:14Z

    [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,  choose the 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) #

  9. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-17T03:23:46Z

    "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 using an explicit cast
regression=# select * from num_data where val = 12345678901234.567;
 id | val
----+-----
(0 rows)

The second case works because it's treated exactly like
	select * 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

  10. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-17T07:14:26Z

    > 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

  11. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-17T07:38:46Z

    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

  12. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-17T14:51:52Z

    > > 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

  13. Re: [HACKERS] ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'

    Peter Eisentraut <peter_e@gmx.net> — 2000-02-19T14:12:54Z

    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