My quick look at other products' documentation has not given me any specifics on their behavior on this issue. I will concentrate my efforts on coming up with a patch which is based on SQL standards. But please feel free to have this discussion going.
Mamta On 10/10/07, Mamta Satoor <[EMAIL PROTECTED]> wrote: > > Dan posted his findings on MySQL behavior on DERBY-2967. The MySQL > Reference Manaul (MySQL reference: > http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html > ), towards the beginning of the page has following blurb "Per the SQL > standard, LIKE performs matching on a per-character basis, thus it can > produce results different from the = comparison operator: " > > I do not know what is Oracle's and SQL Server's behavior here. I will try > to look at their documentation. > > Mamta > > On 10/10/07, Bernt M. Johnsen <[EMAIL PROTECTED]> wrote: > > > > >>>>>>>>>>>> Mamta Satoor wrote (2007-10-09 09:40:53): > > > [...] > > > Unicode has a concept of Contraction where a user might perceive more > > than > > > one character as a single character in a given language. One eg of > > this > > > would be 'AA' in Norwegian locale. Although, this Contraction is made > > of > > > 2 Unicode characters, 'A' and 'A', a Norwegian user perceives them as > > > a single character. > > > > No. Noone in Norway would perceive 'aa' as a single character. > > > > > In addition, in Norwegian, the collation elements for 'AA' are > > > identical to collation elements for 'Å'. So, the question is what > > > should SQL operation 'AA' like 'Å' return? Also, is that behavior > > > same as SQL operation 'AA' = 'Å' ? > > > > 'AA' and 'Å' will in Norwegian have the same collation weight, but the > > way the SQL standard is phrased, that does not apply to LIKE which > > does an character by character match of the strings. I admit that > > > > 'aa'= 'å' > > > > and > > > > 'aa' LIKE 'å' > > > > giving different results may seem a bit odd but I am not able to > > interpret Sections 8.2 and 8.5 differently (On the other hand, that's > > not the only place the SQL spec is contraintuitive for the average > > programmer). > > > > On the other hand, searching and matching are handled in Unicode T10 > > sections 1.5 and 8 (SELECT is explicitely mentioned in 1.5). It seems > > that Unicode is in agreement with how I interpret the semantics for > > comparison operators but not in agreement with how I interpret the > > sematics for the LIKE predicate. > > > > Does anyone know what MySQL, Oracle, SQL Server etc does with this? If > > the other major databases are in agreement, we could follow their > > interpretation if we find it reasonable. > > -- > > Bernt Marius Johnsen, Database Technology Group, > > Staff Engineer, Derby/Java DB > > Sun Microsystems, Trondheim, Norway > > > > >
