Re: where != and NULLs

I don't think it is a bug -- it is both the normal behaviour and the correct behaviour as mandated by all versions of the ANSI SQL standard. >Date: Mon, 26 Aug 1996 12:36:06 -0400 (EDT) >From: Peter Wages <pmwages@cais.com> >X-Informix-List-Id: <list.11151> > >Peculiar Circumstance in Version 7. Is this a bug? > > Do an agregate query > > select sum(column_1) > from table_a > where column_2 != 'value' There are 3 possible cases: column2 contains 'value', 'othervalue' or NULL. If it contains 'value', the condition evaluates to false and the record is excluded. If it contains 'othervalue', the condition evaluates to true and the record is counted. If it contains NULL, the condition evaluates to unknown, and since that is not true, the record is excluded. >If column_2 is NULL for some records, the sum of column_1 will be >incorrect. To get the correct sum the query has to be written > > select sum(column_1) from table_a > where column_2 != 'value' or column_2 is NULL This is the correct formulation. Such are the wonders of 3-valued logic. > Me thinks there is a logic problem here. If column_2 is NULL then >column_2 != 'value' False thinking -- if column2 is NULL, then the expression "column2 != 'value'" evaluates to unknown, not to false. It could be that column2 is equal to value; it isn't known what that NULL represents. > This is a big nuissance. This is SQL. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>