Where did the filter come from?

There has been a recent thread on ORACLE-L where the poster asked why there is an extra filter predicate which appears when functions are being used in the where clause.

We can observe the behavior using the following test case:

SQL> create table t
  2  (
  3  	n number,
  4  	dt date
  5  );
Table created
SQL> insert into t values (1, sysdate);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from t where dt >= add_months(trunc(sysdate, 'mm'), -1) and dt < trunc(sysdate, 'mm');
Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    22 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    22 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ADD_MONTHS(TRUNC([email protected]!,'fmmm'),(-1))<TRUNC([email protected]!,'
              fmmm'))
   2 - filter("DT"<TRUNC([email protected]!,'fmmm') AND
              "DT">=ADD_MONTHS(TRUNC([email protected]!,'fmmm'),(-1)))

Why there is an extra filter predicate against step# 1 in the plan?

The following predicate:

dt >= add_months(trunc(sysdate, 'mm'), -1) AND dt < trunc(sysdate, 'mm')

can be represented in the simplified form as:

X >= Y AND X < Z

The above predicate is true only when the following predicate is true:

Y < Z

In other words, If Y is greater than Z then the original predicate will always evaluate to false no matter what the value of X is. If we substitue the above with the actual values which we have:

X: DT
Y: add_months(trunc(sysdate, 'mm'), -1)
Z: trunc(sysdate, 'mm')
Y < Z: add_months(trunc(sysdate, 'mm'), -1) < trunc(sysdate, 'mm')

In other words, if the filter condition in step# 1 evaluates to false then there is no need to execute the query at all because no rows will come out regardless of what data we have in the dt column and Oracle can use it to short-circuit the execution. The reason that the optimizer have to execute that filter explicitly is because sysdate will look to it as any other non-deterministic function, i.e. you don’t know whether Y < Z or not until you substitute Y and Z with the actual values which will happen every time you execute the query.