@SCAN gives wrong results with NCHAR and LONG NVARCHAR columns and collations

[Uwe van der Horst]

There are different bugs with @SCAN when using a non-standard collation (no problem with the default collation "SET COLLETION BINARY").

@SCAN should work case insensitive independent of the collation but does not.

SQLBase Langugage Reference, Product Version 12:

The @SCAN function can perform a case-insensitive match on columns of type CHAR, VARCHAR, NCHAR, NVARCHAR, LONG VARCHAR and LONG NVARCHAR.

repro: two columns, one with data type CHAR and one with NACHAR. No problems with CHAR.

set collation German_CS_AI;
create table ntest(text char(64),text_nchar nchar(64));
insert into ntest(text,text_nchar) values('abc','abc');
insert into ntest(text,text_nchar) values('ABC','ABC');
commit;

no problems with CHAR:
select @scan(text,'a') from ntest;
select @scan(text,'b') from ntest;
select @scan(text,'c') from ntest;

different bugs with NCHAR:
select @scan(text_nchar,'a') from ntest; -> gives 0/-1, should give 0/0
select @scan(text_nchar,'b') from ntest; -> gives 0/-1, should give 1/1
select @scan(text_nchar,'c') from ntest; -> gives 1/-1, should give 2/2

There are bugs with LONG VARCHAR too.

I entered a ticket for this.

[Steve Leighton]

.
Grrrr. Good to know. Thanks for posting.

[Uwe van der Horst]

One-Off Patch 12.2.2.12887 has been delivered to resolve the issue (no bug-number).