Oracle EXISTS 查询未按预期工作 - DB Link bug？

发布于 2024-09-04 02:54:24 字数 2101 浏览 8 评论 0原文

我对此查询的结果完全感到困惑：

select count(*) from my_tab mt
where  mt.stat = '2473'
  and mt.name= 'Tom'
  and exists (select * from [email protected] cu, 
                [email protected] pr
              where cu.user_id = pr.user_id
              and mt.name = pr.name
              and mt.stat = cu.stat
              )

Returns: 1

There are 0records in [ email protected] 与 stat='2473'，那么为什么它返回 true 存在呢？

如果我像这样更改查询，它会返回 0:

select count(*) from my_tab mt
where  mt.stat = '2473'
  and mt.name= 'Tom'
  and exists (select * from [email protected] cu, 
                [email protected] pr
              where cu.user_id = pr.user_id
              and mt.name = pr.name
              and cu.stat = '2473'
              )

UPDATE 好吧，这真的很奇怪。为了看看会发生什么，我从另一个数据库（数据库链接引用的数据库）执行了查询，它给出了不同的（正确的）结果。

select count(*) from [email protected] mt
    where  mt.stat = '2473'
      and mt.name= 'Tom'
      and exists (select * from company_users cu, 
                    personnel_records pr
                  where cu.user_id = pr.user_id
                  and mt.name = pr.name
                  and mt.stat = cu.stat
                  )

返回 0（如预期）。

原文

I'm completely baffled by the results of this query:

select count(*) from my_tab mt
where  mt.stat = '2473'
  and mt.name= 'Tom'
  and exists (select * from [email protected] cu, 
                [email protected] pr
              where cu.user_id = pr.user_id
              and mt.name = pr.name
              and mt.stat = cu.stat
              )

Returns: 1

There are 0 records in [email protected] with stat='2473', so why is it returning true for the exists?

If I change the query like so, it returns 0:

select count(*) from my_tab mt
where  mt.stat = '2473'
  and mt.name= 'Tom'
  and exists (select * from [email protected] cu, 
                [email protected] pr
              where cu.user_id = pr.user_id
              and mt.name = pr.name
              and cu.stat = '2473'
              )

UPDATE Okay, this is really weird. Just to see what would happen, I executed the query from the other database (the one referenced by the DB Links) and it gave different (correct) results.

select count(*) from [email protected] mt
    where  mt.stat = '2473'
      and mt.name= 'Tom'
      and exists (select * from company_users cu, 
                    personnel_records pr
                  where cu.user_id = pr.user_id
                  and mt.name = pr.name
                  and mt.stat = cu.stat
                  )

Returns 0 (as expected).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

┾廆蒐ゝ 2024-09-11 02:54:24

您问题中的第二个查询有点不同 - 它根本不查看 cu.stat，因此没有解决 cu.stat = '2473' 没有任何内容的事实。如果你执行你会得到什么结果

select count(*)
  from [email protected] cu,     
       [email protected] pr,
       my_tab mt
  where mt.stat = '2473' and
        mt.name = 'Tom' and
        pr.name = mt.name and
        cu.user_id = pr.user_id and
        cu.stat = mt.stat

我认为这相当于你的第一个查询而不使用 EXISTS，并且应该提供正确的结果。

分享并享受。

The second query in your question is a bit different - it doesn't look at cu.stat at all, and thus the fact that there's nothing with cu.stat = '2473' isn't addressed. What results do you get if you execute

select count(*)
  from [email protected] cu,     
       [email protected] pr,
       my_tab mt
  where mt.stat = '2473' and
        mt.name = 'Tom' and
        pr.name = mt.name and
        cu.user_id = pr.user_id and
        cu.stat = mt.stat

I think this is equivalent to your first query without the use of EXISTS, and should provide correct results.

Share and enjoy.

回复收藏 0 原文