<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 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
span.EmailStyle22
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.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="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Hi,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>My guess is blank string is being translated to zero:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>* select integer('');\g<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>Executing . . .<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>+-------------+<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>|col1 |<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>+-------------+<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>| 0|<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Lucida Console";mso-fareast-language:EN-US'>+-------------+<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Ingres 10.1 did not want to play with these new predicates, and my Ingres 11 did as yours.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>OpenROAD gave up here:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>insert into fred values (null), (''), ('1'), ('13-jan-2020');<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'> ^<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>%% Error at line 11<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>E_W400F0 Syntax error. The last symbol read was ','.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Go figure.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>That’s why I don’t look at new SQL features – no point.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Cheers<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Adrian<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></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 style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Martin Bowes <martin.bowes@ndph.ox.ac.uk> <br><b>Sent:</b> 29 July 2020 08:25<br><b>To:</b> aw@rationalcommerce.com; Ingres lists <info-ingres@lists.planetingres.org><br><b>Subject:</b> RE: [Info-ingres] why 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='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'>Hi Adrian,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'>Yes zero works fine.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'>The syntax is referred to as ‘predicates’ and there are a whole host of them to do basic tests. I have found them extremely useful in doing data imports from less than reliable third parties.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'>Marty<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></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 style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri",sans-serif'> <a href="mailto:aw@rationalcommerce.com">aw@rationalcommerce.com</a> <<a href="mailto:aw@rationalcommerce.com">aw@rationalcommerce.com</a>> <br><b>Sent:</b> 29 July 2020 08:18<br><b>To:</b> Martin Bowes <<a href="mailto:martin.bowes@ndph.ox.ac.uk">martin.bowes@ndph.ox.ac.uk</a>>; Ingres lists <<a href="mailto:info-ingres@lists.planetingres.org">info-ingres@lists.planetingres.org</a>><br><b>Subject:</b> Re: [Info-ingres] why is an empty string an integer<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal><span style='font-size:10.0pt'>Morning<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>If you use zero instead of 1 do you get the same?<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>I must admit i have not seen that 'when a is integer/float' syntax before.<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>But then i use openroad so our syntax is about 20 years behind.<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>Adrian<o:p></o:p></span></p></div></div><div><div id=LGEmailHeader><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>------ Original message------<o:p></o:p></span></p></div><div><p class=MsoNormal><b><span style='font-size:10.0pt'>From: </span></b><span style='font-size:10.0pt'>Martin Bowes<o:p></o:p></span></p></div><div><p class=MsoNormal><b><span style='font-size:10.0pt'>Date: </span></b><span style='font-size:10.0pt'>Wed, 29 Jul 2020 08:01<o:p></o:p></span></p></div><div><p class=MsoNormal><b><span style='font-size:10.0pt'>To: </span></b><span style='font-size:10.0pt'>Ingres lists;<o:p></o:p></span></p></div><div><p class=MsoNormal><b><span style='font-size:10.0pt'>Cc: </span></b><span style='font-size:10.0pt'><o:p></o:p></span></p></div><div><p class=MsoNormal><b><span style='font-size:10.0pt'>Subject:</span></b><span style='font-size:10.0pt'>[Info-ingres] why is an empty string an integer<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div></div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'>Hi All,<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'>I may have come across this before, but it’s early and I haven’t had my coffee.<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>declare global temporary table fred(</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> a varchar(20)</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>) on commit preserve rows with norecovery;</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>Executing . . .</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> </span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>continue</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>* * insert into fred values (null), (''), ('1'), ('13-jan-2020');</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>Executing . . .</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> </span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>(4 rows)</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>continue</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>* * * * * * * * select case when a is null then 'null'</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> when a = '' then 'empty string'</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> else a end as data,</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> case when a is integer then 'integer' else 'not integer' end as is_integer,</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> case when a is float then 'float' else 'not float' end as is_float,</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>from fred;</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>Executing . . .</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> </span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> </span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>┌────────────────────┬───────────┬─────────┬────────────┐</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>│data │is_integer │is_float │is_ansidate │</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>├────────────────────┼───────────┼─────────┼────────────┤</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>│null │not integer│not float│not ansidate│</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>│empty string │<span style='background:yellow;mso-highlight:yellow'>integer │float</span> │not ansidate│</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>│1 │integer │float │not ansidate│</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>│13-jan-2020 │not integer│not float│ansidate │</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>└────────────────────┴───────────┴─────────┴────────────┘</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>(4 rows)</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>continue</span><span style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'>Now the tests with nulls behave as I expect. But the empty string cases I did not expect. Is this a bug? Documented behaviour?<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'> <o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:10.0pt'>Martin Bowes<o:p></o:p></span></p></div></div></div></div></body></html>