We migrated several databases across from ASE 15.0.2 to a new sybase server over the weekend. All went well until this morning when we found out that the like operator wasn’t behaving as expected. Instead of the percent/mod operator representing this definition: “Matches any string of zero or more characters.”; it became “any string of one or more characters”.
For example:
select firstname from person where firstname like 'paul%'
This query would, in versions prior to ASE 15.5, return a list similar to the following:
paul
paulie
pauline
In ASE 15.5 though, you’ll get the following:
paulie
pauline
This threw off our applications completely. Sybase technical support did manage to confirm that it was a known bug, which is fixed in 15.5 ESD#1 17791. In the meantime, he suggested we call the following command on the database to work around it until we find the time to do the update:
dbcc traceon(7739)
There was only one link online which really let us know that it could be Sybase’s fault rather than a simple parameter, being the sybase.public.ase.general group on google.
This was all run on a Sun Solaris SPARC server:
select @@version
Adaptive Server Enterprise/15.5/EBF 17336 SMP/P/Sun_svr4/OS 5.8/ase155/2391/64-bit/FBO/Tue Nov 10 01:27:55 2009
This is a quote from an email I received from a Sybase Technical Officer ( this is not official in any way):
CR#616224-WRONGRES % wildcard is not matching 0 characters (i.e. end of string)
Details:
Like pattern matching may return fewer rows when the following conditions are met:
1. server’s default sortorder is non-binary single-byte sortorder
2. like pattern is a constant literal
3. like pattern string length is the same as column data length
4. column is either varchar or char
Workaround:
1. remove ‘lig =’ lines in the .srt file if ligatures are not used, this will then require
a sort order change and accompanying steps.
or
2. enable ‘statement cache’ and enable literal parameterization of literals in the LIKE
clause with traceflag 7739. Certain statements such as ‘select into’ will not benefit
from this workaround.
This bug was fixed on 15.5 ESD#1, 17791.