为什么“在1<>中? 1"；在查询中返回所有行? [英] Why would "Where 1 <> 1" in a query return all rows?

问题描述

我在继承的应用程序中遇到了如下查询:

I've come across a query in an application that I've inherited that looks like this:

Select *
From foo
where
    1 <> 1

正如我解析的那样，

它应该不返回任何内容(1 <> 1应该评估为false，对).但是，(至少在我的Oracle机器上)它返回了foo中所有内容的完整列表.当我在MSAccess/Jet和MSSQL中尝试相同的操作时，我得到了预期的行为. 对于Oracle为什么会有不同(为什么原始开发人员会想要这样做)?

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

注意:我已经对使用"where 1 = 1"的+ s和-s有一些迷信，这引起了全表扫描.但是我不认为这是原始开发人员的意图.

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.

小更新:
在这种情况下，foo是一个视图.当我在实际的表上尝试相同的操作时，我得到了期望的结果(无行).

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

更新2:
我在兔子洞的后面继续跟踪代码，并确定他所做的只是尝试获取字段/列的名称.我仍然不知道为什么它要返回完整的记录集.但仅适用于视图.

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"

在这种情况下，strTableName包含视图的名称.

In this case strTableName contains the name of a view.

更新3:
供参考，这是我遇到的观点之一 (我更改了字段/表/方案名称)

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

附加Order By 1(或foo中select中的某些列名称)似乎使Oracle相信我可以将空集还给我.这是一个长期解决方案，而不是短期解决方案(更改代码并重新部署是主要的PITA).我希望数据库方面存在一些鲜为人知的设置，或者视图中的某些错误是导致这种奇怪行为的原因.

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.

推荐答案

它肯定看起来像Oracle优化器的视图合并代码中的错误.我敢打赌，您只能通过包含外部联接的视图来获得此效果.您的ORDER BY解决了该问题，因为它实际上在视图上强制了NO_MERGE.

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.

我不会在视图中放置ORDER BY或NO_MERGE提示，因为(取决于您的数据量)它可能会降低使用该视图的其他查询的性能.您应该在外部查询中添加一个no_merge提示:

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

您还应该在Oracle支持下提出SR，因为这绝对是一个错误.该查询永远不应该返回任何行，无论您从中选择什么内容，或者内部有多复杂.永远不会.

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?

这篇关于为什么“在1<>中? 1"；在查询中返回所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！