Doh!!
Files attached this time.
On 09/11/2010 07:31 PM, Nathan Grange wrote:
Hello list,
I don't know if it's me, or maybe even the way I designed these
dependencies, but I'm getting unexpected results when i query a
specific view.
I can only reproduce the unexpected results with a very specific set
of tables, views, and relationships.
The bad results are only manifest in 9.0 (beta and rc1). Version 8.4.4
produces the expected results.
To sum up the issue ...
1. View register_orders_view contains only 1 row of data ...
/ttype | ropnum | shop_name
-------+--------+-----------
or | 30129 | District1/
2. The query ...
/
select * from register_orders_view where ttype='z'/
... should return no rows, however all rows are returned.
Included are a buildDemo.sql file that will create the necessary
tables and views, and a runDemo.sql to demonstrate the query from #2
above.
If this is a valid result, can someone please help me understand why?
If this is a case of "not the best" architecture a explanation would
be greatly apprecitated.
Or if this is a bug with 9.0, what actions do I take to make the
PostgreSQL team awares?
nate
---------------------------------------------
CREATE TABLE territory (
terr_id integer NOT NULL PRIMARY KEY,
name character varying NOT NULL
);
COPY territory (terr_id, name) FROM stdin;
40 Foreign
\.
------------------------------------------------
CREATE TABLE org (
org_id integer NOT NULL PRIMARY KEY,
org_name character varying,
terr_id integer REFERENCES territory(terr_id)
);
COPY org (org_id, org_name, terr_id) FROM stdin;
3152 District1 40
\.
---------------------------------------------------
CREATE TABLE orders (
ordnum integer NOT NULL PRIMARY KEY,
org_id integer REFERENCES org(org_id)
);
COPY orders (ordnum, org_id) FROM stdin;
30129 3152
\.
---------------------------------------------------
CREATE TABLE register (
ttype character(2) NOT NULL,
ropnum integer NOT NULL,
CONSTRAINT register_ttype_check CHECK ((ttype = ANY (ARRAY['ad'::bpchar,
'bf'::bpchar, 'or'::bpchar, 'po'::bpchar, 'pt'::bpchar, 'rq'::bpchar,
'wm'::bpchar, 'wt'::bpchar])))
);
COPY register (ttype, ropnum) FROM stdin;
or 30129
\.
--------------------------------------------------
CREATE VIEW org_view AS
SELECT co.org_id
,co.org_name
,ct.name as tername
from org co
left join territory ct on ct.terr_id = co.terr_id;
------------------------------------------------------
CREATE VIEW register_orders_view AS
SELECT m.ttype
, m.ropnum
, co.org_name AS shop_name
FROM register m
JOIN orders oh ON ((oh.ordnum = m.ropnum))
JOIN org_view co ON ((co.org_id = oh.org_id))
WHERE m.ttype = 'or';
-- The following query shows that there are 3 rows of data "in" in the view.
select * from register_orders_view;
-- This next query shows the problem. Although there are no rows where the
-- value for the ttype column is z, there are still 3 rows returned.
select * from register_orders_view where ttype='z';
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
