Kbase P185043: Query returns less data than expected when executed against DataServer for Oracle
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/12/2011 |
|
Status: Unverified
SYMPTOM(s):
Query returns less data than expected when executed against DataServer for Oracle
Query returns <98> records instead of the expected <100> when executed against DataServer for Oracle
Query uses a complex WHERE clause
Example:
FOR EACH <table> NO-LOCK WHERE <field#1> = "<value>" AND <field#2> = "<value>" AND <field#3> = "<value>"
AND (
<field#4> = "<value>"
OR <field#4> = "<value>"
OR <field#4> = "<value>"
OR (<field#4> = "<value>" AND <field#5> = <value>)
)
This query is processed entirely by Oracle database engine
If part of WHERE clause is moved to an IF THEN statement then the expected number of records is returned
Example:
FOR EACH <table> NO-LOCK WHERE <field#1> = "<value>" AND <field#2> = "<value>" AND <field#3> = "<value>":
IF <field#4> = "<value>"
OR <field#4> = "<value>"
OR <field#4> = "<value>"
OR (<field#4> = "<value>" AND <field#5> = <value>) THEN ...
This query returns more records which are then filtered further by ABL
SQL query generated by the DataServer returns same result (less records than expected) when executed via Oracle SQLPlus
Modifying Oracle NLS_SORT (change from BINARY to BINARY_AI) and NLS_COMP (change from BINARY to LINGUISTIC) settings results in expected number of records being returned in both Progress client and SQLPlus
Modifying Oracle NLS_SORT and NLS_COMP settings does not cause the SQL query generated by the DataServer to change
FACT(s) (Environment):
Oracle database does not use shadow columns (U##<field>)
Problem does not occur when Oracle database is created with shadow columns and these columns are referenced in the SQL query generated by the DataServer
SQL queries use the UPPER function when shadow columns are not available
Executing either query against a Progress/OpenEdge database returns the expected number of records
OpenEdge 10.x
HP-UX
Oracle DataServer
CAUSE:
The behaviour is the result of a binary comparison by the Oracle database engine. In a binary sort, all records have their own unique weight. With linguistic sorting, it is possible for more than one binary value to have the same weight in comparisons and sorts. This suggest that the linguistic sort is including the expected, additional records that have values sharing the same sort weight as other values that are already included in the results.
FIX:
Modify Oracle settings to use linguistic searching:
Set NLS_COMP to LINGUISTIC
Set NLS_SORT to BINARY_AI