Forum: >>> Magnum BBS <<<

[Info-ingres] why is an empty string an integer

From Martin Bowes@21:1/5 to Ingres lists on Wed Jul 29 07:12:16 2020

Forgot to mention this is on version II 11.0.0 (a64.lnx/100) +p15426

I also see it on: II 11.1.0 (a64.lnx/100) + p15550

Marty
From: Martin Bowes <martin.bowes@ndph.ox.ac.uk>
Sent: 29 July 2020 08:02
To: Ingres lists <info-ingres@lists.planetingres.org>
Subject: [Info-ingres] why is an empty string an integer

Hi All,

I may have come across this before, but it's early and I haven't had my coffee.

declare global temporary table fred(
a varchar(20)
) on commit preserve rows with norecovery;
Executing . . .

continue
* * insert into fred values (null), (''), ('1'), ('13-jan-2020');
Executing . . .

(4 rows)
continue
* * * * * * * * select case when a is null then 'null'
when a = '' then 'empty string'
else a end as data,
case when a is integer then 'integer' else 'not integer' end as is_integer,
case when a is float then 'float' else 'not float' end as is_float,
case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate
from fred;
Executing . . .

��
�data �is_integer �is_float �is_ansidate � ��
�null �not integer�not float�not ansidate�
�empty string �integer �float �not ansidate�
�1 �integer �float �not ansidate�
�13-jan-2020 �not integer�not float�ansidate � ��
(4 rows)
continue

Now the tests with nulls behave as I expect. But the empty string cases I did not expect. Is this a bug? Documented behaviour?

Martin Bowes

<html 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=koi8-r">
<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:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
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.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle20
{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>

</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
Forgot to mention this is on version
II 11.0.0 (a64.lnx/100) +p15426<o:p></o:p>
<o:p> </o:p> I also see it on:
II 11.1.0 (a64.lnx/100) + p15550<o:p></o:p>
<o:p> </o:p> Marty<o:p></o:p> <div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
From: Martin Bowes <martin.bowes@ndph.ox.ac.uk>

Sent: 29 July 2020 08:02 
To: Ingres lists <info-ingres@lists.planetingres.org> Subject: [Info-ingres] why is an empty string an integer<o:p></o:p>
</div>
</div>
<o:p> </o:p>
Hi All,<o:p></o:p>
 <o:p></o:p>
I may have come across this before, but it’s early and I haven’t had my coffee.<o:p></o:p>
 <o:p></o:p>
declare global temporary table fred(<o:p></o:p>
    a varchar(20)<o:p></o:p>
) on commit preserve rows with norecovery;<o:p></o:p>
Executing . . .<o:p></o:p>
 <o:p></o:p>
continue<o:p></o:p>
* * insert into fred values (null), (''), ('1'), ('13-jan-2020');<o:p></o:p>
Executing . . .<o:p></o:p>
 <o:p></o:p>
(4 rows)<o:p></o:p>
continue<o:p></o:p>
* * * * * * * * select case when a is null then 'null'<o:p></o:p>
            when a = '' then 'empty string'<o:p></o:p>
            else a end as data,<o:p></o:p>
       case when a is integer then 'integer' else 'not integer' end as is_integer,<o:p></o:p>
      case when a is float then 'float' else 'not float' end as is_float,<o:p></o:p>
       case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate<o:p></o:p></

from fred;<o:p></o:p>
Executing . . .<o:p></o:p>
 <o:p></o:p>
 <o:p></o:p>
��<o:p></o:p>
�data                �is_integer �is_float �is_ansidate �<o:p></o:p>
��<o:p></o:p>
�null                �not integer�not float�not ansidate�<o:p></o:p>
�empty string        �integer   
�float    �not ansidate�<o:p></o:p>
�1                   �integer  &
nbsp; �float    �not ansidate�<o:p></o:p>
�13-jan-2020         �not integer�not float�ansidate    �<o:p></o:p>
��<o:p></o:p>
(4 rows)<o:p></o:p>
continue<o:p></o:p>
 <o:p></o:p>
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>
 <o:p></o:p>
Martin Bowes<o:p></o:p>
</div>
</body>
</html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Martin Bowes@21:1/5 to Ingres lists on Wed Jul 29 07:01:41 2020

Hi All,

I may have come across this before, but it's early and I haven't had my coffee.

declare global temporary table fred(
a varchar(20)
) on commit preserve rows with norecovery;
Executing . . .

continue
* * insert into fred values (null), (''), ('1'), ('13-jan-2020');
Executing . . .

(4 rows)
continue
* * * * * * * * select case when a is null then 'null'
when a = '' then 'empty string'
else a end as data,
case when a is integer then 'integer' else 'not integer' end as is_integer,
case when a is float then 'float' else 'not float' end as is_float,
case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate
from fred;
Executing . . .

��
�data �is_integer �is_float �is_ansidate � ��
�null �not integer�not float�not ansidate�
�empty string �integer �float �not ansidate�
�1 �integer �float �not ansidate�
�13-jan-2020 �not integer�not float�ansidate � ��
(4 rows)
continue

Now the tests with nulls behave as I expect. But the empty string cases I did not expect. Is this a bug? Documented behaviour?

Martin Bowes

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=koi8-r">
<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:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@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]-->
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
Hi All,<o:p></o:p>
<o:p> </o:p>
I may have come across this before, but it’s early and I haven’t had my coffee.<o:p></o:p>
<o:p> </o:p>
declare global temporary table fred(<o:p></o:p>
    a varchar(20)<o:p></o:p>
) on commit preserve rows with norecovery;<o:p></o:p>
Executing . . .<o:p></o:p>
<o:p> </o:p>
continue<o:p></o:p>
* * insert into fred values (null), (''), ('1'), ('13-jan-2020');<o:p></o:p>
Executing . . .<o:p></o:p>
<o:p> </o:p>
(4 rows)<o:p></o:p>
continue<o:p></o:p>
* * * * * * * * select case when a is null then 'null'<o:p></o:p>
            when a = '' then 'empty string'<o:p></o:p>
            else a end as data,<o:p></o:p>
       case when a is integer then 'integer' else 'not integer' end as is_integer,<o:p></o:p>
      case when a is float then 'float' else 'not float' end as is_float,<o:p></o:p>
       case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate<o:p></o:p></

