Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Query using BEGINS operator fails to find accented characters

BEGINS operator does not support collation and produces error 565 when special characters are used

**FIND FIRST/LAST failed for table customer.(565)

BEGINS operator does not find records with special characters

FACT(s) (Environment):

Character data contains special characters such as diacritical mark
BEGINS operator does not use collation table
Collation tables do not require indexes
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems

CAUSE:

Bug# OE00160509

CAUSE:

BEGINS and MATCHES operators do not always use collation tables. COMPARE with MATCHES operator also ignores collation. As a result, a query may not always return the expected result set.
BEGINS has two behaviors, one for indexes (which does use the collation) and one for non-index comparisons (which does not use the collation). This can and does cause issues in applications that process character data that contains special characters such as diacritical marks. A basic use case that fails:
Different keyboard layouts, some accents and characters are not readily available (an American keyboard does not have the "c with cedilla" character on it, a French one does). So an alternate spelling is used on those keyboards. And because the collation is not supported in these cases, any lookup operation that should take into account these alternate spellings fails to do so and does not return the desired results.

FIX:

Option #1
Ensure the query uses an index on the specific field

Option #2
Replace the BEGINS clause with a <> clause, i.e.

WHERE Name >= "oo" AND Name <= "oo" + <HIGH_CHARACTER>.

To find the HIGH_CHARACTER (can be different for each collation) use code like this:

DEFINE VARIABLE iHighest AS INTEGER NO-UNDO INITIAL 1.
DEFINE VARIABLE i AS INTEGER NO-UNDO.

DO i = 1 TO 255:
IF CHR(iHighest) < CHR(i) THEN iHighest = i.
END.

Option #3
Use another field for lookup that does not need to populate extended characters