Re: Why don't we accept exponential format for integers?

Folks,

Is there any good reason that this works:

postgres=# select ('1e+01'::numeric)::integer
postgres-# ;
int4
------
10

But this doesn't?

postgres=# select '1e+01'::Integer
postgres-# ;
ERROR: invalid input syntax for integer: "1e+01"
LINE 1: select '1e+01'::Integer

... or did we just never implement it?

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> postgres=# select '1e+01'::Integer
> postgres-# ;
> ERROR: invalid input syntax for integer: "1e+01"

I have never heard of any programming system anywhere that accepts such
a syntax for integers (assuming it distinguishes integers from other
numbers at all). I'm not excited about being the first. Why does this
error surprise you? It doesn't seem particularly different from arguing
that 1.000 should be considered an integer, which strikes me as a
seriously bad idea.

regards, tom lane

In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > postgres=# select '1e+01'::Integer
> > postgres-# ;
> > ERROR: invalid input syntax for integer: "1e+01"
>
> I have never heard of any programming system anywhere that accepts such
> a syntax for integers (assuming it distinguishes integers from other
> numbers at all). I'm not excited about being the first. Why does this
> error surprise you? It doesn't seem particularly different from arguing
> that 1.000 should be considered an integer, which strikes me as a
> seriously bad idea.

But
SELECT 1.000::Integer;
works. And so does
SELECT 1.234::Integer;
which I find just as dangerous as
SELECT '1.234e+01'::Integer;

