

Archives of the TeradataForum
Message Posted: Wed, 11 Feb 2004 @ 16:02:59 GMT
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.
Regards,
Victor
 