from fred;<o:p></o:p>
Executing . . .<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
��<o:p></o:p>
�data                �is_integer �is_float �is_ansidate �<o:

</o:p>

��<o:p></o:p>
�null                �not integer�not float�not ansidate�<o:

</o:p>

�empty string        �integer   
�float    �not ansidate�<o:p></o:p>
�1                   �integer  &
nbsp; �float    �not ansidate�<o:p></o:p>
�13-jan-2020         �not integer�not float�ansidate    �<o:p></o:p>
��<o:p></o:p>
(4 rows)<o:p></o:p>
continue<o:p></o:p>
<o:p> </o:p>
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>
<o:p> </o:p>
Martin Bowes<o:p></o:p>
</div>
</body>
</html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From aw@rationalcommerce.com@21:1/5 to Martin Bowes on Wed Jul 29 08:17:59 2020

To: info-ingres@lists.planetingres.org (Ingres lists)

MorningIf you use zero instead of 1 do you get the same?I must admit i have not seen that 'when a is integer/float' syntax before.But then i use openroad so our syntax is about 20 years behind.Adrian------ Original message------From: Martin BowesDate:
Wed, 29 Jul 2020 08:01To: Ingres lists;Cc: Subject:[Info-ingres] why is an empty string an integer

Hi All,

I may have come across this before, but it’s early and I haven’t had my coffee.

declare global temporary table fred(
 a varchar(20)
) on commit preserve rows with norecovery;
Executing . . .

continue
* * insert into fred values (null), (''), ('1'), ('13-jan-2020');
Executing . . .

