Why would “Where 1 <> 1” in a query return all

Question

I've come across a query in an application that I've inherited that looks like this:

Select *
From foo
where
    1 <> 1

As I parse that, it should return nothing (1 <> 1 should evaluate to false, right). However (at least on my Oracle box) it comes back with a full listing of everything in foo. When I try the same thing in MSAccess/Jet and MSSQL I get the behaviour I expect. Why is it different for Oracle (and why would the original developer want to do this)?

Note: I've seen some superstition about the +s and -s of using "where 1 = 1", and it causing full table scans; but I don't think this is what the original developer was intending.

Small Update:
In this case foo is a view. When I try the same thing on on an actual table, I get what I would expect (no rows).

Update 2:
I've following the code further down the rabbit hole and determined that all he's doing is trying to grab the field/column names. I'm still at a loss as to why it's returning the full record set; but only on views.

Literally, he's building the query in a string and passing it on for another function to execute unaltered.

'VB6
strSQL = "SELECT * FROM " & strTableName & " WHERE 1 <> 1"

In this case strTableName contains the name of a view.

Update 3:
For reference, here is one of the views I'm having problems with (I've changed the field/table/schema names)

CREATE OR REPLACE FORCE VIEW scott.foo (field1,
                                        field2,
                                        field4,
                                        field5,
                                        field12,
                                        field8,
                                        field6,
                                        field7,
                                        field16,
                                        field11,
                                        field13,
                                        field14,
                                        field15,
                                        field17
                                       )
AS
   SELECT   bar.field1,
            bar.field2,
            DECODE
               (yadda.field9, NULL, 'N',
                DECODE (yadda.field3, NULL, 'Y', 'N')
               ) AS field4,
            bar.field5,
            snafu.field6,
            DECODE
                (snafu.field6,
                 NULL,
                bar.field8,
                   bar.field8
                 - snafu.field6
                ) AS field7,
            DECODE
               (yadda.field10,
                NULL,
            bar.field12,
                yadda.field10
               ) AS field11,
            DECODE
               (SIGN (  yadda.field10 - bar.field12),
                NULL, 'N', 1, 'N', 0, 'N', -1, 'Y'
               ) AS field13,
            bar.field14,
            ADD_MONTHS
               (DECODE (yadda.field10, NULL, bar.field12, yadda.field10
                       ),
                bar.field14 * 12
               ) AS field15,
       FROM clbuttic,
            bar,
            yadda,
            snafu
      WHERE clbuttic.asset_type = bar.asset_type
        AND bar.field16 = yadda.field9(+)
        AND bar.field1 = snafu.field1(+)
        AND (bar.field17 IS NULL)
   ;

Appending Order By 1 (or some column name in the select on foo) seems to convince Oracle to give me back the empty set. It's a long term solution, but not a short term one (changing he code and redeploying is a major PITA). I'm hoping there's a little known setting on the DB side or something wrong in the View that is the cause of this odd behaviour.

Answer 1

Partial solution

Update the View by appending order by 1 following the where clause.

WHERE clbuttic.asset_type = bar.asset_type
    AND bar.field16 = yadda.field9(+)
    AND bar.field1 = snafu.field1(+)
    AND (bar.field17 IS NULL)
order by 1;

It treats the symptom (and I don't have to recompile and redeploy the code), but doesn't tell me why I'm getting this strange behavior.

Update: Doing the order by on a string constant has the same affect, but doesn't alter the plan (as shown by explain plan). An I suspect it will be executed faster that order by 1 (which I think should sort by the first column).

WHERE clbuttic.asset_type = bar.asset_type
    AND bar.field16 = yadda.field9(+)
    AND bar.field1 = snafu.field1(+)
    AND (bar.field17 IS NULL)
order by "a";

Answer 2

When you want to dynamically generate a WHERE clause. This way, you could just append some OR [another-condition] clauses and make it work without checking whether the condition is the first one or not.

Answer 3

Just brainstorming here, and may be completely wrong, but I want to say I've seen some SQL parsers parse unquoted integers as meaning "column X". You can confirm this by trying:

SELECT 1 FROM foo WHERE 1 <> 1

If 1 is full of values from the first column of your table, you probably want to stick to quoted integers:

SELECT * FROM FOO WHERE '1' <> '1'

But, again, I could be completely wrong here. I don't have an Oracle install handy to try it out on. :p