Oracle Evaluate condition incorrect

2
votes

I've got a trouble querying on of two Oracle databases.

The query is:

SELECT trunc(null)
FROM dual 
WHERE (null is null 
       or SYSDATE >= TRUNC(null))

(The NULL is actually a param, passed to a query, which may be null, so I cut the query short).

On the production DB it works OK and the response is NULL.

On the dev DB I catch an error, that TRUNC can't be applied to NUMBER and expects DATE (ORA-00932).

Obviously the production DB skips everything in the condition after OR, and the developer one executes the part after OR.

I do know a solution to fix the problem by adding CAST(MY_PARAM as date) to every query, but it doesn't really suit me - there's to much code to change.

The question is: is there some setting in the DB I've missed, that prevents condition scan after it's already true or does it dependent on DB version?

My production db is:

  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  • PL/SQL Release 11.2.0.4.0 - Production
  • CORE 11.2.0.4.0 Production
  • TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
  • NLSRTL Version 11.2.0.4.0 - Production

My dev db is:

  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  • PL/SQL Release 11.2.0.1.0 - Production
  • CORE 11.2.0.1.0 Production
  • TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
  • NLSRTL Version 11.2.0.1.0 - Production
1
sqloracleoracle11g
You should change your queries. Having type mismatches in the query that are short-circuited by boolean logic seems quite dangerous. Even more so for a production system. – Gordon Linoff
"NULL is actually a param". So the query is something like WHERE (:dt is null or SYSDATE >= TRUNC(:dt))? Then obviously you are passing a numeric type to that bind variable. Pass a date type and you should be fine. – Thorsten Kettner

1 Answers

0
votes
SELECT CASE (PARAM IS NULL) THEN NULL ELSE TRUNC(PARAM) END
FROM TABLE