(4 rows)
continue
* * * * * * * * select case when a is null then 'null'
 when a = '' then 'empty string'
 else a end as data,
 case when a is integer then 'integer' else 'not integer' end as is_integer,
 case when a is float then 'float' else 'not float' end as is_float, case when a is ansidate then
'ansidate' else 'not ansidate' end as is_ansidate
from fred;
Executing . . .

┌────────────────────┬───────────┬─────────┬────────────┐
│data │is_integer │is_float │is_ansidate │
├────────────────────┼───────────┼─────────┼────────────┤
│null │not integer│not float│not ansidate│
│empty string │integer │float │not ansidate│
│1 │integer │float │not ansidate│
│13-jan-2020 │not integer│not float│ansidate │ └────────────────────┴───────────┴─────────┴────────────┘
(4 rows)
continue

Now the tests with nulls behave as I expect. But the empty string cases I did not expect. Is this a bug? Documented behaviour?

Martin Bowes

<html><head>

<style></style>
</head>
<body>
<div style="font-size: 10pt;"><div dir="auto">Morning</div><div dir="auto"> </div><div dir="auto">If you use zero instead of 1 do you get the same?</div><div dir="auto"> </div><div dir="auto">I must admit i have not seen that 'when a is integer/
float' syntax before.</div><div dir="auto"> </div><div dir="auto">But then i use openroad so our syntax is about 20 years behind.</div><div dir="auto"> </div><div dir="auto">Adrian</div></div><div style="font-size: 10pt;"><div id="LGEmailHeader"
dir="auto"><div dir="auto"> </div><div
dir="auto">------ Original message------</div><div dir="auto">From: Martin Bowes<martin.bowes@ndph.ox.ac.uk></martin.bowes@ndph.ox.ac.uk></div><div dir="auto">Date: Wed, 29 Jul 2020 08:01</div><div dir="auto">To: Ingres lists;</div><
div dir="auto">Cc: </div><div dir="auto">Subject:[Info-ingres] why is an empty string an integer</div><div dir="auto"> </div></div><div dir="auto">
<div class="WordSection1">
Hi All,<o:p></o:p>
<o:p> </o:p>
I may have come across this before, but it’s early and I haven’t had my coffee.<o:p></o:p>
<o:p> </o:p>
declare global temporary table fred(<o:p></o:p>
    a varchar(20)<o:p></o:p>
) on commit preserve rows with norecovery;<o:p></o:p>
Executing . . .<o:p></o:p>
<o:p> </o:p>
continue<o:p></o:p>
* * insert into fred values (null), (''), ('1'), ('13-jan-2020');<o:p></o:p>
Executing . . .<o:p></o:p>
<o:p> </o:p>
(4 rows)<o:p></o:p>
continue<o:p></o:p>
* * * * * * * * select case when a is null then 'null'<o:p></o:p>
            when a = '' then 'empty string'<o:p></o:p>
            else a end as data,<o:p></o:p>
       case when a is integer then 'integer' else 'not integer' end as is_integer,<o:p></o:p>
      case when a is float then 'float' else 'not float' end as is_float,<o:p></o:p>
       case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate<o:p></o:p></

from fred;<o:p></o:p>
Executing . . .<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
┌────────────────────┬───────────┬─────────┬─�
��──────────┐<o:p></o:p>
│data   
            │is_integer │is_float │is_ansidate │<o:p></o:p>
├────────────────────┼───────────┼─────────┼─�
��──────────┤<o:p></o:p>
│null                │not integer│not float│not
ansidate│<o:p></o:p>
│empty string        │integer  &
nbsp; │float    │not ansidate│<o:p></o:p>
│1                   │integer    │float    │not ansidate│<o:

</o:p>

│13-jan-2020         │not integer│not float│ansidate    │<o:p></o:p>
└────────────────────┴───────────┴─────────┴─�
��──────────┘<o:p></o:p>
(4 rows)<o:p></o:p>
continue<o:p></o:p>
<o:p> </o:p>
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>
<o:p> </o:p>
Martin Bowes<o:p></o:p>
</div>
</div></div>

