Solved SQLBase 12.2.2 and 12.2.1: Bug when using SELECT IN query with combination of CHAR and NCHAR datatypes

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

SQLBase 12.2.2 and 12.2.1: Bug when using SELECT IN query with combination of CHAR and NCHAR datatypes

Post by Uwe van der Horst » 12 Apr 2021, 07:46

We have found a bug when using SELECT IN query with combination of CHAR and NCHAR datatypes. No values being returned.

repro:

create table text1(s_char char(254), s_nchar nchar(254));
create table text2(s_char char(254), s_nchar nchar(254));
insert into text1 (s_char, s_nchar) values ('a', 'a');
insert into text1 (s_char, s_nchar) values ('a', 'a');
insert into text2 (s_char, s_nchar) values ('a', 'a');
commit;

select * from text1 where s_nchar in (select s_char from text2); -> gives "0 rows selected" (should be "2 rows selected")
select * from text1 where exists (select 1 from text2 where text2.s_nchar=text1.s_char); -> gives "2 rows selected" (ok)

Now we do the same with only one row in the table, this one works.

drop table text1;
drop table text2;
create table text1(s_char char(254), s_nchar nchar(254));
create table text2(s_char char(254), s_nchar nchar(254));
insert into text1 (s_char, s_nchar) values ('a', 'a');
insert into text2 (s_char, s_nchar) values ('a', 'a');
commit;

select * from text1 where s_nchar in (select s_char from text2); -> gives "1 rows selected" (ok)
select * from text1 where exists (select 1 from text2 where text2.s_nchar=text1.s_char); -> gives "1 rows selected" (ok)

After we reported the error, we received a ONEOFF within a day. That is outstanding support. The support contract is really worth every penny.
Best regards,
Uwe van der Horst
Advo-web GmbH

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

Re: SQLBase 12.2.2 and 12.2.1: Bug when using SELECT IN query with combination of CHAR and NCHAR datatypes

Post by Steve Leighton » 12 Apr 2021, 09:39

Uwe That's great to know and be aware of. Thanks for reporting !
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests