Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id AB713475973
	for <pgsql-sql@postgresql.org>; Sat,  3 Aug 2002 17:11:39 -0400 (EDT)
Received: from nirvana.dataparty.no (v097b.studby.ntnu.no [129.241.151.97])
	by postgresql.org (Postfix) with SMTP id A6277475946
	for <pgsql-sql@postgresql.org>; Sat,  3 Aug 2002 17:11:38 -0400 (EDT)
Received: (qmail 7761 invoked from network); 3 Aug 2002 21:11:40 -0000
Received: from v107b.studby.ntnu.no (HELO speed) (129.241.151.107)
	by v097b.studby.ntnu.no with SMTP; 3 Aug 2002 21:11:40 -0000
Message-ID: <036501c23b32$5c5149f0$6b97f181@speed>
From: "Kristian Eide" <kreide@online.no>
To: <pgsql-sql@postgresql.org>
Subject: Bug with dump/restore when using UNION and ORDER BY in views
Date: Sat, 3 Aug 2002 23:11:40 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/65
X-Sequence-Number: 8911

There seems to be a bug when dumping a view which is a UNION of selects, one
of which has an ORDER BY. A pair of paranthesises around the select is
missing, and this cause a subsequent restore to fail. This is quite annoying
as the backup file must be manually edited before it can be restored, and I
would really appreciate a solution in a future version of Postgre.

This problem is still present in 7.2.1.

Example:

CREATE TABLE t (id INT);
CREATE VIEW v AS (SELECT id FROM t AS t1 ORDER BY id) UNION SELECT id FROM t
AS t2;

After a dump it looks like this:

CREATE VIEW "v" as SELECT t1.id FROM t t1 ORDER BY t1.id UNION SELECT t2.id
FROM t t2;

Which is not accepted by postgre.


Regards,

Kristian