</body></html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Adrian Williamson@21:1/5 to Martin Bowes on Wed Jul 29 08:39:37 2020

To: info-ingres@lists.planetingres.org (Ingres lists)

This is a multipart message in MIME format.

Hi,

My guess is blank string is being translated to zero:

* select integer('');\g

Executing . . .

+-------------+

|col1 |

+-------------+

| 0|

+-------------+

Ingres 10.1 did not want to play with these new predicates, and my Ingres 11 did as yours.

OpenROAD gave up here:

insert into fred values (null), (''), ('1'), ('13-jan-2020');

^

%% Error at line 11

E_W400F0 Syntax error. The last symbol read was ','.

Go figure.

That’s why I don’t look at new SQL features – no point.

Cheers

Adrian

From: Martin Bowes <martin.bowes@ndph.ox.ac.uk>
Sent: 29 July 2020 08:25
To: aw@rationalcommerce.com; Ingres lists <info-ingres@lists.planetingres.org> Subject: RE: [Info-ingres] why is an empty string an integer

Hi Adrian,

Yes zero works fine.

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.

Marty

From: aw@rationalcommerce.com <mailto:aw@rationalcommerce.com> <aw@rationalcommerce.com <mailto:aw@rationalcommerce.com> >
Sent: 29 July 2020 08:18
To: Martin Bowes <martin.bowes@ndph.ox.ac.uk <mailto:martin.bowes@ndph.ox.ac.uk> >; Ingres lists <info-ingres@lists.planetingres.org <mailto:info-ingres@lists.planetingres.org> >
Subject: Re: [Info-ingres] why is an empty string an integer

Morning

If you use zero instead of 1 do you get the same?

I must admit i have not seen that 'when a is integer/float' syntax before.

But then i use openroad so our syntax is about 20 years behind.

Adrian

------ Original message------

From: Martin Bowes

Date: Wed, 29 Jul 2020 08:01

To: Ingres lists;

Cc:

Subject:[Info-ingres] why is an empty string an integer

Hi All,

I may have come across this before, but it’s early and I haven’t had my coffee.

declare global temporary table fred(

a varchar(20)

) on commit preserve rows with norecovery;

Executing . . .

continue

* * insert into fred values (null), (''), ('1'), ('13-jan-2020');

Executing . . .

(4 rows)

continue

* * * * * * * * select case when a is null then 'null'

when a = '' then 'empty string'

else a end as data,

case when a is integer then 'integer' else 'not integer' end as is_integer,

case when a is float then 'float' else 'not float' end as is_float,

case when a is ansidate then 'ansidate' else 'not ansidate' end as is_ansidate

from fred;

Executing . . .

┌────────────────────┬───────────┬─────────┬────────────┐

│data │is_integer │is_float │is_ansidate │

├────────────────────┼───────────┼─────────┼────────────┤

│null │not integer│not float│not ansidate│

│empty string │integer │float │not ansidate│

│1 │integer │float │not ansidate│

│13-jan-2020 │not integer│not float│ansidate │

└────────────────────┴───────────┴─────────┴────────────┘

(4 rows)

continue

Now the tests with nulls behave as I expect. But the empty string cases I did not expect. Is this a bug? Documented behaviour?

Martin Bowes

<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]--></head><body lang=EN-GB link="#0563C1" vlink="#954F72"><div class=WordSection1>Hi,<o:p></o:p><o:p> </o:p>My guess
is blank string is being translated to zero:<o:p></o:p><o:p> </o:p>* select integer('');\g<o:p></o:p>Executing . . .<o:p></o:p><o:p> </o:p><o:p> </o:p>
+-------------+<o:p></o:p>|
col1 |<o:p></o:p>+-------------+<o:p></o:p>| 0|<o:p></o:p>+-------------+<o:p></o:p><o:p> </o:p>Ingres 10.1 did not want
to play with these new predicates, and my Ingres 11 did as yours.<o:p></o:p><o:p> </o:p>OpenROAD gave up here:<o:p></o:p><o:p> </o:

