Re: [HACKERS] Creating a VIEW with a POINT column

Nick wrote:
> Nope, im not ordering by the POINT column. Heres an example...
>
> CREATE VIEW myview AS
> SELECT table1.title, table1.sorter, table1.xy FROM table1 UNION
> SELECT table2.title, table2.sorter, table2.xy FROM table2;

Hmm, the error seems to be coming from UNION. It's because Postgres
implements UNION by sorting both result sets merging them together.
Sample queries that also fail:

SELECT * FROM myview;
SELECT DISTINCT * FROM table1;
SELECT title, sorter, xy FROM table1 GROUP BY title, sorter, xy;

All three try to sort the table first, and as there's no comparision
operator for the POINT datatype, they fail. Which seems to be wrong - if
there is no comparision operator, you still can do DISTINCT, only less
efficiently.

The quick solution I'd propose is replacing UNION with UNION ALL. This
will not throw away duplicate entries present in both table1 and table2,
but if you can live with that, it will work. Remeber though, it changes
the semantic of that view, so think carefuly before doing that.

I guess some senior hacker should confirm, but I believe this is a bug.

Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers