Re: Problem With Exists in 7.3
In article <6l35if$jh9$1@news.xmission.com>, Alexey Bespaliy
<abespaly@informix.com> writes
>
>The bug number is 95376.
>You can set the environment variable NO_SUBQF as a workaround.
>
Set it to what? Or is it any value...one more for the FAQ..
>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
>>>>
>>>>
>>>>
>>>>
>>>
>>
>
--
David Williams
Maintainer of the Informix FAQ
Primary site (Beta Version) http://www.smooth1.demon.co.uk
Official site http://www.iiug.org/techinfo/faq/faq_top.html
I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care