<o:p> </o:p><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> �
� ^<o:p></o:p>%% Error at line 11<o:p></o:p>E_W400F0 Syntax error. The last symbol read was ','.<o:p></o:p><o:p> </o:p>Go figure.<o:p></o:p><o:p>&
nbsp;</o:p>That’s why I don’t look at new SQL features – no point.<o:p></o:p><o:p> </o:p>Cheers<o:p></o:p><o:p> </o:p>Adrian<o:p></o:

<o:p> </o:p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=

MsoNormal>From: Martin Bowes <martin.bowes@ndph.ox.ac.uk> Sent: 29 July
2020 08:25 To: aw@rationalcommerce.com; Ingres lists <info-ingres@lists.planetingres.org> Subject: RE: [Info-ingres] why is an empty string an integer<o:p></o:p></div></div><o:p> </o:p>Hi Adrian,<o:p></o:p><o:p> </o:p>Yes zero works fine.<o:p></o:p><o:p> </o:p>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><o:p> </o:p>Marty<o:p></o:p><o:p> </o:p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'>From: <a href="mailto:aw@rationalcommerce.com">aw@rationalcommerce.com</a> <<
a href="mailto:aw@rationalcommerce.com">aw@rationalcommerce.com</a>> Sent: 29 July 2020 08:18 To: 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>> Subject: Re: [Info-ingres] why is an empty string an integer<o:p></o:p></div></div><o:p> </o:p><div><div>Morning<o:p></o:p></div><div><o:p> </o:p></div><div>If you use zero instead of 1 do you get the
same?<o:p></o:p></div><div><o:p> </o:p></div><div>I must admit i have not seen that 'when a is integer/float' syntax before.<o:

</o:p></div><div><o:p> </o:p></div><div>But then i use openroad so our syntax is about 20 years behind.<o:p></o:p><

/div><div><o:p> </o:p></div><div>Adrian<o:p></o:p></div></div><div><div id=LGEmailHeader><div><o:p> </o:p></div><div>------ Original message------<o:p></o:p></div><div>From: Martin Bowes<o:p></o:p></div><div>Date: Wed, 29 Jul 2020 08:01<o:p></o:p></div><div>To: Ingres lists;<o:p></o:p></div><div>Cc: <o:p></o:p></div><div>Subject:[Info-ingres] why is an empty string an integer<o:p></o:p></div><div><o:p> </o:p></

</div><div><div>Hi All,<o:p></o:p><span style='font-

size:10.0pt'> <o:p></o:p>I may have come across this before, but it’s early and I haven’t had my coffee.<o:p></o:p>
 <o:p></o:p>declare global temporary table fred(<o:p></o:p>    a varchar(20)<o:p></o:p>) on commit preserve rows with norecovery;<o:p></o:p>Executing . . .<o:p></o:p> <o:p></o:p>
continue<o:p></o:p>* * insert into fred values (
null), (''), ('1'), ('13-jan-2020');<o:p></o:p>
Executing . . .<o:p></o:p> <o:p></o:p>(4 rows)<o:

</o:p>continue<o:p></o:p><p class=

MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;text-autospace:none'>* * * * * * * * select case when a is null then 'null'<o:p></o:p>            when a = '' then '
empty string'<o:p></o:p>    
        else a end as data,<o:p></o:p>       case when a is integer then 'integer' else 'not integer' end as is_integer,<o:p></o:p>      case when a is float then 'float' else 'not float' end as is_float,<o:p></o:

case when a is ansidate then 'ansidate' else '

not ansidate' end as is_ansidate<o:p></o:p>from
fred;<o:p></o:p>Executing . . .<o:p></o:p> <o:p><
/o:p> <o:p></o:p>┌────────────────────┬───────────┬─�
��───────┬────────────┐<o:p></o:p>│data                │is_integer │is_float │is_ansidate │<o:p></o:p>├────────────────────┼───────────┼────�
�────┼────────────┤<o:p></o:p>│null                │not integer│not float│not ansidate│<o:p></o:p>│empty string        │integer 
   │float    │not ansidate│<o:p></o:p>│1                   │integer    │float    │not ansidate│
