Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group