Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 10 Feb 2004 @ 00:36:27 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: EXISTS predicate bug?
From:   Victor Sokovin

Interesting observation! I just checked your ideas on Oracle and

select count(*) from demo.customer
where exists (select max(customer_id) from demo.customer where 1 < 0)

returns 33, which is indeed the full count, the same as in

select count(*) from demo.customer

I then modified the subquery (included the NVL function) to visualize what it returns.

select nvl(max(customer_id), -1) from demo.customer where 1 < 0

returns -1. Meaning that MAX returns NULL here. The fact that something is returned by the subquery makes Oracle EXISTS evaluate to TRUE. Everything is at least internally consistent.

The differences you described can be quite a gotcha with Oracle - TD migrations, so thanks for the warning. For what it's worth, I personally dislike EXISTS in all databases but sometimes it is unavoidable, so it's better to be prepared for those differences.

The query with UNION puzzles me the most, BTW.



  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023