Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id E4ADA1337BC6
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Sep 2010 13:57:03 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 39975-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Sep 2010 16:56:56 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from tao.actarg.com (actarg.com [204.130.245.1])
	by mail.postgresql.org (Postfix) with ESMTP id 765591337BE9
	for <pgsql-sql@postgresql.org>; Tue, 14 Sep 2010 13:56:56 -0300 (ADT)
Received: from tak.actarg.com (tak.actarg.com [192.168.4.85])
	by tao.actarg.com (8.14.2/8.14.2) with ESMTP id o8EGusEM019169
	for <pgsql-sql@postgresql.org>; Tue, 14 Sep 2010 10:56:54 -0600
Received: from nb160.actarg.com (nb160.actarg.com [192.168.7.160])
	(authenticated bits=0)
	by tak.actarg.com (8.14.2/8.14.2) with ESMTP id o8EGuilL025593
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-sql@postgresql.org>; Tue, 14 Sep 2010 10:56:54 -0600
Message-ID: <4C8FA94C.9030901@actarg.com>
Date: Tue, 14 Sep 2010 10:56:44 -0600
From: Nathan Grange <nathang@actarg.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.8) Gecko/20100806 Fedora/3.1.2-1.fc13 Thunderbird/3.1.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: 9.0rc1 - query of view produces unexpected results
References: <4C8C2D58.5090308@actarg.com>
In-Reply-To: <4C8C2D58.5090308@actarg.com>
Content-Type: multipart/mixed; boundary="------------090802090505080300090401"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201009/36
X-Sequence-Number: 34567

This is a multi-part message in MIME format.
--------------090802090505080300090401
Content-Type: multipart/alternative;
	boundary="------------090800020300090408030102"


--------------090800020300090408030102
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

  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
>


--------------090800020300090408030102
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#ffffff">
    Doh!!&nbsp; <br>
    Files attached this time.<br>
    <br>
    On 09/11/2010 07:31 PM, Nathan Grange wrote:
    <blockquote cite="mid:4C8C2D58.5090308@actarg.com" type="cite">
      <meta http-equiv="content-type" content="text/html;
        charset=ISO-8859-1">
      Hello list, <br>
      <br>
      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. <br>
      <br>
      I can only reproduce the unexpected results with a very specific
      set of tables, views, and relationships.<br>
      The bad results are only manifest in 9.0 (beta and rc1). Version
      8.4.4 produces the expected results.<br>
      <br>
      To sum up the issue ...<br>
      &nbsp;&nbsp;&nbsp; 1. View register_orders_view contains only 1 row of data ...<br>
      <br>
      <font color="#009900"><i>ttype | ropnum | shop_name <br>
          -------+--------+-----------<br>
          &nbsp; or&nbsp;&nbsp;&nbsp; |&nbsp; 30129 | District1</i></font><br>
      <br>
      &nbsp;&nbsp;&nbsp; 2. The query ...<br>
      <i><br>
        <font color="#009900">select * from register_orders_view where
          ttype='z'</font></i><br>
      <br>
      &nbsp; &nbsp; &nbsp;&nbsp; ... should return no rows, however all rows are returned. <br>
      <br>
      <br>
      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. <br>
      <br>
      If this is a valid result, can someone please help me understand
      why?<br>
      If this is a case of "not the best" architecture a explanation
      would be greatly apprecitated.<br>
      Or if this is a bug with 9.0, what actions do I take to make the
      PostgreSQL team awares?<br>
      <br>
      nate<br>
      <br>
    </blockquote>
    <br>
  </body>
</html>

--------------090800020300090408030102--

--------------090802090505080300090401
Content-Type: text/plain;
 name="buildDemo.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="buildDemo.sql"

---------------------------------------------

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';


--------------090802090505080300090401
Content-Type: text/plain;
 name="runDemo.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="runDemo.sql"


-- 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';


--------------090802090505080300090401--