Thread: 9.0rc1 - query of view produces unexpected results
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 unexpectedresults when i query a specific view. <br /><br /> I can only reproduce the unexpected results with a very specificset of tables, views, and relationships.<br /> The bad results are only manifest in 9.0 (beta and rc1). Version 8.4.4produces the expected results.<br /><br /> To sum up the issue ...<br /> 1. View register_orders_view contains only1 row of data ...<br /><br /><font color="#009900"><i>ttype | ropnum | shop_name <br /> -------+--------+-----------<br/> or | 30129 | District1</i></font><br /><br /> 2. The query ...<br /><i><br /><fontcolor="#009900">select * from register_orders_view where ttype='z'</font></i><br /><br /> ... should returnno rows, however all rows are returned. <br /><br /><br /> Included are a buildDemo.sql file that will create the necessarytables 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 wouldbe 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 />
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
Doh!!
Files attached this time.
On 09/11/2010 07:31 PM, Nathan Grange wrote:
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
Attachment
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