7.2.1: pg_dump of UNIONed VIEWs broken

Ian Morgan <imorgan@webcon.net> writes:

The SQL for CREATE VIEW as output by pg_dump generates an error, because the
appropriate nesting of SELECTs with parentheses is missing:

Hmm. You shouldn't *need* parentheses in most cases ...

CREATE VIEW "agents" as (
(
SELECT DISTINCT
employees.contact_id
FROM
employees
ORDER BY
employees.contact_id
)
UNION
(
SELECT DISTINCT
contractors.contact_id
FROM
contractors
ORDER BY
contractors.contact_id
)
);

although I suppose this is a counterexample. Would it help any to point
out that ORDER BY inside an arm of a UNION is a complete waste of
cycles? That's probably why it didn't occur to anyone to test this.

BTW, the SELECT DISTINCTs are also a waste of cycles, since UNION will
do that anyway.

regards, tom lane

On Mon, 22 Apr 2002, Tom Lane wrote:

Ian Morgan <imorgan@webcon.net> writes:

The SQL for CREATE VIEW as output by pg_dump generates an error, because the
appropriate nesting of SELECTs with parentheses is missing:

Hmm. You shouldn't *need* parentheses in most cases ...

CREATE VIEW "agents" as (
(
SELECT DISTINCT
employees.contact_id
FROM
employees
ORDER BY
employees.contact_id
)
UNION
(
SELECT DISTINCT
contractors.contact_id
FROM
contractors
ORDER BY
contractors.contact_id
)
);

although I suppose this is a counterexample. Would it help any to point
out that ORDER BY inside an arm of a UNION is a complete waste of
cycles? That's probably why it didn't occur to anyone to test this.

BTW, the SELECT DISTINCTs are also a waste of cycles, since UNION will
do that anyway.

Interesting observations. I knew the ORDER BYs were irrelevant, but hadn't
gotten around to removing them. The DISTINCTs, on the other hand were a
throwback to some old queries, and I completely missed that the UNION would
do that for me.. hehe.. oops.

Anyhow, rewriting the select:

Works:

SELECT employees.contact_id FROM employees
UNION
SELECT contractors.contact_id FROM contractors;

Does not work:

SELECT employees.contact_id FROM employees ORDER BY employees.contact_id
UNION
SELECT contractors.contact_id FROM contractors ORDER BY contractors.contact_id;

Even if adding ORDER BY to a UNION is really sub-optimal SQL, shouldn't
pg_dump (and even psql's \d <viewname>) produce SQL that actually works for
any query that was originally accepted when creating the VIEW?
Otherwise, maybe having ORDER BY in a VIEW's SELECT should be made illegal?

Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------

Ian Morgan <imorgan@webcon.net> writes:

Even if adding ORDER BY to a UNION is really sub-optimal SQL, shouldn't
pg_dump (and even psql's \d <viewname>) produce SQL that actually
works

I didn't say it wasn't a bug ;-) ... just pointing out that fixing it
doesn't seem very high priority. I'll do something about it next time
I have occasion to touch ruleutils.c.

regards, tom lane

Can someone phrase a TODO item for me?

---------------------------------------------------------------------------

Ian Morgan wrote:

PostgreSQL 7.2.1's pg_dump prduces bad SQL for VIEW's.

The SQL for CREATE VIEW as output by pg_dump generates an error, because the
appropriate nesting of SELECTs with parentheses is missing:
ERROR: parser: parse error at or near "SELECT"

Original SQL:

CREATE VIEW "agents" as (
(
SELECT DISTINCT
employees.contact_id
FROM
employees
ORDER BY
employees.contact_id
)
UNION
(
SELECT DISTINCT
contractors.contact_id
FROM
contractors
ORDER BY
contractors.contact_id
)
);

As output by pg_dump:

CREATE VIEW "agents" as SELECT DISTINCT employees.contact_id FROM employees
ORDER BY employees.contact_id UNION SELECT DISTINCT contractors.contact_id
FROM contractors ORDER BY contractors.contact_id;

As you can see, the nesting is entirely lost.

Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Tue, 23 Apr 2002, Bruce Momjian wrote:

Can someone phrase a TODO item for me?

How about:
Fix pg_get_viewdef() to output fully nested selects

Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Can someone phrase a TODO item for me?

It's already done (not committed yet though).

regards, tom lane