Bug?

I'm currently back to work with version 6.2.1 since I cannot connect with
6.3 via ODBC.

Anyway, I got my application running after finding a problem (inside the
app) with data conversion (it read double from a long data field). But it
seems I also encountered what I believe to be a bug. Since I'm nor sure
whether it's known I bring it up here. If I try to insert 199802051215 to a
float8 field it doesn't work because the parser believes this is a long and
truncates it to 2147...... Using 199802051215.0 to make sure it's a float
works fine. Shouldn't the parser be able to handle this?

The current v6.3beta behavior is this:

postgres=> select 199802051215;
ERROR: Bad integer input '199802051215'

We had a bit of a discussion about the best way to handle this, and decided to
try Bruce's solution to reject the input as a first step. I have patches to do
the automatic conversion to a float, but have not applied them.

The other approach would lead to an error looking like:

postgres=> insert into t values (199802051215.0);
ERROR: pg_atoi: error reading "199802051215.000000": Math result not
representable

I can see arguments for both approaches; do you have a strong opinion either
way?

- Tom

Is there a way to explicitely tell the parser which kind of variable we
want? That is something like float(1) to get 1.0.

It's obviously easier in Oracle (where my source came from) since they
only have one number type. But expclicitely converting would be okay
IMO.

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

Is there a way to explicitely tell the parser which kind of variable we
want? That is something like float(1) to get 1.0.

'1'::float8

It's obviously easier in Oracle (where my source came from) since they
only have one number type. But expclicitely converting would be okay
IMO.

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

Until later, Jan

--

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

I'm currently back to work with version 6.2.1 since I cannot connect with
6.3 via ODBC.

Anyway, I got my application running after finding a problem (inside the
app) with data conversion (it read double from a long data field). But it
seems I also encountered what I believe to be a bug. Since I'm nor sure
whether it's known I bring it up here. If I try to insert 199802051215 to a
float8 field it doesn't work because the parser believes this is a long and
truncates it to 2147...... Using 199802051215.0 to make sure it's a float
works fine. Shouldn't the parser be able to handle this?

I think it converts it to an integer, and then by the time it tries to
convert it, it has already chopped off the top of the number. The only
fix for this would be to read all integers in as 64-bit integers, then
do the conversion, but that could be a performance problem.

--
Bruce Momjian
maillist@candle.pha.pa.us

Jan Wieck writes:

Is there a way to explicitely tell the parser which kind of variable we
want? That is something like float(1) to get 1.0.

'1'::float8

I'll try that for a complex statement. Seems I have to learn quite a lot
about postgresql. :-)

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

Bruce Momjian writes:

I think it converts it to an integer, and then by the time it tries to
convert it, it has already chopped off the top of the number. The only
fix for this would be to read all integers in as 64-bit integers, then
do the conversion, but that could be a performance problem.

I agree. And performance is important. I think explicit type conversion is
what we should do. Or is it asked for too much if the user has to add a
::float8 to the number?

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10

I think it converts it to an integer, and then by the time it tries to
convert it, it has already chopped off the top of the number. The only
fix for this would be to read all integers in as 64-bit integers, then
do the conversion, but that could be a performance problem.

Well, the other possibility is to try converting to float8 only if the int4
conversion fails. If both fail, then throw an elog(ERROR). I have patches for
this...

Bruce Momjian said:

The only fix for this would be to read all integers in as 64-bit
integers, then do the conversion, but that could be a performance
problem.

Michael Meskes answered:

I agree. And performance is important. I think explicit type
conversion is what we should do. Or is it asked for too much if the
user has to add a ::float8 to the number?

Am I being dense here? Can there really be a significant performance
hit in the parsing of a query? Let's say that it takes a millisecond
extra to do the right thing with a number. Does it matter? How many
queries per second can we expect to process anyway?

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

Bruce Momjian said:

The only fix for this would be to read all integers in as 64-bit
integers, then do the conversion, but that could be a performance
problem.

Michael Meskes answered:

I agree. And performance is important. I think explicit type
conversion is what we should do. Or is it asked for too much if the
user has to add a ::float8 to the number?

Am I being dense here? Can there really be a significant performance
hit in the parsing of a query? Let's say that it takes a millisecond
extra to do the right thing with a number. Does it matter? How many
queries per second can we expect to process anyway?

I don't know of standard ways to read in 64-bit integers.

--
Bruce Momjian
maillist@candle.pha.pa.us

On Fri, 6 Feb 1998, Bruce Momjian wrote:

Am I being dense here? Can there really be a significant performance
hit in the parsing of a query? Let's say that it takes a millisecond
extra to do the right thing with a number. Does it matter? How many
queries per second can we expect to process anyway?

I don't know of standard ways to read in 64-bit integers.

Oh, sorry -- I wasn't being clear. Of course you don't, since we
don't even have standard 64-bit integers. My point was that I
couldn't see was how special handling of constants during the parsing
of the query string could have significant performance impact, even if
you did read them as 64-bit integers, which would mean adding bignum
code to PostgreSQL. In other words, performance isn't the argument to
be used against doing the right thing during parsing.

As for implementation, I was thinking more along the lines of:

{integer} {
char* endptr;

errno = 0;
yylval.ival = strtol((char *)yytext,&endptr,10);
if (*endptr != '\0' || errno == ERANGE) {
errno = 0;
yylval.dval = strtod(((char *)yytext),&endptr);
if (*endptr != '\0' || errno == ERANGE) {
elog(ERROR,"Bad integer input '%s'",yytext);
return (ICONST);
}
CheckFloat8Val(yylval.dval);
return (FCONST);
}
return (ICONST);
}

