tags:

views:

868

answers:

15

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.

+15  A: 

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)

Beska
i agree that's a good point...but people should really be using where 1=0 for that purpose IMO.
dotjoe
+1 New concept I have never been exposed to this...
Sung Meister
@dotjoe ? Why? What difference would it make? One obviously false statement vs. another.
Beska
@beska: to me, just glancing at 1=0 is easier to see false than glancing at 1<>1 or 1!=1. totally a personal preference.
dotjoe
+6  A: 

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.

Mehrdad Afshari
But 1 <> 1 is sure to not return any rows, whatever the other conditions are.
Akbar ibrahim
1 <> 1 OR 1 = 1 will return all rows. Think twice
Mehrdad Afshari
It's much more common (I would say better) to use "WHERE 1=1 AND ...".
Tony Andrews
@Tony: They satisfy different purposes. The OR thing can be used in constructing OR search queries where at least one of keywords should match. AND one might be used where you want to check existence of all keywords.
Mehrdad Afshari
+3  A: 

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!

Tony Andrews
FWIW, <> is standard SQL, whereas != is not standard (though it is supported by several database brands).
Bill Karwin
That's worth knowing, thanks! Pity, because != expresses "not equal" far more eloquently IMHO.
Tony Andrews
Interesting. I never thought about it before, but I parse != as "not equal" and <> as "inequal". I wonder what I think the difference is? :-)
Ben Blank
I dunno if it's important to prefer <> over !=. Most programmers are clearly familiar with != from its use in other languages. And I'd guess you will never need to use a database that doesn't support !=.
Bill Karwin
That indeed appears to be what the previous programmer was doing.
S Ennis
+1  A: 

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.

Bill Karwin
Yup, absolutely sure. I pulled it out and ran it by hand in SQLPLUS and TOAD
S Ennis
+7  A: 

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
Paul Tomblin
You're right. The code I was running was going against a view, not a table. Tables give me the results I expect.
S Ennis
+1  A: 

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
Brian
Good suggestion. But remember that double-equals is not the equality comparison operator in SQL. You need to use a single-equals in SQL. Just a matter of trivia FWIW.
Bill Karwin
I get the same result when I change the where clause from "1 <> 1" to "1 = 2". It's bizarre.
S Ennis
+1  A: 

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

job
+2  A: 

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

Chad Birch
Nope. I though of that.
S Ennis
+2  A: 

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

Cody Casterline
I had this thought as well, because I know that you can do this inside the ORDER BY clause, with something like "ORDER BY 1" to order by the first column. I didn't get the same behavior inside a WHERE though, so I really have no idea.
Chad Birch
I get a column called "1" full of about 200,000 rows all consisting of "1".
S Ennis
Yah know, when I add Order by (any column name / #) I now get an empty set. I can change the code later, but I'm still looking for the why.
S Ennis
A: 

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.

recursive
I've already tried that through SQLPLUS and through TOAD (Quest Software), with the same results.
S Ennis
A: 

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";
S Ennis
A: 

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?

Jon Bright
Nope. The first column happens to be full of data (it is auto numbering). The other columns are on the whole non null as well. If this was the case I would expect my "order by 1" trick not to work.
S Ennis
+3  A: 

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.

WW
NO_MERGE had no effect on the results (on the view or the select). However playing with explain plan did lead to some curious results. adding 'order by 1' flipped the plan on its head, but adding 'order by "a"' did not (yet still gave me the empty set I wanted).
S Ennis
+1  A: 

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

David Aldridge
See my comment for WW.
S Ennis
+5  A: 

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?

Gabor Kecskemeti
I've got 10gR2 (10.2.0.1.0) with patchsets 1 (10.2.0.2.0) and 3 (10.2.0.4.0). I found that the NO_MERGE hint did not solve the problem in any way, and the performance problem not having it in the view right now is worse than what might happen to other queries that depend on them (so I'll go for it).
S Ennis
However, I agree this has to be a bug. So +1 and cred.
S Ennis