UNION bug in 7.1.3?

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.

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~