However: do we really want to do this anyway? If you demand that the
user indicate whether a given constant is integer or real, you lessen
the risk of doing the wrong thing with his or her data. Specifically,
going to floating point means giving up accuracy in representation,
and this may not be something we want to do without user permission.

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

However: do we really want to do this anyway? If you demand that the
user indicate whether a given constant is integer or real, you lessen
the risk of doing the wrong thing with his or her data. Specifically,
going to floating point means giving up accuracy in representation,
and this may not be something we want to do without user permission.

I never auto-convert integer to float unless I have to.

--
Bruce Momjian
maillist@candle.pha.pa.us

That's exactly what I meant with my first mail. IMO this is the correct
way to handle it.

Michael
--
Dr. Michael Meskes, Projekt-Manager | topystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux! | Tel: (+49) 2405/4670-44

We're talking about quite selects/updates/inserts etc. If we say we have
50TPS that makes one transaction every 20 milliseconds. So one more for
parsing makes up for 5% more computation power. I doubt inlining offers
much more speedup.

Michael
--
Dr. Michael Meskes, Projekt-Manager | topystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux! | Tel: (+49) 2405/4670-44

I like this code. I really do. I don't think accuracy is a problem. It
will work as it does right now if the number is a long. Only if it is
out of range it will go to float instead of giving back an error
message. Where could that be a problem?

Michael
--
Dr. Michael Meskes, Projekt-Manager | topystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux! | Tel: (+49) 2405/4670-44

On Sun, 8 Feb 1998, Michael Meskes wrote:

Only if it is out of range it will go to float instead of giving
back an error message. Where could that be a problem?

My worry about that is for the (unlikely, but possible) case where a
user gives a large number as a constant in a query, believing it to be
an integer. If we signal an error, the user will know that the number
was out of range. If not, we may end up doing calculations on floats
that the user wanted done on integers. This may mean losing accuracy,
which is something you don't want to do behind the user's back.

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

I wrote:

My worry about that is for the (unlikely, but possible) case where a
user gives a large number as a constant in a query, believing it to be
an integer. If we signal an error, the user will know that the number
was out of range. If not, we may end up doing calculations on floats
that the user wanted done on integers. This may mean losing accuracy,
which is something you don't want to do behind the user's back.

Something just struck me... How's this for a workaround? It lets you
enter large floats that happen to be integral without any explicit
indication of the fact that they are floats, which was the original
intent of this whole thread, while letting the user know that we're
doing it. In effect, we're saying "sure, you can do this on the fly,
and we'll do the right thing, but please be explicit in stored code".

Here's the modified code -- reindented for email purposes:

{integer} {
char* endptr;

errno = 0;
yylval.ival = strtol((char *)yytext,&endptr,10);
if (*endptr != '\0' || errno == ERANGE) {
errno = 0;
yylval.dval = strtod(((char *)yytext),&endptr);
if (*endptr != '\0' || errno == ERANGE) {
elog(ERROR,"Bad integer input '%s'",yytext);
return (ICONST);
}
elog(NOTICE,"Out of range integer '%s' promoted to float",yytext);
CheckFloat8Val(yylval.dval);
return (FCONST);
}
return (ICONST);
}

I don't know how the NOTICE is treated in all situations, though. If
the user doesn't see it, there isn't much point in having it. What
happens for the various interface methods? Will it be shown to a user
accessing a database from MS Excel using ODBC, for instance?

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

My worry about that is for the (unlikely, but possible) case where a
user gives a large number as a constant in a query, believing it to be
an integer. If we signal an error, the user will know that the number
was out of range. If not, we may end up doing calculations on floats
that the user wanted done on integers. This may mean losing accuracy,
which is something you don't want to do behind the user's back.

Something just struck me... How's this for a workaround? It lets you
enter large floats that happen to be integral without any explicit
indication of the fact that they are floats, which was the original
intent of this whole thread, while letting the user know that we're
doing it. In effect, we're saying "sure, you can do this on the fly,
and we'll do the right thing, but please be explicit in stored code".
I don't know how the NOTICE is treated in all situations, though. If
the user doesn't see it, there isn't much point in having it. What
happens for the various interface methods? Will it be shown to a user
accessing a database from MS Excel using ODBC, for instance?

Per Tom H's suggestion; what do you think Bruce?

postgres=> select 100000000000;
NOTICE: Integer input '100000000000' is out of range; promoted to float
?column?
------------
100000000000
(1 row)

That would alleviate the "hidden" side effects, but still come closer to
doing something helpful...

- Tom

Per Tom H's suggestion; what do you think Bruce?

postgres=> select 100000000000;
NOTICE: Integer input '100000000000' is out of range; promoted to float
?column?
------------
100000000000
(1 row)

That would alleviate the "hidden" side effects, but still come closer to
doing something helpful...

With the NOTICE, I like it.

--
Bruce Momjian
maillist@candle.pha.pa.us

Bruce Momjian wrote:

Per Tom H's suggestion; what do you think Bruce?

postgres=> select 100000000000;
NOTICE: Integer input '100000000000' is out of range; promoted to float
?column?
------------
100000000000
(1 row)

That would alleviate the "hidden" side effects, but still come closer to
doing something helpful...

With the NOTICE, I like it.

Will be done for v6.3 then. I'm finishing up on patches to gram.y to help
with CREATE FUNCTION, etc., wrt type names. Will commit the scan.l changes at
that time...

- Tom

Great. Thanks.

Michael

--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10