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.