Re: Problem With Exists in 7.3
The bug number is 95376.
You can set the environment variable NO_SUBQF as a workaround.
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:31 AM
Subject: 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
>>>
>>>
>>>
>>>
>>
>