One of the exciting (but possibly wrong) arguments in favor of this is the
fact that some programming languages will output integers in exponential
notation when the numbers are very large (PHP is the only example that
comes to mind, but it's a pretty common language) which may cause numbers
formatted thusly to be included in queries without the programmers prior
realization.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> I have never heard of any programming system anywhere that accepts such
>> a syntax for integers (assuming it distinguishes integers from other
>> numbers at all). I'm not excited about being the first.

> But
> SELECT 1.000::Integer;
> works. And so does

Sure. That's a datatype conversion, though; it's not a case of taking
the value as an integer natively.

> One of the exciting (but possibly wrong) arguments in favor of this is the
> fact that some programming languages will output integers in exponential
> notation when the numbers are very large (PHP is the only example that
> comes to mind, but it's a pretty common language)

Just another example of the fact that PHP was designed by incompetent
amateurs :-(

http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html

regards, tom lane

On Fri, 2010-12-17 at 14:35 -0500, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > postgres=# select '1e+01'::Integer
> > postgres-# ;
> > ERROR: invalid input syntax for integer: "1e+01"
>
> I have never heard of any programming system anywhere that accepts
> such
> a syntax for integers (assuming it distinguishes integers from other
> numbers at all). I'm not excited about being the first. Why does
> this
> error surprise you? It doesn't seem particularly different from
> arguing
> that 1.000 should be considered an integer, which strikes me as a
> seriously bad idea.
>
Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56)
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> print int(1e+01)
10
>>>

Sincerely,

Joshua D. Drake

> regards, tom lane
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> On Fri, 2010-12-17 at 14:35 -0500, Tom Lane wrote:
>> I have never heard of any programming system anywhere that accepts
>> such
>> a syntax for integers (assuming it distinguishes integers from other
>> numbers at all).

> Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56)
> [GCC 4.4.5] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
> print int(1e+01)
> 10

That's a conversion, not an integer natively.

regards, tom lane

> Just another example of the fact that PHP was designed by incompetent
> amateurs :-(
>
> http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html

Unless I am misunderstanding the argument... perl and python both
support what is suggested here.

jd(at)jd-desktop:~$ perl -e 'print int('1e+01')';
10

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

>>>> print int(1e+01)
> 10
>>>>
>

That isn't building an integer: it is creating a float and casting to an int.

try:

int( 1e100 )

Best,
Nathan

On Fri, Dec 17, 2010 at 3:31 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
>> Just another example of the fact that PHP was designed by incompetent
>> amateurs :-(
>>
>> http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html
>
> Unless I am misunderstanding the argument... perl and python both
> support what is suggested here.
>
> jd(at)jd-desktop:~$ perl -e 'print int('1e+01')';
> 10

You're misunderstanding the argument.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Python 2.6.1 (r261:67515, Jun 24 2010, 21:47:49)
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> isinstance(10,int)
True
>>> isinstance(1e10,int)
False

--
-- Christophe Pettus
xof(at)thebuild(dot)com

Christophe Pettus <xof(at)thebuild(dot)com> writes:
> Python 2.6.1 (r261:67515, Jun 24 2010, 21:47:49)
> [GCC 4.2.1 (Apple Inc. build 5646)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> isinstance(10,int)
> True
> isinstance(1e10,int)
> False

Right. Possibly a more concrete reason why this doesn't seem like a
great idea:

1e+1 integer?
1e+0 integer?
1e-0 integer?
1e-1 definitely not an integer

regards, tom lane

On Fri, Dec 17, 2010 at 12:16 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
>> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> > postgres=# select '1e+01'::Integer
>> > postgres-# ;
>> > ERROR:  invalid input syntax for integer: "1e+01"
>>
>> I have never heard of any programming system anywhere that accepts such
>> a syntax for integers (assuming it distinguishes integers from other
>> numbers at all).  I'm not excited about being the first.  Why does this
>> error surprise you?  It doesn't seem particularly different from arguing
>> that 1.000 should be considered an integer, which strikes me as a
>> seriously bad idea.
>
> But
> SELECT 1.000::Integer;
> works.  And so does
> SELECT 1.234::Integer;

And so does:
SELECT 1.23e+01::Integer

> which I find just as dangerous as
> SELECT '1.234e+01'::Integer;

Add quotes to either of the other two, and then they don't work either.

Cheers,

Jeff

In response to "Joshua D. Drake" <jd(at)commandprompt(dot)com>:

>
> > Just another example of the fact that PHP was designed by incompetent
> > amateurs :-(
> >
> > http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html
>
> Unless I am misunderstanding the argument... perl and python both
> support what is suggested here.
>
> jd(at)jd-desktop:~$ perl -e 'print int('1e+01')';
> 10

Try the equivalent of:

$i = 1; while ($i < 1000000000000000000000000) { $i *= 10; echo $i . "\n";}

In languages other than PHP. In PHP the output is:

10
100
1000
10000
100000
1000000
10000000
100000000
1000000000
10000000000
100000000000
1000000000000
10000000000000
100000000000000
1000000000000000
10000000000000000
100000000000000000
1000000000000000000
1.0E+19
1.0E+20
1.0E+21
1.0E+22
1.0E+23
1.0E+24

The result being that a construct such as:
$query = "INSERT INTO some_table (int_column) VALUES ($i)";

Could end up being:
$query = "INSERT INTO some_table (int_column) VALUES (1.0E+24)";

Now, I want to make it clear that I'm not arguing that this is correct.
PHP's bizarre ideas about what constitutes types is one of my biggest
gripes against that language. I'm only pointing it out because it's
a clear case where _not_ having the suggested conversion might cause
errors in a program. Again, I'd be liable to argue that in such a
case the error is with PHP and not PostgreSQL, but if many other
languages behave the same, it might be a legitimate argument in favor
of supporting such an automatic conversion.

A strong argument against this is the fact that I've had problems with
MSSQL converting strings such as 1034297182365013256e109613205819326501
(i.e., that's an unfortunate hexidecimal string, not an exponential
number) into numbers and then returning overflow errors, which I find
extremely annoying and outright wrong, and which requires hacks in the
application code to prevent.

Now that I consider those points, I think I'm actually arguing on Tom's
side, that we should not support such a conversion ... actually, I'm
not sure what side of this I'm on right now, I'm just providing
evidence ...

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

In response to Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> On Fri, Dec 17, 2010 at 12:16 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> > In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >
> >> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> >> > postgres=# select '1e+01'::Integer
> >> > postgres-# ;
> >> > ERROR:  invalid input syntax for integer: "1e+01"
> >>
> >> I have never heard of any programming system anywhere that accepts such
> >> a syntax for integers (assuming it distinguishes integers from other
> >> numbers at all).  I'm not excited about being the first.  Why does this
> >> error surprise you?  It doesn't seem particularly different from arguing
> >> that 1.000 should be considered an integer, which strikes me as a
> >> seriously bad idea.
> >
> > But
> > SELECT 1.000::Integer;
> > works.  And so does
> > SELECT 1.234::Integer;
>
> And so does:
> SELECT 1.23e+01::Integer
>
>
> > which I find just as dangerous as
> > SELECT '1.234e+01'::Integer;
>
> Add quotes to either of the other two, and then they don't work either.

Ah ... I wasn't looking carefully enough, that changes the landscape
quite a bit ...

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On 12/17/10 12:46 PM, Jeff Janes wrote:
>
> And so does:
> SELECT 1.23e+01::Integer
>
>
>> > which I find just as dangerous as
>> > SELECT '1.234e+01'::Integer;
>
> Add quotes to either of the other two, and then they don't work either.

Well, that's stupidly arbitrary. If we're not going to accept
'1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 12/17/10 12:46 PM, Jeff Janes wrote:
>> Add quotes to either of the other two, and then they don't work either.

> Well, that's stupidly arbitrary. If we're not going to accept
> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.

It's not arbitrary in the slightest. One is a run-time type conversion;
the other is a question of what strings the type-specific input routine
for integer will accept.

regards, tom lane

On Sat, Dec 18, 2010 at 00:05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Well, that's stupidly arbitrary.  If we're not going to accept
> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.

Not surprising to me. This is how many languages implement type conversion.

Python:
>>> int(1.234e+01)
12
>>> int('1.234e+01')
ValueError: invalid literal for int() with base 10: '1.234e+01'

PHP:
print intval(1.234e+01) . "\n";
print intval('1.234e+01') . "\n";
gives:
12
1
Because PHP's int->string cast terminates parsing when it sees an
unrecognized character.

Java makes the difference quite explicit and obvious:
int a = (int)1.234e+01;
int a = Integer.parseInt("1.234e+01);

Regards,
Marti

> > And so does:
> > SELECT 1.23e+01::Integer
> >
> >
> >> > which I find just as dangerous as
> >> > SELECT '1.234e+01'::Integer;
> >
> > Add quotes to either of the other two, and then they don't work either.
>
> Well, that's stupidly arbitrary. If we're not going to accept
> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.
>

Isn't this a case of an explicit cast? Shouldn't our answer to 1.234e+1::Integer be the same as CAST(1234e+1 AS Integer)?
Which is legal ISO SQL, as far as I can see.

>> Well, that's stupidly arbitrary. If we're not going to accept
>> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.
>
> It's not arbitrary in the slightest. One is a run-time type conversion;
> the other is a question of what strings the type-specific input routine
> for integer will accept.

Oh, *I* understand the difference. Any app developer is going to see it
as stupidly arbitrary, though.

Anyway, this answered my basic question.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Oh, *I* understand the difference.  Any app developer is going to see it
> as stupidly arbitrary, though.

Speaking as someone who spent 9 years doing app development, I dispute
the word "any".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/17/10 3:34 PM, Robert Haas wrote:
> On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Oh, *I* understand the difference. Any app developer is going to see it
>> as stupidly arbitrary, though.
>
> Speaking as someone who spent 9 years doing app development, I dispute
> the word "any".

Ok, "lots" then. If it's not a good idea to enable that functionality,
then it would be nice to come up with some way to make it more clear why
it's failing.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Dec 17, 2010 at 6:49 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 12/17/10 3:34 PM, Robert Haas wrote:
>> On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> Oh, *I* understand the difference.  Any app developer is going to see it
>>> as stupidly arbitrary, though.
>>
>> Speaking as someone who spent 9 years doing app development, I dispute
>> the word "any".
>
> Ok, "lots" then.

Fair enough.

> If it's not a good idea to enable that functionality,
> then it would be nice to come up with some way to make it more clear why
> it's failing.

I guess I'm about to show my arrogance and utter lack of sympathy for
the common man here, but it's hard for me to imagine anyone who has
any experience at all as a programmer seeing the message ERROR:
invalid input syntax for integer: "1e+01" and having NO idea what the
problem could possibly be. I can imagine them thinking, as you said,
that it's stupid and arbitrary, even though I don't agree with that
myself. But I have a hard time imagining someone looking at that
error and not knowing what they need to do to correct it, unless they
don't know the meaning of the word "integer".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/17/2010 07:03 PM, Robert Haas wrote:
>
>
>> If it's not a good idea to enable that functionality,
>> then it would be nice to come up with some way to make it more clear why
>> it's failing.
> I guess I'm about to show my arrogance and utter lack of sympathy for
> the common man here, but it's hard for me to imagine anyone who has
> any experience at all as a programmer seeing the message ERROR:
> invalid input syntax for integer: "1e+01" and having NO idea what the
> problem could possibly be. I can imagine them thinking, as you said,
> that it's stupid and arbitrary, even though I don't agree with that
> myself. But I have a hard time imagining someone looking at that
> error and not knowing what they need to do to correct it, unless they
> don't know the meaning of the word "integer".

Well, maybe. Also, giving the sort of feedback Josh seems to want
probably would not be nearly as easy as he seems to think, ISTM.

cheers

andrew

> Well, maybe. Also, giving the sort of feedback Josh seems to want
> probably would not be nearly as easy as he seems to think, ISTM.

Oh, I don't think it would be easy. I can't think, right now, of a good
way to do it.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Dec 17, 2010 at 7:35 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> Well, maybe. Also, giving the sort of feedback Josh seems to want
>> probably would not be nearly as easy as he seems to think, ISTM.
>
> Oh, I don't think it would be easy.  I can't think, right now, of a good
> way to do it.

I mean, it wouldn't be enormously difficult to look for something of
the form \d+(\.\d+)?e\d+ and give a different error message for that
case, like "scientific notation is not allowed for integer inputs",
but I don't think it's really worth it. A person who can't figure it
out without that is probably more confused than we're going to be able
to fix with a one-line error message.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company