Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNION bug in 7.1.3?

UNION bug in 7.1.3?

From
Keary Suska
Date:
The following syntax, which works fine in 7.0.3, fails in 7.1.3:

SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
WHERE t1.col3 = t2.col4
UNION
SELECT t1.col1, NULL FROM table1 t1
WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
ORDER BY t1.col1

Fails with "ERROR:  Relation 't1' does not exist".

It seems to be in the "order by" clause. So, if I do:

SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
WHERE t1.col3 = t2.col4
UNION
SELECT t1.col1, NULL FROM table1 t1
WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
ORDER BY table1.col1

The above error goes away, but is replaced by "ERROR:  ORDER BY on a
UNION/INTERSECT/EXCEPT result must be on one of the result columns". Is this
a bug? It doesn't even work when using full table names instead of aliases.

Of course, I can do an outer join without any problem, but I have other
cases where I am doing unions, the above just seems to be the most
explicative example.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

Re: UNION bug in 7.1.3?

From
Stephan Szabo
Date:
On Mon, 22 Oct 2001, Keary Suska wrote:

> The following syntax, which works fine in 7.0.3, fails in 7.1.3:
>
> SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
> WHERE t1.col3 = t2.col4
> UNION
> SELECT t1.col1, NULL FROM table1 t1
> WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
> ORDER BY t1.col1
>
> Fails with "ERROR:  Relation 't1' does not exist".
>
> It seems to be in the "order by" clause. So, if I do:
>
> SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
> WHERE t1.col3 = t2.col4
> UNION
> SELECT t1.col1, NULL FROM table1 t1
> WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
> ORDER BY table1.col1
>
> The above error goes away, but is replaced by "ERROR:  ORDER BY on a
> UNION/INTERSECT/EXCEPT result must be on one of the result columns". Is this
> a bug? It doesn't even work when using full table names instead of aliases.

I think an ORDER BY col1 will do what you want, since ISTM col1 is the
column name in the query expression of the select union select.

Re: UNION bug in 7.1.3?

From
Herbert Liechti
Date:
On Mon, 22 Oct 2001, Keary Suska wrote:

 > The following syntax, which works fine in 7.0.3, fails in 7.1.3:
 >
 > SELECT t1.col1, t2.col2 FROM table1 t1, table2 t2
 > WHERE t1.col3 = t2.col4
 > UNION
 > SELECT t1.col1, NULL FROM table1 t1
 > WHERE t1.col3 NOT IN (SELECT t2.col2 FROM table2 t2)
 > ORDER BY t1.col1
 >
 >
 > The above error goes away, but is replaced by "ERROR:  ORDER BY on a
 > UNION/INTERSECT/EXCEPT result must be on one of the result columns".
Is this
 > a bug? It doesn't even work when using full table names instead of
aliases.

This is not a bug. Use aliases instaed or use the position number of the
column in the order by clause. Ex. ORDER BY 1

Regards
Herbie
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                                  http://www.thinx.ch
ThinX networked business services    Adlergasse 5, CH-4500 Solothurn
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~