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.