Re: NOT EXIST clause

In <33609b61.16732299@gate.idg.no>, Nils Myklebust wrote: >sean@infosol.ie (Sean Birkhead) wrote: > >:There is probably a null being returned in the subquery which messes >:everything up . This is a bug in Informix as far as I'm concerned. > >I have never found or even heard about any bugs in Informix related to >null "values" since they first implemented them (in 1987 was it?). > > >In another post sean@infosol.ie (Sean Birkhead) wrote: > >:This solution will not work if there is a null value in commonfiled >in >:any row of table b. >: >:Try the following : >: >: select * from test:a where commonfield not in >: (select commonfield from live:b >: where commonfield is not null) >: >:or even >: >: select * from test:a where commonfield not in >: (select commonfield from live:b >: where length(commonfield) > 0 ) >: >:Try eliminating any nulls that are returned. This is easier done by >:matching specific fields in the query rather than doing a Select *. >: >:This is certainly the case under SE 5 anyway - maybe they fixed it >for >:online. > >They can't possibly "fix" something that works exactly as it should. >The behaviour you describe is according to the standard and the way >null's should behave. > >You might want to claim that the spesifications related to null's in >the ANSI standard for SQL is a "defect", but that is another issue. > > >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 Nils, There is a rather nice example 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. Any more enlightened comments on this? Why should it work for "IN" as opposed to "NOT IN" as shown in the example? 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 ?