Re: NOT EXIST clause

If people could check a little before they blamed Informix (or other vendors) for what they regard as strange behaviour the world would be just a little better. Just ask in c.d.i. and there are a lot of people that will give good answers. sean@infosol.ie (Sean Birkhead) wrote: ...snip of previous posts... :Nils, : :There is a rather nice example of not knowing too much about the behaviour of null's. : quoted by a colleague of mine :attached below. If this example is tried on Interbase, for example, :the NULL anomaly described in the above posting does not appear. In which case there is a serious defect in Interbase and I am very happy for not using it. It's enough to deal with null when everything works as it should (mostly it isn't very hard though). It would be fatal to have to deal with them if it wheren't. :Any more enlightened comments on this? I allready posted an explanation of this about a week ago, but will repeat a short explanation here. You might want to tell Cyril O'Floinn about this as well. :Why should it work for "IN" as opposed to "NOT IN" as shown in the :example? In SQL a null in a column means the value is unknown. Remember null is not a value - it's unknown. If you ask whether a spesific value (meaning not null) is a member of a set (using "IN") one can determin this easily. In the example (see below) the account codes A0001, A0002, A0003 for the invoices I0001,I0002,I0003 are in the customer table. For the other invoices the "IN" clause will se the null in the customer table and determin that it's unknown whether these are in the table or not. As SQL have no idea about unknown (other than via the "IS (NOT) NULL" operator) it takes it as false (as per the standard). When "NOT IN" is used the row with NULL in the code field is seen. Remember the value is unknown. With other words it's unknown whether any account code is in the table or not. Again as SQL don't know about unknown it will allways return false and the whole statement will return no rows. This is behaviour according to the SQL standard and the way it have to be if null means an unknown value. If you disagree don't ask Informix to do anything - talk to the SQL standardisation committee. I doubt you will get any results. What they should do is to implement proper support for default values and real domains. Then you would have had an alternative. :Anybody else want to comment ????? :Sean Birkhead. : : : :From: Cyril O'Floinn <cyrron@infosol.ie> :To: 'Sean Birkhead' <sean@infosol.ie> :Subject: Nulls & Informix :MIME-Version: 1.0 :Content-Type: text/plain; charset="us-ascii" :Content-Transfer-Encoding: quoted-printable : :There is a serious problem with the way Informix Database Engine(SE :5.03) :handles null values in a set. : :Take 2 tables, invoice and customer where invoice.customer references :customer.code: : :create table invoice ( : ref char(10) not null, : account char(10) :); : :create table customer ( : code char(10), : name char(30) :); : :Say these 2 tables have the following values: : :-----Invoice------ =20 :ref account :============ :I0001 A0001 :I0002 A0002 :I0003 A0003 :I0004 X0001 :I0005 X0002 :I0006 X0003 : :---customer------ :code name :========== :<null> Mr. Headache :A0001 John Smith :A0002 Frank Sinatra :A0003 Roger Rabbit : :If you wanted to see all invoices that have a valid customer then :the following select statement works without any problems: : : SELECT * : FROM invoice : WHERE account IN ( SELECT code FROM customer); : :This would return invoice's I0001,I0002,I0003 as you would expect.=20 :However say you wanted to see those invoices that DO NOT have :a valid customer then you would naturally do the following: : : SELECT * : FROM invoice : WHERE account NOT IN (SELECT code FROM customer); : :The above should return invoices I0004,I0005,I0006 yet it does not. :In fact no invoices are found whatsoever. :If you remove however the customer will a <null> code the NOT IN :performs :exactly as you would expect and invoices I0004,I0005 & I0006 are :returned. : :It seems that just one null value in a set causes the Informix engine :to go blind if that set is being used with a NOT clause. : :P.S. Has anyone else out there come across the float "error" in 4GL : v4.11 or above ? : Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org