The following bug has been logged online:
Bug reference: 4106
Logged by: Alexander Strotmann
Email address: kiruu@[EMAIL PROTECTED]
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
|
