This is not intended to be a discussion if 1 actually equals 2. But has to do with a line of sql from the other day.

I had a request come in at work to disable a tab in one of our applications that showed historical data for customer accounts. My initial thought was that we would have to make a change to the application and redeploy. Pain in the arse! When it dawned on me (which possibly was Adam slapping me up the side of the head) that since the application depended on the results of a query in Oracle, I could just force the query to return no rows, effectively showing no history in the application.

So the quick fix was to disable the query by adding to the where clause “and 1=2”. So regardless what the skeptics say, in Oracle land 1 will never equal 2. So the query returns 0 rows no matter what parameters are passed in.

Fantastic! One quick after hour change (b/c of packages supporting “State”, discussion for another time), we had history disabled in the application.

So what does 1=2 actually do?

Now what was really interesting was running an explain on the query and discovering how Oracle deals with 1=2. As with most compilers, Oracle will attempt to simplify code as best it can to optimize performance.

Consider the following example in C#:

1
2
3
4
5
6
7
8
9
10
11

static void Test1()
{
  if (1 == 2)
  {
    Console.WriteLine("True Statement");
  }
  else
  {
    Console.WriteLine("False Statement");
  }
}

The IF statement is pointless since at runtime you will always end up in the ELSE. Checking the code in Reflector after compiling in Release mode shows that the optimizer catches the waste of cpu cycles and space and consolidates:

1
2
3
4

private static void Test1()
{
  Console.WriteLine("False Statement");
}

Now this change made by the compiler makes sense in this situation.

What if 1=2 is crucial?

Consider the following from Oracle:

1
2
3
4

SELECT uo.object_name,
       uo.object_type
  FROM user_objects uo
 WHERE uo.object_type = 'TABLE';

The query will return a list of table(s) owned by the user (assuming the user owns at least one table).

Adding the 1=2 condition:

1
2
3
4
5

SELECT uo.object_name,
       uo.object_type
  FROM user_objects uo
 WHERE uo.object_type = 'TABLE'
       AND 1=2;

The 1=2 will cause the query to always return 0 rows. Now of course the optimizer can’t remove the 1=2 at runtime without changing the intended results. So what does Oracle do?

Taking a look at the explain: “1=2” is translated to “NULL IS NOT NULL”

So the Resulting SQL more or less becomes:

1
2
3
4
5

SELECT uo.object_name,
       uo.object_type
  FROM user_objects uo
 WHERE uo.object_type = 'TABLE'
       AND NULL IS NOT NULL;

Brilliant!

I about fell in the floor laughing when I saw this when making the aforementioned code change. I proceeded to change my where clause to “NULL IS NOT NULL”. And look forward to the occasional laugh when a co-worker stumbles upon the code.

Now I haven’t checked to see how Oracle deals with 1=2 in a IF statement in pl/sql. I would assume it would handle it in a similar fashion as .net does. This situation would actually make a better comparison to how .NET vs Oracle deals with optimizing code.