The value for varchar colx in two different rows are
'AAA_BBB_12345' and 'AAA BBB 12345'.

Ingres 9.2.1 (a64.lnx/103)NPTL thinks these are the same, but Ingres 10
doesn't.

Ingres 9.2 wont allow this table to be created as btree unique on colx.

Is this a bug or am I missing something?

Thanks

Dennis

Ingres 9.2 is evaluating the underscore "_" as the wildcard character.

Could you please perform this query and let us know the result?
SELECT DBMSINFO('unicode_level')\p\g


--
bilgihan

Is your where clause *WHERE colx = 'AAA_BBB_12345'* or is it *WHERE colx
LIKE 'AAA_BBB_12345'*?

LIKE treats the underscore as a wildcard for any single character unless
you escape it (*WHERE colx LIKE 'AAA\_BBB\_12345' ESCAPE '\'*) .


--
rhann

HI Dennis

On ordinary databases: createdb bowtest

I get the same answer on both 9.2.0 and 10.0.0 on a64/lnx.

In both cases the rows sort in order:
AAA BBB 12345
AAA_BBB_12345

You possibly used a different collation sequence on the different installations?

Martin Bowes

-----Original Message-----
From: Dennis [mailto:droesler (AT) comcast (DOT) net]
Sent: 15 January 2012 23:52
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Ingres 9.2 - Why does Ingres think these are the same?

The value for varchar colx in two different rows are
'AAA_BBB_12345' and 'AAA BBB 12345'.

Ingres 9.2.1 (a64.lnx/103)NPTL thinks these are the same, but Ingres 10
doesn't.

Ingres 9.2 wont allow this table to be created as btree unique on colx.

Is this a bug or am I missing something?

Thanks

Dennis
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Hi All,

I've mucked around with this further....
I can replicate the error(?) if the database is unicode enabled: createdb -i bowtest

And only then if the sort column is nvarchar not varchar.

create table x(a nvarchar(100) not null not default);

insert into x values('AAA BBB 12345');
insert into x values('AAA_BBB_12345');

modify x to btree unique on a;

select * from x;

The modify will generate the error:

E_US1591 MODIFY: table could not be modified because rows contain duplicate keys.

I get this on both 9.2 and on 10.0.

Marty
-----Original Message-----
From: Martin Bowes
Sent: 16 January 2012 09:24
To: info-ingres (AT) kettleriverconsulting (DOT) com
Cc: 'Dennis'
Subject: RE: [Info-Ingres] Ingres 9.2 - Why does Ingres think these are the same?

HI Dennis

On ordinary databases: createdb bowtest

I get the same answer on both 9.2.0 and 10.0.0 on a64/lnx.

In both cases the rows sort in order:
AAA BBB 12345
AAA_BBB_12345

You possibly used a different collation sequence on the different installations?

Martin Bowes

-----Original Message-----
From: Dennis [mailto:droesler (AT) comcast (DOT) net]
Sent: 15 January 2012 23:52
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Ingres 9.2 - Why does Ingres think these are the same?

The value for varchar colx in two different rows are
'AAA_BBB_12345' and 'AAA BBB 12345'.

Ingres 9.2.1 (a64.lnx/103)NPTL thinks these are the same, but Ingres 10
doesn't.

Ingres 9.2 wont allow this table to be created as btree unique on colx.

Is this a bug or am I missing something?

Thanks

Dennis
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

No it isn't - pattern mating is only performed with the pattern
operators LIKE and SIMILAR TO.

This behaviour will likely be due to a UTF-8 installation and hence
Unicode collation enabled on the column in question.

Regards,
Ian


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
eric.mischke (AT) w-link (DOT) net
Sent: 16 January 2012 06:08
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Ingres 9.2 - Why does Ingres think these are
the same?

Ingres 9.2 is evaluating the underscore "_" as the wildcard character.





_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

It depends on the unicode collation sequence in use. The default
unicode collation sequence used by Ingres treats underscore and space
as the same.
You can use the udefault5 collation sequence instead (createdb -
iudefault5 dbname), which distinguishes between these characters (and
some more).
Not sure at the moment whether this was available in the first 9.2 GA
release, but at least available by patch and in 10.0.

By the way, when using a UTF8 installation you will see the problem
with varchar too.

Kristoff

On Jan 16, 11:03*am, Martin Bowes <martin.bo... (AT) ctsu (DOT) ox.ac.uk> wrote:

Thanks, Kristoff! I wasn't aware of this

-- Emma

PS Getting used to my iPhone. Excuse typos and nonsensical auto corrects!


On Jan 16, 2012, at 13:38, "Kristoff" <kristoff.picard (AT) ingres (DOT) com> wrote:

On 1/16/2012 12:36 AM, Ingres Forums wrote:
This returns 1

Dennis