Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3
Date: 2002-12-17 13:02:27
Message-ID: 20021217130227.BE0AC47658C@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Dariusz Knociski (dknoto(at)next(dot)com(dot)pl) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
pg_restore bug on views with union, PostgreSQL 7.2.3

Long Description
I have problem with restoring views with union and without
attribute names in select, without union all works OK because
pg_dump for "sum(attr)" generate "sum(attr) as attr", with union generate "sum(attr) as sum".

Sample Code
I have created view with query in PostgreSQL 7.2.3 :

CREATE VIEW
"smnad_200212"
(
nadawca,
licznik,
kwota
)
AS
(
SELECT
o.nadawca,
sum(o.count),
(sum(o.kwota))::numeric(15,2)
FROM
oplaty_bif_nad_yyyymmdd o
WHERE
(text(o.data) ~~ '2002-12-%'::text)
GROUP BY o.nadawca
)
UNION
(
SELECT
'Razem:' AS nadawca,
sum(o.count),
(sum(o.kwota))::numeric(15,2)
FROM
oplaty_bif_nad_yyyymmdd o
WHERE
(text(o.data) ~~ '2002-12-%'::text)
);

and then I have made backup with pg_dump.
pg_dump created query:

CREATE VIEW
"smnad_200212"
as
(
(
SELECT
o.nadawca,
sum(o.count) AS sum, --
(sum(o.kwota))::numeric(15,2) AS sum -- Error
FROM
oplaty_bif_nad_yyyymmdd o
WHERE
(text(o.data) ~~ '2002-12-%'::text)
GROUP BY o.nadawca
)
UNION
(
SELECT
'Razem:' AS nadawca,
sum(o.count) AS sum, --
(sum(o.kwota))::numeric(15,2) AS sum -- Error
FROM
oplaty_bif_nad_yyyymmdd o
WHERE
(text(o.data) ~~ '2002-12-%'::text)
)
);

In these query have very important SQL bug, attributes "sum" duplicated.

No file was uploaded with this report

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2002-12-17 13:05:08 Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3
Previous Message Tom Lane 2002-12-16 23:53:56 Re: odd behavior: function not atomic/not seeing it's own updates/not acting serializable nor read committed