BUG #4106: WHERE - clause in view works only sometimes - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4106
Logged by:          Alexander Strotmann
Email address:      kiruu@gmx.de
PostgreSQL version: 8.2
Operating system:   Windows XP / Windows 2003 Server
Description:        WHERE - clause in view works only sometimes
Details:

Hello,

we have this table:

CREATE TABLE rollenhistorie
(
  pnr integer NOT NULL,
  rolle character varying NOT NULL,
  seit timestamp without time zone NOT NULL,
  bis timestamp without time zone,
  CONSTRAINT rollenhistorie_pkey PRIMARY KEY (pnr, seit)
)
WITHOUT OIDS;

and this view:

CREATE OR REPLACE VIEW benutzer_mit_rolle_vw AS
 SELECT benutzer_ohne_alles_vw.pnr, benutzer_ohne_alles_vw.vorname,
benutzer_ohne_alles_vw.nachname, benutzer_ohne_alles_vw.nutzerkennung,
benutzer_ohne_alles_vw.passwort, benutzer_ohne_alles_vw.plz,
benutzer_ohne_alles_vw.ort, benutzer_ohne_alles_vw.strasse,
benutzer_ohne_alles_vw.hausnummer, benutzer_ohne_alles_vw.telefon_dienst,
benutzer_ohne_alles_vw.email, benutzer_ohne_alles_vw.anzahl_tage_erinnerung,
benutzer_ohne_alles_vw.gebietsschutz, rollenhistorie.rolle,
rollenhistorie.seit, rollenhistorie.bis
   FROM benutzer_ohne_alles_vw
NATURAL JOIN rollenhistorie
  WHERE rollenhistorie.bis IS NULL;

So the view joins the upper table with another view and filters the sets in
'rollenhistorie' by taking only the sets where 'bis' is NULL.
By questioning this view with this function:

CREATE OR REPLACE FUNCTION get_user_and_role_plpgsql()
  RETURNS SETOF benutzer_mit_rolle_vw AS
$BODY$
DECLARE
 rec RECORD;
BEGIN
 FOR rec IN Select * from benutzer_mit_rolle_vw
 LOOP
  RETURN NEXT rec;
 END LOOP;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

we get sometimes results where 'bis' is not NULL! But it is not
deterministic because in about 80% of request times the result is correct.
The work-around for us is putting the 'WHERE bis IS NULL' in the
function...

Kind regards

Alexander Strotmann