Solution : https://service.sap.com/sap/support/notes/658322 (SAP Service marketplace login required)
Summary :
This SAP Note identifies various discrepancies in Oracle database query results across versions and specific conditions. The common issues encompass incorrect result sets, such as excess entries, insufficient entries, or duplicated records attributed to diverse reasons like NULL values, use of ORDER BY DESC, improper joins, and ineffective hints among others. Numerous specific cases from Oracle versions 8.0.4 to 10.2.0.4 are addressed, detailing potential misbehaviors in scenarios like parallel queries, star transformations, and handling of FLOAT numbers. Solutions mainly involve patches, upgrades, and parameter adjustments. Proper referencing to corresponding SAP Notes for specific fixes is encouraged to resolve these discrepancies.
Key words :
floating point number differ slightly, oracle database query returns, incorrect 'number -> character' conversion 10, prevent incorrect resulting sets, database character set, index skip scan 19, predicate pushing solution 1, sql statement displays, current patch set, incorrect resulting set
Related Notes :
| 1244076 | kkp* merge fix for Oracle 10.2.0.4 |
| 1144178 | Invalid float values |
| 1126517 | ORA-1445 or incorrect values with 10.2 |
| 1119194 | 10.2.0.2: Incorrect result set with Bloom filters |
| 1118068 | Rounding effects for floating point numbers & Oracle DBs |
| 1109753 | Table prefetching produces incorrect result sets |
| 1107700 | 10.2.0.2: Execution of SQL statements in wrong schema |
| 1088403 | Table buffer and NULL values on database |
| 1040300 | Wrong values with Star Transformation Hint 9.2.0.8 /10.2.0.2 |
| 1037600 | Oracle 9.2.0.8: Data is not selected using index skip scan |
| 1003198 | J2EE: Incorrect data with Oracle10g JDBC lower than 10.2.0.4 |
| 992261 | Optimizer merge fix for Oracle 9.2.0.8 |
| 985118 | Wrong Values with parallel execution |
| 977319 | Incorrect values in Oracle 10.2 (II) |
| 964858 | Incorrect vals w/ STAR_TRANSFORMATION_ENABLED in Oracle 10.2 |
| 896903 | Optimizer merge fix for Oracle 9.2.0.7 |
| 834100 | Merge fix for Oracle 9.2.0.6 |
| 825653 | Oracle: Common misconceptions |
| 822638 | Incorrect values for query with OR |
| 815653 | Various bug fixes: ST-SER320 SP06 |
| 814514 | Incorrect values for a query with a "common sub-expression" |
| 755629 | KKO merge fix for Oracle 9.2.0.5 |
| 727310 | Wrong Results with ODS or Non-Star_Transformation Queries |
| 717496 | Wrong results with STAR_TRANSFORMATION_ENABLED=TRUE |
| 708762 | Oracle9: Error converting NUMBER to CHAR |
| 699521 | Oracle 9: Incorrect results with EXEC SQL |
| 695080 | Merge fix for Oracle 9.2.0.4 |
| 656704 | Duplicate data records during AND EQUAL index access |
| 617679 | Error due to incomplete column definitions |
| 610668 | Document flow: Material document from incorrect fiscal year |
| 610526 | Incorrect results with ANSI OUTER JOIN |
| 606359 | FAQ: Oracle National Language Support (NLS) |
| 602843 | Environment settings for R/3/Oracle on UNIX |
| 595757 | Composite note Oracle 9.2.0.2 & incor. data (star transf.) |
| 594136 | Compos. SAP note Oracle 8.1.7.4 & wrong data (star transf.) |
| 556232 | Environment settings for R/3/Oracle on Windows |
| 459314 | SE16 and pooled tables |
| 357016 | Incorrect results with PARALLEL hint |
| 355774 | Incorrect results from the database server |
| 335877 | Incorrect res. qty w/ select with OR in WHERE cond. |
| 140237 | Incorrect results for SELECT .. ORDER BY DESC |