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
>
>
>
>