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