Thread: 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@agliodbs.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@sss.pgh.pa.us>: > Josh Berkus <josh@agliodbs.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@potentialtech.com> writes: > In response to Tom Lane <tgl@sss.pgh.pa.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@agliodbs.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@commandprompt.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@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@commandprompt.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@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@thebuild.com
Christophe Pettus <xof@thebuild.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@potentialtech.com> wrote: > In response to Tom Lane <tgl@sss.pgh.pa.us>: > >> Josh Berkus <josh@agliodbs.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@commandprompt.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@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.janes@gmail.com>: > On Fri, Dec 17, 2010 at 12:16 PM, Bill Moran <wmoran@potentialtech.com> wrote: > > In response to Tom Lane <tgl@sss.pgh.pa.us>: > > > >> Josh Berkus <josh@agliodbs.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@agliodbs.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@agliodbs.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@agliodbs.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@agliodbs.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@agliodbs.com> wrote: > On 12/17/10 3:34 PM, Robert Haas wrote: >> On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus <josh@agliodbs.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@agliodbs.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