<o:p></o:p>│13-jan-2020         │not
integer│not float│ansidate    │<o:p></o:p>└────────────────────┴───────────┴─────────┴────────────┘<o:p></o:p>(4 rows)<o:p></o:p>continue<o:p></o:p> <o:p></o:p>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> <o:p></o:p>Martin Bowes<o:p></o:p></div></div></div></div></body></html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Paul White@21:1/5 to Martin Bowes on Wed Jul 29 18:56:06 2020

Copy: info-ingres@lists.planetingres.org (Ingres lists)

Hi Marty,

I expect the result of "a is integer" should align to the result of
int(a).

Compare with MSSQL with some doubtful results.

create table #fred(
a varchar(20));

insert into #fred values (null), (''), ('1'), ('1.0'), ('13-jan-2020');

select case when a is null then 'null'
when a = '' then 'empty string'
else a end as data,
case when try_convert(numeric,a) is null then '' else 'Y' end as Is_Num,
case when try_convert(int,a) is null then '' else 'Y' end as is_int,
case when try_convert(float,a) is null then '' else 'Y' end as is_float,
case when try_convert(money,a) is null then '' else 'Y' end as Is_money,
case when try_convert(datetime,a) is null then '' else 'Y' end as is_date
from #fred;

data Is_Num is_int is_float Is_money is_date -------------------- ------ ------ -------- -------- -------
null
empty string Y Y Y Y
1 Y Y Y Y
1.0 Y Y Y
13-jan-2020 Y

(5 rows affected)

Paul White
&

<div dir="ltr"><div dir="ltr">Hi Marty,<div> </div><div>I expect the result of "a is integer" should align to the result of int(a). </div><div> </div><div>Compare with MSSQL with some doubtful results.</div><div> create table #fred(<

a varchar(20)); insert into #fred values (null), (''), ('1'), ('1.0'), ('13-jan-2020'); select case when a is null then 'null' when a = '' then 'empty string&#

39; else a end as data, case when try_convert(numeric,a) is null then '' else 'Y' end as Is_Num, case when try_convert(int,a) is null then '' else 'Y' end as is_int, case when
try_convert(float,a) is null then '' else 'Y' end as is_float, case when try_convert(money,a) is null then '' else 'Y' end as Is_money, case when try_convert(datetime,a) is null then '' else '
Y' end as is_date from #fred; </div><div>data Is_Num is_int is_float Is_money is_date -------------------- ------ ------ -------- -------- ------- null �
� empty string Y Y Y Y 1 Y Y Y Y 1.0 Y Y Y
 13-jan-2020 Y </div><div> </div><div>(5 rows affected)</div><div> </div><div>Paul White </div></div><div dir="ltr" class="gmail_
signature"><div dir="ltr"><div><div dir="ltr">&</div></div><div dir="ltr"> </div></div></div></div>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Roy Hann@21:1/5 to Martin Bowes on Wed Jul 29 09:01:00 2020

Martin Bowes wrote:

Is this a bug? Documented behaviour?

I don't recall seeing it documented. I don't think it is a bug though.
It might be mis-feature but it would appear to be forced on us. If you
think of "is integer" meaning "is this a value which Ingres will treat
as an integer", Ingres has always treated '' as zero except in the
special case

SELECT ''

which does return an empty string.

Otherwise all these return numbers of one sort or another:

SELECT 1+'';
SELECT 1*'';
SELECT cos('');
SELECT 0**'';

And this gives the predictable error:

SELECT 1/'';

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	293
Nodes:	16 (2 / 14)
Uptime:	235:41:43
Calls:	6,624
Files:	12,172
Messages:	5,319,767

[Info-ingres] why is an empty string an integer

Who's Online

System Info