9.0_alpha4 Planner Bug

From: Oleg K <windspirit(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: 9.0_alpha4 Planner Bug
Date: 2010-03-21 21:25:36
Message-ID: 4BA68ED0.4060906@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

My OS is Gentoo Linux 2.6.32.10 x86_64

Postgre:# select version();
PostgreSQL 9.0alpha4 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (Gentoo 4.3.4 p1.0, pie-10.1.5) 4.3.4, 64-bit

There is a regression, in some query that was working fine in Postgre
8.4.3 and became broken after upgrade

the details are here http://pastebin.ca/1848469

basically, i have 2 tables cjw.service and cjw.company_to_service
when i run query that left-joins them and produces a boolean constant
named "linked" on successful join

SELECT
service_name AS name
-- , com_serv.service::bool AS linked
, linked AS linked
FROM
cjw.service
LEFT JOIN (
SELECT
service
, true as linked
FROM
cjw.company_to_service
WHERE
company = 3
) AS com_serv USING (service)
ORDER BY
service_name

so when I select that constant by it's name - the planner completely
ignores join and returns "true" for every row, even though that is wrong
and that row could not be joined

and if i select, a real value from joined part - the planner performs as
expected and returns valid result.

please checkout pastebin url http://pastebin.ca/1848469
i included descriptions of tables and ANALYZE of queries there.
as well as partial query results

please note that first query shows WRONG result and second one is OK.

Regards.

Oleg.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Gudala, Sridhar (GE EntSol, Intelligent Platforms) 2010-03-22 12:01:35 Noticed a Bug with stored procedures
Previous Message Tom Lane 2010-03-20 15:30:27 Re: BUG #5385: gd disallows agg function in subselect of update