Re: Subselect problem

Ok, here's some more details.

This is my query:

SELECT ah.FEATURE_ID, FG.TILE_NUM FROM
nndb.admin_hierarchy ah
JOIN NNDB.LINK_ADMIN LA
ON ah.FEATURE_ID = LA.ADMIN_ID
JOIN psi.FEATURE_GEOMETRY FG
ON LA.LINK_ID = FG.NNDB_FEATURE_ID
WHERE  fg.tile_num = 8613949  and ah.feature_id in (SELECT AH2.FEATURE_ID
FROM NNDB.ADMIN_HIERARCHY AH2
LEFT JOIN psi.FEATURE_GEOMETRY FG2
ON AH2.FEATURE_ID = FG2.NNDB_FEATURE_ID
WHERE FG2.nndb_feature_id IS NULL)


This is the output of EXPLAIN:


"Hash Join  (cost=87.20..105.37 rows=13 width=8)"
"  Hash Cond: (fg.nndb_feature_id = la.link_id)"
"  ->  Bitmap Heap Scan on feature_geometry fg  (cost=4.45..22.51 rows=26 width=8)"
"        Recheck Cond: (tile_num = 8613949)"
"        ->  Bitmap Index Scan on nx_featuregeometry_tilenum  (cost=0.00..4.45 rows=26 width=0)"
"              Index Cond: (tile_num = 8613949)"
"  ->  Hash  (cost=82.59..82.59 rows=13 width=8)"
"        ->  Nested Loop Semi Join  (cost=73.89..82.59 rows=13 width=8)"
"              Join Filter: (ah.feature_id = la.admin_id)"
"              ->  Seq Scan on admin_hierarchy ah  (cost=0.00..1.13 rows=13 width=4)"
"              ->  Materialize  (cost=73.89..99.95 rows=2606 width=12)"
"                    ->  Nested Loop  (cost=50.20..71.29 rows=2606 width=12)"
"                          ->  HashAggregate  (cost=50.20..50.21 rows=1 width=4)"
"                                ->  Hash Anti Join  (cost=48.95..50.19 rows=1 width=4)"
"                                      Hash Cond: (ah2.feature_id = fg2.nndb_feature_id)"
"                                      ->  Seq Scan on admin_hierarchy ah2  (cost=0.00..1.13 rows=13 width=4)"
"                                      ->  Hash  (cost=31.20..31.20 rows=1420 width=4)"
"                                            ->  Seq Scan on feature_geometry fg2  (cost=0.00..31.20 rows=1420
width=4)"
"                          ->  Index Scan using linkadmin_adminid on link_admin la  (cost=0.00..17.82 rows=261
width=8)"
"                                Index Cond: (la.admin_id = ah2.feature_id)"


The query result is empty.

However, using the query

SELECT ah.FEATURE_ID, FG.TILE_NUM FROM
nndb.admin_hierarchy ah
JOIN NNDB.LINK_ADMIN LA
ON ah.FEATURE_ID = LA.ADMIN_ID
JOIN psi.FEATURE_GEOMETRY FG
ON LA.LINK_ID = FG.NNDB_FEATURE_ID
WHERE  fg.tile_num = 8613949  and ah.feature_id in (170303063)


where 170303063 is one of the values returned by the subselect in the first query when run in isolation, you get a
non-emptyresult set, so there seems to be a problem with the subselect. 

If you need any other information to decide whether this is one of the known bugs or a new one, just let me know what
exactlyyou need. I can provide a backup of the three tables in question, which should be enough to isolate the problem. 

Best regards,
Harald



> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Gesendet: Mittwoch, 2. September 2009 20:09
> An: Wellmann, Harald
> Cc: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] Subselect problem
>
> "Wellmann, Harald" <harald.wellmann@harman.com> writes:
> > The problem occurs with PostgreSQL 8.4.0. I cannot
> reproduce it with
> > PostgreSQL 8.3.7.
>
> There are known bugs in 8.4.0 having to do with improperly
> exchanging the ordering of semijoins (IN joins) and other
> joins.  You haven't provided enough information to test
> whether your case is one of them.
> If you can try CVS branch tip or a recent nightly snapshot,
> there might still be enough time to do something about it for
> 8.4.1, if it isn't fixed already.
>
>             regards, tom lane
>

*******************************************
innovative systems GmbH Navigation-Multimedia
Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser
Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980

*******************************************
Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen. Wenn Sie nicht der richtige Adressat
sindoder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und loeschen Sie diese
Mail.Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. 
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have
receivedthis e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying,
disclosureor distribution of the contents in this e-mail is strictly forbidden. 
*******************************************