<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:"MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"\@MS Mincho";
panose-1:2 2 6 9 4 2 5 8 3 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
h6
{mso-style-priority:9;
mso-style-link:"Heading 6 Char";
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:7.5pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.Heading6Char
{mso-style-name:"Heading 6 Char";
mso-style-priority:9;
mso-style-link:"Heading 6";
font-weight:bold;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.NolangChar
{mso-style-name:"Nolang Char";
mso-style-link:Nolang;
font-family:"Courier New";
color:black;}
p.Nolang, li.Nolang, div.Nolang
{mso-style-name:Nolang;
mso-style-link:"Nolang Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:13.0pt;
font-family:"Courier New";
color:black;}
p.s499, li.s499, div.s499
{mso-style-name:s499;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.s505, li.s505, div.s505
{mso-style-name:s505;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle24
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle25
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle26
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle27
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle28
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">I would have expected it somewhere in type coercion in comparisons.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Ian Kirkham [mailto:Ian.Kirkham@actian.com]
<br>
<b>Sent:</b> 11 May 2016 14:41<br>
<b>To:</b> Martin Bowes; info-ingres@lists.planetingres.org<br>
<b>Subject:</b> RE: [Info-ingres] When is an empty string an integer?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Hi Marty,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">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. :-(</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Regards,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Ian</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">Integer Literals</span></b><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">Integer literals are specified by a sequence of digits and an optional sign, in the following format:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">[+|-] digit {digit} [e digit]</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">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.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">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:</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:12.0pt;font-family:"Times New Roman","serif"">1e5</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> Martin Bowes [<a href="mailto:martin.bowes@ndph.ox.ac.uk">mailto:martin.bowes@ndph.ox.ac.uk</a>]
<br>
<b>Sent:</b> 11 May 2016 14:17<br>
<b>To:</b> Ian Kirkham <<a href="mailto:Ian.Kirkham@actian.com">Ian.Kirkham@actian.com</a>>;
<a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> RE: [Info-ingres] When is an empty string an integer?</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Hi Ian,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">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.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">FYI. Geraint Jones has informed me that:</span><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><i><span style="color:#1F497D">Various DBMS’s disagree on how to handle cast(‘’ as integer).</span></i><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><i><span style="color:#1F497D"> </span></i><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><i><span style="color:#1F497D">Ingres : 0.</span></i><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><i><span style="color:#1F497D">Oracle : NULL.</span></i><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><i><span style="color:#1F497D">Postgres : Error.</span></i><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><i><span style="color:#1F497D">MySQL : 0.</span></i><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">Marty</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Ian Kirkham [<a href="mailto:Ian.Kirkham@actian.com">mailto:Ian.Kirkham@actian.com</a>]
<br>
<b>Sent:</b> 11 May 2016 13:51<br>
<b>To:</b> Martin Bowes; <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> RE: [Info-ingres] When is an empty string an integer?</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Hi Marty,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Historically Ingres has always treated a string of just zero or more space characters as numeric 0 on conversion to integers.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">MS excel does the same so it must be an industry standard -
</span><span style="font-family:Wingdings;color:#1F497D;mso-fareast-language:EN-US">J</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Not something we could easily correct now.</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Regards,</span><o:p></o:p></p>
<p class="MsoNormal"><span style="color:#1F497D;mso-fareast-language:EN-US">Ian</span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> <a href="mailto:info-ingres-bounces@lists.planetingres.org">
info-ingres-bounces@lists.planetingres.org</a> [<a href="mailto:info-ingres-bounces@lists.planetingres.org">mailto:info-ingres-bounces@lists.planetingres.org</a>]
<b>On Behalf Of </b>Martin Bowes<br>
<b>Sent:</b> 11 May 2016 13:42<br>
<b>To:</b> <a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a><br>
<b>Subject:</b> [Info-ingres] When is an empty string an integer?</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Hi All,<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">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.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">What is the expected result of: select integer(varchar('', 1))<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">The answer is zero.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">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:<o:p></o:p></p>
<p class="MsoNormal">E_US100F String cannot be converted to numeric due to incorrect syntax.<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Martin Bowes<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
</body>
</html>