Why would "Where 1 <> 1" in a query return all rows?

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

Okay...why this would happen in Oracle is beyond me. However, I can tell you why it's often used in other DBs: when the person wants the columns returned, but no values. (Such as for creating a schema for a new table)

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

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 4

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 5

Oracle doesn't do that for me:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 19 13:36:20 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from wrkr where 1 <> 1;

no rows selected

SQL> select count(*) from wrkr;

  COUNT(*)
----------
        88

Edit: It's nothing intrinsic with views, either:

SQL> create view foo as select * from wrkr;

View created.

SQL> select count(*) from foo;

  COUNT(*)
----------
        88

SQL> select * from foo where 1 <> 1;

no rows selected

Answer 6

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 7

Perhaps if you wanted to simply test a connection to the database.

Answer 8

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

Answer 9

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

Answer 10

SELECT probably isn't broken. First, get the exact query string that's causing this behavior. Run the query directly on the DB, not through the VB 6 app. Is the problem still occurring? Go from there.

Answer 11

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 12

Related to Cody Casterline's thoughts: is column 1 in the view maybe full of NULL values? If Oracle is interpreting the WHERE as "value in column 1 not equal to value in column 1" and the value in column 1 is NULL, we enter the strange world of SQL NULLs. It's the case in every SQL dialect I know that NULL=NULL is not true. Perhaps Oracle decided that NULL<>NULL therefore must be true?

Answer 13

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 14

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

Answer 15

It definitely looks like a bug in the view merging code of the Oracle optimizer. I bet you only get this with views which contain outer joins. Your ORDER BY solves it, because it practically forces a NO_MERGE on the view.

I wouldn't put either an ORDER BY or a NO_MERGE hint inside the view though, because (depending on your data volume) it could degrade performance of other queries which use the view. You should put a no_merge hint in the outer query:

Select /*+ NO_MERGE(foo) */ *
From foo
where
    1 <> 1

You should also raise an SR with Oracle support, as this is definitely a bug. That query should never ever return any rows no matter what you are selecting from, or how complex it is inside. Never ever.

I couldn't reproduce it, so it's probably fixed in the version I'm using. What's the db version you are using?