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

[Uwe van der Horst]

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.

[Steve Leighton]

Uwe That's great to know and be aware of. Thanks for reporting !