* Wrong Results Issues - Recommended Actions (Doc ID 150895.1)

Last updated on AUGUST 29, 2023

Applies to:

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Standard Edition - Version 7.0.16.0 and later
Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Introduction

This article provides an action plan for gathering diagnostics for Wrong results issues and suggests ideas for quickly recovering the situation.

Avoid known issues by running on the latest revision. If using the latest revision, check that there are no additional recommended patches. Refer to:

<Document 1904820.1> Things to Consider Before Upgrading to Avoid Poor Performance, Wrong Results or SQL Plan Management (SPM) Issues (11.2.0.X and above)

Background

There are a number of different factors that can cause an incorrect result to occur at both parse and execution time. Wrong result issues can have a crippling effect on an application. Other than the obvious effect of incorrect data being returned, they also reduce user confidence in 'correct' results.

Upon encountering a wrong result issue, the most important step is the production of a know fix or a viable workaround to the problem. The following steps can be used to facilitate analysis of the situation.

PLEASE NOTE:

Errors are not necessarily "Wrong Results"

Typically, Oracle Documentation and Articles do not regard errors generated by SQL as a 'Wrong' Result; rather a "Wrong Result" is generally defined as a statement which should return a particular set of rows but does not. Receiving an error from a SQL statement may not be the desirable result, but it is not necessarily "Wrong". The error indicates that the statement has not completed successfully as opposed to it completing and not returning the desired rows.

When searching for issues that raise errors, if the keywords "Wrong Result" are included incorrectly, then this may eliminate articles that you are interested in or add ones that you are not.
The following articles address errors that are commonly raised as being a "Wrong Result":

<Document 232243.1> ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates

<Document 19133.1> OERR: ORA 1795 maximum number of expressions in a list is 1000
Internal Only - Document 406321.1 How To Workaround ORA-01795: maximum number of expressions in a list is 1000

<Document 1600974.1> ORA-01791: not a SELECTed expression after upgrade to 11.2.0.4
<Document 13768663.8> Bug 13768663 - ORA-1791 not reported in 11.2 when expected

Unexpected order of data is not necessarily a "Wrong Result"

The order in which data is presented can result in differing outputs that may well be expected even though it looks 'incorrect'. 'Wrong Results' with the order of data or with certain resultant errors may not be code defects or errors at all. Remember that Oracle does not guarantee the order that data is returned without an order by clause.
Please refer to the following articles:

<Document 456707.1> Order Of Data Retrieval Differs after upgrading 9i To 10g
<Document 344135.1> Ordering of Result Data

<Document 345048.1> 'Group By' Does Not Guarantee a Sort Without Order By Clause In 10g and Above
<Document 341838.1> From10Gr2, HASH UNIQUE Operation Returns Results in UNSORTED ORDER by Default

Results of comparisons with NULL values may be unexpected

If a column contains NULL, and it is compared to a value in a where clause, then it will return NULL and will not match the row. This is not a wrong result. You can see the results of comparisons against NULLs in the following document:

<Document 29019.1> QREF: NULL Comparisons