Kbase P99356: UTF-8 database not returning records when using a query with a multiple part where clause
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/30/2010 |
|
Status: Verified
SYMPTOM(s):
UTF-8 database not returning records when using a query with a multiple part where clause
UTF-8 database using ICU-UCA collation
Client connecting with -cpinternal ISO8859-1 -cpcoll BASIC
When using only single where clause, records will be returned.
where clause uses a value that represents the highest character in the clients collation sequence.
highest character is not a printable character
FACT(s) (Environment):
OpenEdge 10.0B
CAUSE:
In order to understand this problem better, first you need to be aware of the following:
1. In Progress, the operations of comparison operators like "<=" is collation oriented.
Therefore, if the execution of the query is performed on the client side the comparison predicate is executed based on the client's collation. However, if the execution of the query is performed on the server side the comparison predicate is executed based on the server's(db's) collation. A query that will scan an index with or without an index hint, or a query specified as query by server is most likely executed on the server side not on the client side.
2. The result of a comparison predicate in a query is only unique to one collation based on where the query is executed. The same query will very likely give different result if it's run against different collations. It will be improper to make the assumption of getting the same results back when running the same query against different collations of different codepages.
FIX:
The solution is to understood where a query will be executed(client or server) and how to design the query's comparison predicate based on the execution side's collation.
One solution is to use binary comparison through the COMPARE function by skipping the collation comparison. For example:
for each <file> where <field> = <variable> and COMPARE(<field>," <=", chr(160), "RAW")=TRUE.