Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 11 Feb 2004 @ 16:02:59 GMT

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

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

  The subquery "select max(c1) from n1 where c1 < 0" where all values of c1 being > 0 is a known result - no rows. As in all the discussions on this board about comparing a value with null - null signifies unknown. So in all the other databases, returning a value of null is surely misleading - the empty set is the right answer.  

I think what matters here is whether MAX and EXISTS comply with the ANSI standards. I had a chance to look up what ANSI specify in this respect. They are quite clear about the EXISTS. It just checks the cardinality of the result set returned by the subquery. If 0 then FALSE; >0 then TRUE. So, this part is simple and everything depends on what MAX should return on the empty set. The standard does not discuss this special case (empty set) separately but it requires that the data type of MAX(c1) (the same for the MIN) should always be the same as that of c1. I understand this as MAX should always return a row, and in case of an empty set it can only return NULL (which other value would fit?).

If my reading of the standard is correct, it implies that

EXISTS (select max(c1) from n1 where c1 < 0) should always evaluate to TRUE.

  So isn't the bug in all the other databases?  

Perhaps it is but they are at least ANSI compliant, for what it's worth.



  <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