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

[Adrian Williamson]

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> 
Sent: 29 July 2020 08:25
To: aw at rationalcommerce.com; Ingres lists <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/6e48fc9a/attachment.html>