pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed9.0rc1 - query of view produces unexpected results
4+ messages / 3 participants
[nested] [flat]
* 9.0rc1 - query of view produces unexpected results
@ 2010-09-12 01:31 Nathan Grange <nathang@actarg.com>
0 siblings, 2 replies; 4+ messages in thread
From: Nathan Grange @ 2010-09-12 01:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 9.0rc1 - query of view produces unexpected results
@ 2010-09-14 03:58 Alvaro Herrera <alvherre@commandprompt.com>
parent: Nathan Grange <nathang@actarg.com>
1 sibling, 0 replies; 4+ messages in thread
From: Alvaro Herrera @ 2010-09-14 03:58 UTC (permalink / raw)
To: Nathan Grange <nathang@actarg.com>; +Cc: pgsql-sql
Excerpts from Nathan Grange's message of sáb sep 11 21:31:04 -0400 2010:
> 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.
> 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.
You didn't attach the files, so no one can study your problem.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 9.0rc1 - query of view produces unexpected results
@ 2010-09-14 16:56 Nathan Grange <nathang@actarg.com>
parent: Nathan Grange <nathang@actarg.com>
1 sibling, 1 reply; 4+ messages in thread
From: Nathan Grange @ 2010-09-14 16:56 UTC (permalink / raw)
To: pgsql-sql
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: 9.0rc1 - query of view produces unexpected results
@ 2010-09-14 17:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nathan Grange <nathang@actarg.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-09-14 17:51 UTC (permalink / raw)
To: Nathan Grange <nathang@actarg.com>; +Cc: pgsql-sql
Nathan Grange <nathang@actarg.com> writes:
>> Or if this is a bug with 9.0, what actions do I take to make the
>> PostgreSQL team awares?
I think you already did ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-09-14 17:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-12 01:31 9.0rc1 - query of view produces unexpected results Nathan Grange <nathang@actarg.com>
2010-09-14 03:58 ` Alvaro Herrera <alvherre@commandprompt.com>
2010-09-14 16:56 ` Nathan Grange <nathang@actarg.com>
2010-09-14 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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