From: Nathan Grange <nathang@actarg.com>
To: pgsql-sql@postgresql.org
Subject: Re: 9.0rc1 - query of view produces unexpected results
Date: Tue, 14 Sep 2010 10:56:44 -0600
Message-ID: <4C8FA94C.9030901@actarg.com> (raw)
In-Reply-To: <4C8C2D58.5090308@actarg.com>
References: <4C8C2D58.5090308@actarg.com>
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';
Attachments:
[text/plain] buildDemo.sql (1.6K, ../4C8FA94C.9030901@actarg.com/3-buildDemo.sql)
download | inline:
---------------------------------------------
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';
[text/plain] runDemo.sql (313B, ../4C8FA94C.9030901@actarg.com/4-runDemo.sql)
download | inline:
-- 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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nathang@actarg.com
Subject: Re: 9.0rc1 - query of view produces unexpected results
In-Reply-To: <4C8FA94C.9030901@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox