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

[Martin Bowes]

Hi Adrian,

That coercion of an empty string into a zero seems a plausible explanation for the behaviour. So the question becomes is the behaviour what you would expect? I’m going to have a hard time selling it to the boss as such!

The multiple row insert syntax has been around for some time, I’m surprised that OpenRoad is having trouble with it.

Marty

From: Adrian Williamson <adrian.williamson at rationalcommerce.com>
Sent: 29 July 2020 08:40
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

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 at ndph.ox.ac.uk<mailto:martin.bowes at ndph.ox.ac.uk>>
Sent: 29 July 2020 08:25
To: aw at rationalcommerce.com<mailto:aw at rationalcommerce.com>; Ingres lists <info-ingres at lists.planetingres.org<mailto:info-ingres at 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 at rationalcommerce.com<mailto:aw at rationalcommerce.com> <aw at rationalcommerce.com<mailto:aw at rationalcommerce.com>>
Sent: 29 July 2020 08:18
To: Martin Bowes <martin.bowes at ndph.ox.ac.uk<mailto:martin.bowes at ndph.ox.ac.uk>>; Ingres lists <info-ingres at lists.planetingres.org<mailto: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/6c08b51e/attachment.html>