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

[Martin Bowes]

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 at rationalcommerce.com <aw at rationalcommerce.com>
Sent: 29 July 2020 08:18
To: Martin Bowes <martin.bowes at ndph.ox.ac.uk>; Ingres lists <info-ingres at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20200729/29730fa7/attachment.html>