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

You would typically use something like that when you want to only return all of the the columns for the SQL table. If it isn't working in Oracle, you may want to try something similarly wrong like:

Select *
From foo
where
    1 == 2

or possibly

where
    key < 0

Answer 2

This is going to sound strange, but does the view/table have a column named "1"?

Answer 3

Sounds like bug in the view-merging code in Oracle. Oracle will take your WHERE clause and merge it into the view SQL, and then come up with a plan for that.

Try your select with this hint and see if the problem goes away:

SELECT /*+ NO_MERGE */ ...

You could also look at an EXPLAIN PLAN to get some insight into what is going wrong.

Answer 4

Why use WHERE 1<>1?

The one place I have seen this used, or even used it myself is as a quick way to copy the structure of a table without copying the contents:

create table foo2
as select * from foo where 1 <> 1;

(except that I always use != rather than <> - which I really shouldn't (see Bill's comment))

Apparent Oracle Bug

If you have a case where you can clearly demonstrate that Oracle is returning rows in SQL Plus when you execute "select * from my_view where 1<>1" then you should contact Oracle support (or get the authorised person in your company to do so): it would indicate a significant bug. Of course, if you are using an old version of Oracle they will probably just tell you to upgrade!

Answer 5

WHERE 1 = 1 should cause a full table scan, just as omitting the WHERE clause entirely. If you're retrieving every row from the table, of course it's a full table scan.

I can't comment about WHERE 1 <> 1 failing to work as expected on Oracle. That sounds really wrong. Are you certain you saw the result you describe from that query? Try it again to be sure.

Answer 6

It would be very interesting to see the execution plan for the query, using ...

explain plan for select ...;

select * from table(dbms_xplan.display);

If you're querying a view then this might show how the predicate is being evaluated in the wrong phase