The database version we have 12.1.0.2. The problem is because we have hit the bug 25392535 in 12.1.0.2 version. Query giving wrong result because of parallelism. In our database default parallelism is enable 2.
SQL> show parameter parallel_threads_per_cpu;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
The following way you can test your query giving incorrect or correct result.
Option 1: Change Optimizer feature for session
Execute the query and the query gives wrong result (I have not share here the complex query executed in my database)
4898 rows selected.
Incorrect Result
alter Session Set optimizer_features_enable = '11.2.0.4';
Re-execute the query again.
5428 rows selected.
Correct Result
Option 2: Disable parallelism for session
[Optional] If you already change the optimizer to '11.2.0.4' change it back
alter Session Set optimizer_features_enable = '12.1.0.2';
Execute the query
4898 rows selected.
Incorrect Result
ALTER session disable parallel query;
Re-execute your query
5428 rows selected.
Correct Result.
Solution:
Apply patch 25392535 which is available in MOS see https://updates.oracle.com/download/25392535.html
Please check Oracle Doc ID 2290845.1 and Doc ID 2217465.1 for more information.