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

Report SqlBase bugs and possible workarounds.
Uwe van der Horst
Site Admin
Site Admin
Germany
Posts: 525
Joined: 05 Mar 2017, 14:21
Location: Wetter (Ruhr), Germany

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

Post by Uwe van der Horst » 05 May 2021, 08:30

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.
Best regards,
Uwe van der Horst
Advo-web GmbH

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 288
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

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

Post by Steve Leighton » 07 May 2021, 03:01

.
Grrrr. Good to know. Thanks for posting.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Uwe van der Horst
Site Admin
Site Admin
Germany
Posts: 525
Joined: 05 Mar 2017, 14:21
Location: Wetter (Ruhr), Germany

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

Post by Uwe van der Horst » 08 May 2021, 06:30

One-Off Patch 12.2.2.12887 has been delivered to resolve the issue (no bug-number).
Best regards,
Uwe van der Horst
Advo-web GmbH

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests