Strange behaviour in 11.70
Hello,
we boiled down a problem at a customers site (AIX 6.1 IDS 11.70FC2) to the
following delete statement:
DELETE FROM table_a
WHERE key IN (SELECT key FROM table_b)
AND key NOT IN (SELECT key FROM table_c)
To my surprise this statement does not delete all affected rows!
When afterwards I execute the following select with exactly the same condition:
select count(*) FROM table_a
WHERE key IN (SELECT key FROM table_b)
AND key NOT IN (SELECT key FROM table_c)
it expect it to return 0 rows, but it gives me 39 rows.
To my opinion those subqueries are not correlated, and prior to 11.70 we
encountered no problems.
We found a solution with a slightly different query condition that works fine:
DELETE FROM table_a
WHERE key IN (SELECT key FROM table_b
WHERE key not in (SELECT key FROM table_c))
Because nobody knows where all those potentially misbehaving statements are
hidden, the customer feels a bit nervous.
Can anybody help?
Thanks for any suggestions.
Peter
Can you send the explain plans for both delete's just to see who
the optimizer is evaluating the two queries.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 05/25/2011 11:32:34 PM:
> [image removed]
>
> Strange behaviour in 11.70 [23842]
>
> PETER IJEWSKI
>
> to:
>
> ids
>
> 05/25/2011 11:34 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> [image removed]
>
> From:
>
> "PETER IJEWSKI" <peter@ijewski.de>
>
> To:
>
> ids@iiug.org
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids@iiug.org
>
> Hello,
>
> we boiled down a problem at a customers site (AIX 6.1 IDS 11.70FC2) to
the
> following delete statement:
>
> DELETE FROM table_a
> WHERE key IN (SELECT key FROM table_b)>
> AND key NOT IN (SELECT key FROM table_c)
>
> To my surprise this statement does not delete all affected rows!
>
> When afterwards I execute the following select with exactly the same
> condition:
>
> select count(*) FROM table_a
> WHERE key IN (SELECT key FROM table_b)>
> AND key NOT IN (SELECT key FROM table_c)
>
> it expect it to return 0 rows, but it gives me 39 rows.
>
> To my opinion those subqueries are not correlated, and prior to 11.70 we
> encountered no problems.
>
> We found a solution with a slightly different query condition that
> works fine:
>
> DELETE FROM table_a
> WHERE key IN (SELECT key FROM table_b>
> WHERE key not in (SELECT key FROM table_c))
>
> Because nobody knows where all those potentially misbehaving statements
are
> hidden, the customer feels a bit nervous.
>
> Can anybody help?
>
> Thanks for any suggestions.
>
> Peter
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hi,
the explain plans follow:
::::::::::::::::::::::::::::::::::::::::::::::::::::::::
QUERY: (OPTIMIZATION TIMESTAMP: 05-27-2011 13:44:55)
------
DELETE FROM kxp
WHERE IK IN (SELECT IK FROM atx) AND
IK NOT IN (SELECT IK FROM aporz)
Estimated Cost: 13
Estimated # of Rows Returned: 1
1) xxx.kxp: INDEX PATH
(1) Index Name: xxx.ix_kxp2
Index Keys: ik status gueltig_ab gueltig_bis (Key-First) (Serial, fragments:
ALL)
Lower Index Filter: xxx.kxp.ik = ANY <subquery>
Index Key Filters: (xxx.kxp.ik != ALL <subquery> )
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) xxx.atx: SEQUENTIAL SCAN
Subquery:
---------
Estimated Cost: 10
Estimated # of Rows Returned: 204
1) xxx.aporz: INDEX PATH
(1) Index Name: xxx.ix_aporz
Index Keys: ik status gueltig_ab gueltig_bis (Key-Only) (Serial, fragments:
ALL)
::::::::::::::::::::::::::::::::::::::::::::::::::::::::
QUERY: (OPTIMIZATION TIMESTAMP: 05-27-2011 13:46:37)
------
DELETE FROM kxp
WHERE IK IN (SELECT IK FROM atx WHERE IK NOT IN (SELECT IK FROM aporz))
Estimated Cost: 13
Estimated # of Rows Returned: 1
1) xxx.kxp: INDEX PATH
(1) Index Name: xxx.ix_kxp2
Index Keys: ik status gueltig_ab gueltig_bis (Serial, fragments: ALL)
Lower Index Filter: xxx.kxp.ik = ANY <subquery>
Subquery:
---------
Estimated Cost: 12
Estimated # of Rows Returned: 1
1) vdak_dba.atx: SEQUENTIAL SCAN
Filters: xxx.atx.ik != ALL <subquery>
Subquery:
---------
Estimated Cost: 10
Estimated # of Rows Returned: 204
1) xxx.aporz: INDEX PATH
(1) Index Name: xxx.ix_aporz
Index Keys: ik status gueltig_ab gueltig_bis (Key-Only) (Serial, fragments:
ALL)
::::::::::::::::::::::::::::::::::::::::::::::::::::::::
I don't see any real difference according to theory of sets.
Any Idea?
Peter Ijewski