From nathang@actarg.com Sun Sep  12 02:24:26 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 2B55163252D
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sat, 11 Sep 2010 23:24:26 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 69173-08 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Sun, 12 Sep 2010 02:24:16 +0000 (UTC)
X-Greylist: delayed 00:53:08.886154 by SQLgrey-1.7.6
Received: from tao.actarg.com (actarg.com [204.130.245.1])
	by mail.postgresql.org (Postfix) with ESMTP id 5BD5D63243D
	for <pgsql-sql@postgresql.org>; Sat, 11 Sep 2010 23:24:18 -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 o8C1V6cn028441
	for <pgsql-sql@postgresql.org>; Sat, 11 Sep 2010 19:31:06 -0600
Received: from nb160.actarg.com (c-98-202-195-78.hsd1.ut.comcast.net
	[98.202.195.78]) (authenticated bits=0)
	by tak.actarg.com (8.14.2/8.14.2) with ESMTP id o8C1V5B3028672
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO)
	for <pgsql-sql@postgresql.org>; Sat, 11 Sep 2010 19:31:06 -0600
Message-ID: <4C8C2D58.5090308@actarg.com>
Date: Sat, 11 Sep 2010 19:31:04 -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: 9.0rc1 - query of view produces unexpected results
Content-Type: multipart/alternative;
	boundary="------------090805010309030303000000"
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/31
X-Sequence-Number: 34562

This is a multi-part message in MIME format.
--------------090805010309030303000000
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

  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


--------------090805010309030303000000
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 http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body text="#000000" bgcolor="#ffffff">
    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>
  </body>
</html>

--------------090805010309030303000000--



From alvherre@commandprompt.com Tue Sep  14 03:59:21 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id E97531337BC1
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Sep 2010 00:59:21 -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 92005-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Sep 2010 03:59:14 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from spinlock.commandprompt.com (host-215.commandprompt.net
	[207.173.203.215])
	by mail.postgresql.org (Postfix) with ESMTP id CEBA91335C08
	for <pgsql-sql@postgresql.org>; Tue, 14 Sep 2010 00:59:13 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by spinlock.commandprompt.com (Postfix) with ESMTP id 7E2D8348013;
	Mon, 13 Sep 2010 20:59:03 -0700 (PDT)
X-Virus-Scanned: Debian amavisd-new at commandprompt.com
Received: from spinlock.commandprompt.com ([127.0.0.1])
	by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id CmHQ1Ba5wncm; Mon, 13 Sep 2010 20:58:57 -0700 (PDT)
Received: from perhan.alvh.no-ip.org (200-126-90-175.bk6-dsl.surnet.cl
	[200.126.90.175])
	by spinlock.commandprompt.com (Postfix) with ESMTPSA id 9EED4348011;
	Mon, 13 Sep 2010 20:58:56 -0700 (PDT)
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id C577C6E486; Mon, 13 Sep 2010 23:58:51 -0400 (CLT)
Content-Type: text/plain; charset=UTF-8
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: 9.0rc1 - query of view produces unexpected results
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Nathan Grange <nathang@actarg.com>
In-reply-to: <4C8C2D58.5090308@actarg.com>
References: <4C8C2D58.5090308@actarg.com>
Date: Mon, 13 Sep 2010 23:58:51 -0400
Message-Id: <1284436684-sup-6808@alvh.no-ip.org>
User-Agent: Sup/git
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201009/34
X-Sequence-Number: 34565

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



From nathang@actarg.com Tue Sep  14 16:57:03 2010
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--



From tgl@sss.pgh.pa.us Tue Sep  14 17:52:17 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id EC3EC1337DCC
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Sep 2010 14:52:17 -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 57402-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 14 Sep 2010 17:52:11 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id A34D11337BD7
	for <pgsql-sql@postgresql.org>; Tue, 14 Sep 2010 14:51:40 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o8EHpcMf009151;
	Tue, 14 Sep 2010 13:51:38 -0400 (EDT)
To: Nathan Grange <nathang@actarg.com>
cc: pgsql-sql@postgresql.org
Subject: Re: 9.0rc1 - query of view produces unexpected results 
In-reply-to: <4C8FA94C.9030901@actarg.com> 
References: <4C8C2D58.5090308@actarg.com> <4C8FA94C.9030901@actarg.com>
Comments: In-reply-to Nathan Grange <nathang@actarg.com>
	message dated "Tue, 14 Sep 2010 10:56:44 -0600"
Date: Tue, 14 Sep 2010 13:51:38 -0400
Message-ID: <9150.1284486698@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201009/37
X-Sequence-Number: 34568

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