like predicate does not match corrected on key column

I am using Ingres 10.1 on Linux (64bit).
Version: Ingres Linux Version II 10.1.0 (a64.lnx/120)NPTL

I noticed today a strange behaviour which I had not observed in the 23 years
of using Ingres before.

table snm is an isam table with key ssnm, varchar(40) and some 600'000 rows.

select * from snm where ssnm like 'a%'
does not match with 'a', only with all lines beginning with 'a..'.

This happens only when the column on which the like predicate operates is
a key column or an index exists on it.

It does not happen when I add a clause WITHOUT CASE, but in embedded SQL
this clause is not supported.

The problem appeasr both in interactive queries and in C embedded SQL.

I had to rewrite my queries as
select * from snm where (ssnm like 'a%' or ssnm = 'a')

It seems to me that this is a bug in Ingres.

[rhann]

It does not happen when I add a clause WITHOUT CASE, but in embedded SQL
this clause is not supported.

There is a lot of syntax not supported by ESQL in 10.1. I find myself becoming quite impatient at the gaps.

In the meantime, you could try using EXECUTE IMMEDIATE to get your SQL past the preprocessor limitations.

Hi Alois,

The query works as you would have expected on my II 10.0.0 (a64.lnx/132)NPTL +p14094 installation.
I ensured in my testing that the search column was the primary key of the table.

So I'd say yes you have found a bug. Please report it to Actian.

Martin Bowes

-----Original Message-----
From: alois.treindl@gmail.com [mailto:alois.treindl@gmail.com]
Sent: 09 February 2012 12:42
To: info-ingres@kettleriverconsulting.com
Subject: [Info-Ingres] like predicate does not match corrected on key column

I am using Ingres 10.1 on Linux (64bit).
Version: Ingres Linux Version II 10.1.0 (a64.lnx/120)NPTL

I noticed today a strange behaviour which I had not observed in the 23 years
of using Ingres before.

table snm is an isam table with key ssnm, varchar(40) and some 600'000 rows.

select * from snm where ssnm like 'a%'
does not match with 'a', only with all lines beginning with 'a..'.

This happens only when the column on which the like predicate operates is
a key column or an index exists on it.

It does not happen when I add a clause WITHOUT CASE, but in embedded SQL
this clause is not supported.

The problem appeasr both in interactive queries and in C embedded SQL.

I had to rewrite my queries as
select * from snm where (ssnm like 'a%' or ssnm = 'a')

It seems to me that this is a bug in Ingres.
_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://ext-cando.kettleriverconsulti...fo/info-ingres

On Feb 9, 2012, at 7:42 AM, alois.treindl@gmail.com wrote:

> select * from snm where ssnm like 'a%'
> does not match with 'a', only with all lines beginning with 'a..'.

That is indisputably a bug.

Fortunately it doesn't seem to be happening with the current
head-revision of main, which will become the real and official 10.1
Fairly Soon Now.

Have you built your own Ingres from source, or are you using one of
the prebuilt distributions? If you can't wait for the official 10.1, you
might consider getting the latest from svn (rather than the build 120
source from esd.actian.com), and building that. I am not sure how
up-to-date the community svn repository is, but it might be newer
than the 120 source tarball and might have the fix.

Karl