Kbase P150219: With a Latvian query some indexes do not return any records.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/12/2009 |
|
Status: Unverified
SYMPTOM(s):
Latvian query results are not correct
Some indexes return the correct results and others do not return any records at all.
FACT(s) (Environment):
The query being used looks like:
for each <table-name> no-lock where
field1 = <someValue> AND
field2 = <someValue> AND
fieldX <= '1X1Y' AND
field3 >= <someValue> AND
field4 <= <someValue>
use-index <index-name>:
disp <table-name>.
end.
There are many records in the database. As an example the field (fieldX) in question contains many values some of which are:
"1X1U"
"1X1X"
"1X1Y"
The database code page is UTF-8 and database collation is basic.
The client code page is 1257 and the client collation is Latvian.
Finnish users are connecting to a UTF-8 database with Latvian settings in order to run queries on Latvian data.
All Supported Operating Systems
OpenEdge 10.0x
CAUSE:
This is expected behavior. The cause of this problem is a combination of where the query is being evaluated, the sort sequence being used and the fact that both server and client sides of the client connection are using different collation sequences. The important point to realize here is that with basic ASCII sorting 'Y' sorts after both 'U' and 'X', but with Latvian and Lithuanian sorting, 'Y' sorts before 'U' and 'X' (actually after 'I' and before 'J').
In the case where an index returns the correct records, in this particular instance the field 'fieldX' is part of a compound index and due to index bracketing the query is being partially resolved on the server. The field 'fieldX' participates in this bracket and is evaluated in the database (server side) using basic collation. The results of the partially resolved query are then passed to the client for the remainder of the selection criteria to be applied. This further processing is performed by the client using its local collation (in this case Latvian). Since the evaluation of fieldX has taken place on the server using basic collation, 'Y' has been evaluated as greater than 'U' and 'X' so many records (including fieldX values with 'U' and 'X') have been returned to the client for further processing.
In the case where the index does not return any records, the index used again uses a bracket to partially resolve the query on the server. But this time the field 'fieldX' does not participate in this bracket. The resulting records are then passed to the client for further processing and at this point the fieldX component is evaluated, but this time using Latvian collation. Since with Latvian collation 'Y' sorts before 'U' and 'X' any records after 'Y' ('J' onwards) are discarded. The subsequent query returns very few records (and in this specific case, none).
FIX:
Make sure that the clients, database and database servers are using the correct collation for their respective regions. In this particular case the query results that are perceived to be incorrect are actually correct if Latvian users observed them.