[Info-ingres] When is an empty string an integer?

[Ian Kirkham]

Hi Marty,
I can’t see this documented in our docset. Where I’d expect to find it is the section on integer literals but this is obviously not correct. :-(
Regards,
Ian
Integer Literals
Integer literals are specified by a sequence of digits and an optional sign, in the following format:
[+|-] digit {digit} [e digit]
Integer literals are represented internally as an integer, smallint, or bigint depending on the value of the literal. A literal in the range -32,768 to +32,767 is represented as a smallint. A literal in the range ‑2,147,483,648 to +2,147,483,647 but outside the range of a smallint is represented as an integer. A literal in the range ‑9,223,372,036,854,775,808 to +9,223,372,036,854,775,807 is represented as a bigint. Values that exceed the range of integers are represented as decimals.
You can specify integers using a simplified scientific notation, similar to the way floating point values are specified. To specify an exponent, follow the integer value with the letter, e, and the value of the exponent. This notation is useful for specifying large values. For example, to specify 100,000 use the exponential notation as follows:
1e5


From: Martin Bowes [mailto:martin.bowes at ndph.ox.ac.uk]
Sent: 11 May 2016 14:17
To: Ian Kirkham <Ian.Kirkham at actian.com>; info-ingres at lists.planetingres.org
Subject: RE: [Info-ingres] When is an empty string an integer?

Hi Ian,

I don’t see this as a bug, just a state of play. Might be a good idea for it to be documented in the SQL guide somewhere but that’s as far as I’d go.

FYI. Geraint Jones has informed me that:
Various DBMS’s disagree on how to handle cast(‘’ as integer).

Ingres : 0.
Oracle : NULL.
Postgres : Error.
MySQL : 0.

Marty

From: Ian Kirkham [mailto:Ian.Kirkham at actian.com]
Sent: 11 May 2016 13:51
To: Martin Bowes; info-ingres at lists.planetingres.org<mailto:info-ingres at lists.planetingres.org>
Subject: RE: [Info-ingres] When is an empty string an integer?

Hi Marty,
Historically Ingres has always treated a string of just zero or more space characters as numeric 0 on conversion to integers.
MS excel does the same so it must be an industry standard - ☺
Not something we could easily correct now.
Regards,
Ian
From: info-ingres-bounces at lists.planetingres.org<mailto:info-ingres-bounces at lists.planetingres.org> [mailto:info-ingres-bounces at lists.planetingres.org] On Behalf Of Martin Bowes
Sent: 11 May 2016 13:42
To: info-ingres at lists.planetingres.org<mailto:info-ingres at lists.planetingres.org>
Subject: [Info-ingres] When is an empty string an integer?

Hi All,

This is probably documented behaviour but I’m having a hard time finding it. So if someone could point me at the document I’d be happy.

What is the expected result of: select integer(varchar('', 1))

The answer is zero.

I must concede I can sort of see this as being necessary, but I’m curious as to why we just didn’t get the standard error:
E_US100F String cannot be converted to numeric due to incorrect syntax.

Martin Bowes




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20160511/e9a83f81/attachment.html>