segfault on query - Mailing list pgsql-bugs

Hi,

I'd like to report a bug. I'm running pgsql-9.3.5 on debian wheezy
(amd64, up2date). Latest pgsql binary from pgdg wheezy repo.

With these tables:

CREATE TABLE offerable (
     id integer NOT NULL,
     offerable_reference character varying,
     name character varying,
     description character varying,
     can_be_ordered boolean NOT NULL,
     is_delivery_address_required boolean NOT NULL,
     parent_offerable_id integer,
     default_price_gross numeric(18,9),
     default_accounting_strategy_id integer NOT NULL
);


CREATE TABLE product (
     offerable_id integer NOT NULL,
     sku character varying NOT NULL
);


CREATE TABLE b_subscribable (
     offerable_id integer NOT NULL,
     service_level integer NOT NULL
);

CREATE TABLE a_subscribable (
     offerable_id integer NOT NULL,
     zenderpakket integer
);

ALTER TABLE ONLY offerable
     ADD CONSTRAINT offerable_pkey PRIMARY KEY (id);


ALTER TABLE ONLY product
     ADD CONSTRAINT product_pkey PRIMARY KEY (offerable_id);


ALTER TABLE ONLY b_subscribable
     ADD CONSTRAINT b_subscribable_pkey PRIMARY KEY (offerable_id);


ALTER TABLE ONLY a_subscribable
     ADD CONSTRAINT a_subscribable_pkey PRIMARY KEY (offerable_id);


The query below makes the server segfault:

SELECT "GroupBy1"."A1" AS "C1"
FROM ( SELECT Count(1) AS "A1"
    FROM    (SELECT
        "UnionAll8"."C1"
        FROM  (SELECT
            "UnionAll7"."C1"
            FROM  (select 1 as "C1" where 1 = 0)
                        AS "UnionAll7"
        UNION ALL
            SELECT
            "Extent9".offerable_id
            FROM b_subscribable AS "Extent9") AS "UnionAll8"
    UNION ALL
        SELECT
        "Extent10".offerable_id
        FROM a_subscribable AS "Extent10") AS "UnionAll9"
    INNER JOIN offerable AS "Extent11"
            ON "UnionAll9"."C1" = "Extent11".id
    LEFT OUTER JOIN product AS "Extent12"
            ON "UnionAll9"."C1" = "Extent12".offerable_id
)  AS "GroupBy1";

The tables do not need any records for this to happen (but it also
happens with records).

discovered so far:
- on other debian wheezy servers with 9.3.5, the problem persists.

- the problem does not occur under the following conditions:
    when running on 9.3.4 (debian testing repo)
    when running on 9.4.0 (pgdg repo)
    when you do *not* create the primary key constraints for the tables!
    when you leave out the INNER JOIN from the query.
    when you remove a UNION ALL like so:

SELECT
"GroupBy1"."A1" AS "C1"
FROM ( SELECT Count(1) AS "A1"
    FROM    (SELECT
        "UnionAll8"."C1"
        FROM  (select 1 as "C1" where 1 = 0) AS "UnionAll8"
    UNION ALL
        SELECT
        "Extent10".offerable_id
        FROM a_subscribable AS "Extent10") AS "UnionAll9"
    INNER JOIN offerable AS "Extent11" ON "UnionAll9"."C1" = "Extent11".id
    LEFT OUTER JOIN product AS "Extent12" ON "UnionAll9"."C1" =
"Extent12".offerable_id
)  AS "GroupBy1";

The query was originally much larger (with more UNION ALLs and more
tables, generated by some framework). This was the smallest set that
still produced the error.

Ironically, We just upgraded our 7.3 database to 9.3 (after 10 years of
outstanding duty) just to be able to run these queries (that worked on
9.3.4) :).

Anyway, thanks for crating such a fabulous piece of free software. And I
hope someone can fix this bug.

thanks,
Alex.