Re: Problem With Exists in 7.3

Hi again, I'm sorry, Alexander, I looked again and realized that I missed the point... You're right, that looks like a real bug... Regards, Alexey -----Original Message----- From: Alexey Bespaliy <abespaly@informix.com> To: Alexander V.Didytch <unknown.thegreat@usa.net> Cc: informix-list@iiug.org <informix-list@iiug.org>; David Kosenko <dkosenko@monmouth.com> Date: Wednesday, June 03, 1998 11:03 AM Subject: Re: Problem With Exists in 7.3 >Hi Alexander, > >Well, don't you forget to include t1 in the FROM clause of the last two >queries? ;) >It does work (if corrected, of course). > >Regards, >Alexey > >-----Original Message----- >From: Alexander V.Didytch <unknown.thegreat@usa.net> >To: David Kosenko <dkosenko@monmouth.com> >Cc: informix-list@iiug.org <informix-list@iiug.org>; abespaly@informix.com ><abespaly@informix.com> >Date: Tuesday, June 02, 1998 8:20 PM >Subject: Re: Problem With Exists in 7.3 > > >>Hi Again ! >> >>Don't you remember my last mail about problem with insert >>in 7.3 ? Now I thought we had found the real beast -- >>OPTIMIZER. >> >>Once again: >>----------- >> >>CREATE TEMP TABLE t1 (a integer); >>CREATE TEMP TABLE t2 >>( >> _row_id INTEGER NOT NULL , >> PRIMARY KEY (_row_id) >>-- ^^^^^^^^^^^ >>) >>WITH NO LOG;>> >>INSERT INTO t1 VALUES(1); >>INSERT INTO t2 SELECT ROWID FROM t1;>> >>-- Now we test the JOIN : >> >>SELECT t1.ROWID , t2._row_id >>FROM t1, t2 >>WHERE t1.ROWID = t2._row_id;>> >>-- It returns exactly one record ! >>-- But when we perform the following: >> >>SELECT >>* FROM t1 >>WHERE EXISTS( >> ^^^^^^ >> SELECT _row_id FROM t2 where t2._row_id = t1.rowid >>);>> >>-- AND NOTHING RETURNS >> >>-- EVEN MORE: >>SELECT >>* FROM t1 >>WHERE NOT EXISTS( >> ^^^^^^^^^^ >> SELECT _row_id FROM t2 where t2._row_id = t1.rowid >>);>> >>-- AND NOTHING RETURNS AGAIN ! >> >>When we had seen the sqlexec plan it was as follows: >> >>Estimated Cost: 3 >>Estimated # of Rows Returned: 10 >> >>1) informix.t2: INDEX PATH >> ^^^ TABLE IN EXISTS WAS PUTTEN FIRST ! >> >> (1) Index Keys: _row_id (Key-Only) >> Lower Index Filter: >> informix.t2._row_id = informix.t1.ROWID >> ^^^^^^^^^^^^^^^^^ >> -- WHERE IT CAN TAKE THIS ? >> >>2) informix.t1: SEQUENTIAL SCAN >>NESTED LOOP JOIN >> >> >>The same things are going on when we used IN instead of EXISTS. >>We had solved this problem by taking off primary keys on t1 and >>replace it with unique key. >> >>I know that Informix has done a lot with 7.3 optimizer. May be >>somebody knows another problems with it ? >> >>Sincerely, Alexander >>-- >>Alexander V.Didytch, Kyiv, Ukraine >> >>"Those who fail to learn lessons early and clearly, and take effective >action >>to correct bad situations, are doomed to repeat them irretrievably >throughout >>the project" -Gilb, 1997 >> >> >> >> >