What Happens when 1=2 is Included in a WHERE Clause?

The following appeared in a Usenet post:

Surprisingly, the optimizer sometimes seems to do a bad job about such constant clauses. As in “where 1 = 2” resulting in a full table scan…

The above statement is actually correct (OK, partially).  However, it might be a good idea to check the DBMS_XPLAN output and/or the output of a 10046 trace and/or the output of SET AUTOTRACE TRACEONLY STATISTICS.  For example, here is a test on Oracle 11.1.0.7 with a 100,000,000 row table with a primary key column in a freshly bounced database:

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT 
  * 
FROM 
  T1 
WHERE 
  1=2;

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 3332582666 
 
--------------------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT   |      |     1 |    53 |       0 (0)|          | 
|*  1 |  FILTER            |      |       |       |            |          | 
|   2 |   TABLE ACCESS FULL| T1   |   100M|  5054M|   221K  (1)| 00:44:14 | 
--------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter(NULL IS NOT NULL)

The plan indicates a full table scan, and the filter predicate indicates?
Continuing:

SET AUTOTRACE TRACEONLY STATISTICS 
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'My_Trace'; 
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; 
 
SELECT 
  * 
FROM 
  T1 
WHERE 
  1=2; 
 
Statistics 
---------------------------------------------------------- 
   1  recursive calls 
   0  db block gets 
   0  consistent gets 
   0  physical reads 
   0  redo size 
 301  bytes sent via SQL*Net to client 
 349  bytes received via SQL*Net from client 
   1  SQL*Net roundtrips to/from client 
   0  sorts (memory) 
   0  sorts (disk) 
   0  rows processed 
 
 
SELECT SYSDATE FROM DUAL;

A full table scan on a 100,000,000 row table which performed 0 consistent gets, interesting….

Now, a check of the 10046 extended SQL trace file:

===================== 
PARSING IN CURSOR #7 len=32 dep=0 uid=56 oct=3 lid=56 tim=327318181 hv=2373026659 ad='2775adb30' sqlid='077d50q6r30v3' 
SELECT 
  * 
FROM 
  T1 
WHERE 
  1=2 
END OF STMT 
PARSE #7:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3332582666,tim=327318181 
EXEC #7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3332582666,tim=327318181 
WAIT #7: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=527 tim=327322648 
FETCH #7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3332582666,tim=327318181 
STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 time=0 us)' 
STAT #7 id=2 cnt=0 pid=1 pos=1 obj=68961 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us cost=221088 size=5300000000 card=100000000)' 
WAIT #7: nam='SQL*Net message from client' ela= 11075 driver id=1413697536 #bytes=1 p3=0 obj#=527 tim=327343489

The STAT lines in the 10046 trace also confirm that Oracle did not even bother to execute the full table scan.
Just for confirmation that rows do exist in the table:

SELECT 
  COUNT(*) 
FROM 
  T1; 
 
Statistics 
---------------------------------------------------------- 
     1  recursive calls 
     0  db block gets 
813324  consistent gets 
813317  physical reads 
     0  redo size 
   342  bytes sent via SQL*Net to client 
   360  bytes received via SQL*Net from client 
     2  SQL*Net roundtrips to/from client 
     0  sorts (memory) 
     0  sorts (disk) 
     1  rows processed