Why does "Where 1 <> 1" in the query return all rows?

Question

I met a request in an application that is inherited that looks like this:

 Select * From foo where 1 <> 1 

As I parse this, it should not return anything ( 1 <> 1 should evaluate to false, right). However (at least on my Oracle box) it returns with a complete list of everything in foo . When I try to do the same in MSAccess / Jet and MSSQL, I get the expected behavior. Why is this different from Oracle (and why does the original developer want to do this)?

Note. I saw some superstition regarding the + s and -s uses of "where 1 = 1", and this causes a full table scan; but I don’t think this is what the original developer intended.

Small update:
In this case, foo is the view. When I try to do the same on the actual table, I get what I expect (no rows).

Update 2:
I follow the code further down the rabbit hole and decided that everything he does is trying to capture the field / column names. I am still at a loss why it returns a full set of records; but only on representations.

Literally, he builds the query in a string and passes it so that another function runs without changes.

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

In this case, strTableName contains the name of the view.

Update 3:
For reference, here is one of the opinions I am having problems with (I changed the names of the fields / tables / schemas)

 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) ; 

Adding Order By 1 (or some column name in select on foo) seems to convince Oracle to return an empty set to me. This is a long-term solution, but not a short-term one (code change and redistribution is a big PITA). I hope there’s a little known tweak on the DB side or something is wrong with the view, which is the reason for this odd behavior.

Answer 1

This definitely looks like an error in combining Oracle optimizer code. I bet you only get this with views that contain external connections. Your ORDER BY solves it because it practically makes NO_MERGE see.

I would not add ORDER BY or NO_MERGE , because (depending on your data volume) this can degrade the performance of other queries that use the view. You must specify the no_merge hint in the outer request:

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

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

I could not reproduce it, so it is probably fixed in the version I am using. What version of db are you using?

Answer 2

Well ... why is this happening in Oracle, it's outside of me. However, I can tell you why it is often used in other databases: when a person needs returned columns, but there are no values. (For example, to create a schema for a new table)

Answer 3

Oracle does not do this 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 has nothing to do with views:

 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 4

If you want to dynamically generate a WHERE . That way, you can just add some OR [another-condition] clauses and make it work without checking if the condition is first or not.

Answer 5

Why use WHERE 1 <> 1?

One place I've seen used or used it myself is a quick way to copy a table structure without copying the contents:

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

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

Clear Oracle Error

If you have a case where you can clearly demonstrate that Oracle returns rows in SQL Plus, when you run "select * from my_view, where 1 <> 1", you should contact Oracle Support (or get an authorized person in your company for this): this indicates a significant error . Of course, if you are using an older version of Oracle, they will probably just tell you about the upgrade!

Answer 6

Sounds like an error in merge code in Oracle. Oracle will accept the WHERE clause and combine it in the SQL view, and then come up with a plan for this.

Try selecting this hint and see if the problem persists:

 SELECT /*+ NO_MERGE */ ... 

You can also watch PLUS EXPLAIN to get an idea of ​​what is going wrong.

Answer 7

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

Answer 8

Just brainstorming here, and it may be completely wrong, but I want to say that I saw some SQL parsers parse unspecified integers as column X values. You can confirm this by trying:

SELECT 1 FROM foo WHERE 1 <> 1

If 1 is populated with values ​​from the first column of your table, you probably want to stick to integers:

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

But, again, I could be completely wrong here. I have no way to install Oracle to try .: P

Answer 9

WHERE 1 = 1 should cause a full table scan, as well as omit the WHERE . If you extract each row from a table, of course, this is a full table scan.

I cannot comment on WHERE 1 <> 1 , which does not work properly in Oracle. That sounds wrong. Are you sure you saw the result that you described from this query? Please try again to make sure.

Answer 10

Usually you use something like this when you only want to return all the columns of an SQL table. If it does not work in Oracle, you may try something like this incorrectly:

 Select * From foo where 1 == 2 

or maybe

 where key < 0 

Answer 11

Perhaps if you just want to test the connection to the database.

Answer 12

It would be very interesting to see a query execution plan using ...

 explain plan for select ...; select * from table(dbms_xplan.display); 

If you request a view, this can show how the predicate is evaluated in the wrong phase.

Answer 13

SELECT is probably not broken. First, get the exact query string that causes this behavior. Run the query directly in the database and not through the VB 6 application. Does the problem still occur? Go from there.

Answer 14

Partial solution

Refresh the view by adding order by 1 according to 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 considers the symptom (and I don't need to recompile and redeploy the code), but does not tell me why I get this strange behavior.

Update: Performing order on a string constant has the same effect, but does not change the plan (as shown in the explanation plan). I suspect that it will be executed faster than the order by 1 (which, I think, should be sorted 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 15

Associated with Cody Casterline's thoughts: is there column 1 in the view, possibly full of NULL values? If Oracle interprets WHERE as "a value in column 1 not equal to a value in column 1" and a value in column 1 is NULL, we introduce the strange world of NULL SQL. This is the case in every SQL dialect, which I know that NULL = NULL is incorrect. Perhaps Oracle decided that NULL <> NULL should therefore be true?