SQL Query returning different results on same Oracle database on 2 different systems

Question

I have a 2 identical databases running on Oracle 12c. They are sitting on 2 different servers.

I'm executing the following query using SQL Developer on both databases.

select *
from sla
where objectname = 'INCIDENT' and
      status in 'ACTIVE'  and
      (exists (select 1
               from classancestor
               where ancestor=sla.classstructureid and classancestor.classstructureid= '')  or
       classstructureid is null or
       classstructureid =  ''
      ) ;

I get 260 results on one of the databases and 0 results on the other. This query is generated by an ERP we are using so I cannot modify the SQL itself.

One thing I noticed is that if I remove the parentheses surrounding the exists operator, the behaviour changes.

Can someone help? Is there a precedence when using OR? Is there an Oracle patch that has an impact on how SQL statements are processed?

Thanks

the behaviour changes ... how does the behavior change? My guess is that the underlying tables are not the same. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 29, 2017 at 13:33
Yes sorry, I get the same result on both databases. Both databases are identical, each table present in the SQL statement was compared thoroughly — Pete
– Pete, Commented Dec 29, 2017 at 13:35
Next question: Are both databases running the same version of Oracle? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 29, 2017 at 13:36
YES Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production "CORE 12.1.0.2.0 Production" TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production — Pete
– Pete, Commented Dec 29, 2017 at 13:39
I can't explain this, but could you update your question with the logic behind the WHERE clause? I am assuming there is some funky precedence stuff going on here, but if you write your query right, that shouldn't matter on any version or Oracle. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 29, 2017 at 13:44

Kaushik Nayak · Accepted Answer · 2017-12-30 12:51:21Z

2

Ok, for now I simply reverted to Oracle 11g optimizer and solved my issue.

ALTER SYSTEM SET optimizer_features_enable='11.2.0.3';

Not my favorite solution, but it's a workaround for now.

Thanks for your help guys

edited Dec 30, 2017 at 12:51

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

answered Dec 29, 2017 at 16:20

Pete

513 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query returning different results on same Oracle database on 2 different systems

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related