Dariusz Knociñski (dknoto@next.com.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
