Trouble with an outer join - Mailing list pgsql-novice

I want to run an outer join query on two tables so that the tags from
one will always appear even if the other table is lacking those
attributes.     Unfortunately, while the join seems to work there is no
filtering taking place, causing a certain amount of strife.

The two tables are as follows:

            Table "ethereal.tag"
  Column  |         Type          |                  Modifiers
---------+-----------------------+----------------------------------------------
  tagname | character varying(25) | not null
  tagtype | character varying(15) | not null default 'system'
  tagdata | text                  |
Indexes:
     "pktag" PRIMARY KEY, btree (tagname, tagtype)

            Table "ethereal.realmdesign"
     Column     |         Type          | Modifiers
---------------+-----------------------+-----------
  realmname     | character varying(30) | not null
  tagname       | character varying(20) | not null
  designcontent | text                  |
Indexes:
     "pkrealmdesign" PRIMARY KEY, btree (realmname, tagname)


Obviously 'TagName' is the field which matches both.   These tables are
not linked together and the removal of an entry from Tag will cause the
removal from RealmDesign through code.

The query is as follows.  Note that there is a lot of filtering as to
prevent too many rows which are not required from being dealt with:

SELECT
   t.TagName       AS "TagName",
   t.TagType       AS "TagType",
   d.RealmName     AS "RealmName"
FROM ethereal.Tag t
LEFT OUTER JOIN ethereal.RealmDesign d
  ON (t.TagName=d.TagName
AND t.TagType='template'
AND (t.TagName LIKE 'Realm%'
  OR  t.TagName LIKE 'Offline%')
AND d.RealmName='Horror')
ORDER BY t.TagName;

The EXPLAIN ANALYZE seems to indicate that the filtering takes place:

  Merge Left Join  (cost=122.55..127.57 rows=946 width=35) (actual
time=10.633..13.591 rows=946 loops=1)
    Merge Cond: ("outer"."?column3?" = "inner"."?column3?")
    Join Filter: ((("outer".tagtype)::text = 'template'::text) AND
(("outer"."?column3?" ~~ 'Realm%'::text) OR ("outer"."?column3?" ~~
'Offline%'::text)))
    ->  Sort  (cost=100.22..102.58 rows=946 width=25) (actual
time=10.479..11.405 rows=946 loops=1)
          Sort Key: (t.tagname)::text
          ->  Seq Scan on tag t  (cost=0.00..53.46 rows=946 width=25)
(actual time=0.011..1.788 rows=946 loops=1)
    ->  Sort  (cost=22.33..22.36 rows=11 width=26) (actual
time=0.134..0.138 rows=11 loops=1)
          Sort Key: (d.tagname)::text
          ->  Seq Scan on realmdesign d  (cost=0.00..22.14 rows=11
width=26) (actual time=0.026..0.091 rows=11 loops=1)
                Filter: ((realmname)::text = 'Horror'::text)
  Total runtime: 14.418 ms


The problem is however, that the filtering does not take place.  In fact
it outright ignores it and returns every entry from the Tag table no
matter what I do to change the query.  Sample limited output is below:


         TagName        |    TagType    | RealmName
-----------------------+---------------+-----------
  OptPrivacy            | contrib       |
  OptPrivacy            | configuration |
  OptRating             | contrib       |
  OptScope              | configuration |
  OptSort               | contrib       |
  OptSrchField          | gallery       |
  OptSrchSort           | gallery       |
  OptSrchType           | gallery       |
  OptWeekdays           | gallery       |
  RealmBanner           | template      |
  RealmDice             | template      | Horror
  RealmFooter           | template      |
  RealmFrameAutoscroll  | template      | Horror
  RealmFrameNormal      | template      | Horror
  RealmHeader           | template      | Horror
  RealmInfo             | template      |
  RealmJavascript       | template      |
  RealmNotice           | template      | Horror
  RealmPanel            | template      | Horror
  RealmPrivate          | template      | Horror
  RealmRefresh          | template      |
  RealmRemote           | template      | Horror
  RealmSeperator        | template      | Horror
  RealmSupp             | template      |
  RealmWarning          | template      | Horror
  RealmZoom             | template      | Horror
  SetAbuse              | user          |

 From the output its pretty clear that the first 10 should have been
omitted for more then one reason.   However they appear every time and
in order to compensate for this, I have the script skip through unneeded
entries manually.

So what exactly am I doing wrong?

    Martin Foster
    martin@ethereal-realms.org