2113377 - Comparison results in a utf8 server with sort order 54 return different results - SAP ASE

• Getting wrong results when the Adaptive Server Enterprise (ASE) default sort order is 54 and the WHERE clause compares accent characters to others.
   
• If you create a table as:

create table #t1 (a varchar(20))
insert #t1 values("Fred")
insert #t1 values("fred")
insert #t1 values("Fred")
insert #t1 values("fréd")
insert #t1 values("frëd")
insert #t1 values("fréd")

and run the following queries:

select * from #t1
select * from #t1 order by 1
select * from #t1 where a="fred"

all three resultsets should contain the same six rows because Sort order 54 is supposed to be case and accent insensitive, however what you actually get is:

a
--------------------
Fred
fred
Fred
fréd
frëd
fréd
(6 rows affected)

a
--------------------
Fred
Fred
fred
fréd
fréd
frëd
(6 rows affected)

a
--------------------
Fred
fred
Fred
(3 rows affected)

• Interestingly though, if you do the comparisons in an IF statement they work fine. For example, running this script:

if "Fred" = "fred"
print "The same"
else
print "Different"
go
if "fréd" = "frëd"
print "The same"
else
print "Different"
go
if "fred" = "frëd"
print "The same"
else
print "Different"
go

gives:

The same
The same
The same

• SAP Adaptive Server Enterprise (ASE) 15.7
• SAP Adaptive Server Enterprise (ASE) 16.0

KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem