Thread: ORDER BY col is NULL in UNION causes error?

ORDER BY col is NULL in UNION causes error?

From

Mike Benoit

Date:

27 December 2006, 00:05:54

I asked about this on IRC and the helpful people online at the time
didn't seem to have an explanation.

Works
---------------------
select * from income_tax_rate_us order by state is null;

Works
---------------------
select * from income_tax_rate_us UNION select * from income_tax_rate_us
order by state;

Fails
---------------------
select * from income_tax_rate_us UNION select * from income_tax_rate_us
order by state is null;
ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns

I'm running: PostgreSQL 8.1.5 on x86_64-mandriva-linux-gnu, compiled by
GCC x86_64-mandriva-linux-gnu-gcc (GCC) 4.1.1 20060724 (prerelease)
(4.1.1-3mdk)


--
Mike Benoit <ipso@snappymail.ca>

Attachment

signature.asc

Re: ORDER BY col is NULL in UNION causes error?

From

Michael Glaesemann

Date:

27 December 2006, 00:39:29

On Dec 26, 2006, at 18:39 , Mike Benoit wrote:

> Fails
> ---------------------
> select * from income_tax_rate_us UNION select * from
> income_tax_rate_us
> order by state is null;
> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> the result columns

Even though state is a column in both tables, the order by is using
an expression, rather than a column.

Should work:

SELECT *, state IS NULL AS state_is_null
FROM income_tax_rate_us
UNION
SELECT *, state IS NULL AS state_is_null
FROM income_tax_rate_us
ORDER BY state_is_null

This should also work:

SELECT *
FROM (
    SELECT *
    FROM income_tax_rate_us
    UNION
    SELECT *
    FROM income_tax_rate_us
    ) union_result
ORDER BY state IS NULL

I'm not sure of the underlying reasons why your query doesn't work,
but give these a shot.

Michael Glaesemann
grzm seespotcode net

Re: ORDER BY col is NULL in UNION causes error?

From

Tom Lane

Date:

27 December 2006, 02:26:10

Michael Glaesemann <grzm@seespotcode.net> writes:
> On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
>> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
>> the result columns

> Even though state is a column in both tables, the order by is using
> an expression, rather than a column.
> ...
> I'm not sure of the underlying reasons why your query doesn't work,
> but give these a shot.

There are some implementation reasons for not supporting expressions
computed on a UNION result without an intervening sub-SELECT.  It's too
late at night for me to recall exactly what they are :-( --- one is that
an Append plan node doesn't do any expression evaluation, but I think
there are some more-subtle issues too.  Suffice it to say that we could
support this if we wanted to throw enough effort at it, but so far other
problems have seemed more pressing.

In the meantime, it seems like the above-quoted error message is not
clear enough, since Mike failed to get the point that "the ORDER BY
item has to be just a simple column name of the UNION output".  Anyone
have a suggestion for better wording?

            regards, tom lane

Re: ORDER BY col is NULL in UNION causes error?

From

Bruce Momjian

Date:

11 January 2007, 17:44:32

Tom Lane wrote:
> Michael Glaesemann <grzm@seespotcode.net> writes:
> > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> >> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> >> the result columns
>
> > Even though state is a column in both tables, the order by is using
> > an expression, rather than a column.
> > ...
> > I'm not sure of the underlying reasons why your query doesn't work,
> > but give these a shot.
>
> There are some implementation reasons for not supporting expressions
> computed on a UNION result without an intervening sub-SELECT.  It's too
> late at night for me to recall exactly what they are :-( --- one is that
> an Append plan node doesn't do any expression evaluation, but I think
> there are some more-subtle issues too.  Suffice it to say that we could
> support this if we wanted to throw enough effort at it, but so far other
> problems have seemed more pressing.
>
> In the meantime, it seems like the above-quoted error message is not
> clear enough, since Mike failed to get the point that "the ORDER BY
> item has to be just a simple column name of the UNION output".  Anyone
> have a suggestion for better wording?

I have updated the wording from

  "ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns")));

to:

  "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns")));

The 'match' wording might help, rather then 'use'.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: ORDER BY col is NULL in UNION causes error?

From

Mike Benoit

Date:

11 January 2007, 17:59:54

On Thu, 2007-01-11 at 13:44 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Michael Glaesemann <grzm@seespotcode.net> writes:
> > > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> > >> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> > >> the result columns
> >
> > > Even though state is a column in both tables, the order by is using
> > > an expression, rather than a column.
> > > ...
> > > I'm not sure of the underlying reasons why your query doesn't work,
> > > but give these a shot.
> >
> > There are some implementation reasons for not supporting expressions
> > computed on a UNION result without an intervening sub-SELECT.  It's too
> > late at night for me to recall exactly what they are :-( --- one is that
> > an Append plan node doesn't do any expression evaluation, but I think
> > there are some more-subtle issues too.  Suffice it to say that we could
> > support this if we wanted to throw enough effort at it, but so far other
> > problems have seemed more pressing.
> >
> > In the meantime, it seems like the above-quoted error message is not
> > clear enough, since Mike failed to get the point that "the ORDER BY
> > item has to be just a simple column name of the UNION output".  Anyone
> > have a suggestion for better wording?
>
> I have updated the wording from
>
>   "ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns")));
>
> to:
>
>   "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns")));
>
> The 'match' wording might help, rather then 'use'.

That helps some, but I'm sure it could be even more clear.

The main issue is that you can't order by an expression computed by
unions, correct? So couldn't the error message by something like:

"ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result
columns and not be an expression")));

>
--
Mike Benoit <ipso@snappymail.ca>

Attachment

signature.asc

Re: ORDER BY col is NULL in UNION causes error?

From

Bruce Momjian

Date:

11 January 2007, 18:20:56

Mike Benoit wrote:
> That helps some, but I'm sure it could be even more clear.
>
> The main issue is that you can't order by an expression computed by
> unions, correct? So couldn't the error message by something like:
>
> "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result
> columns and not be an expression")));

Well, it can be an expression _if_ the expression _matches_ an existing
UNION column.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: ORDER BY col is NULL in UNION causes error?

From

Tom Lane

Date:

11 January 2007, 18:25:13

Bruce Momjian <bruce@momjian.us> writes:
> Well, it can be an expression _if_ the expression _matches_ an existing
> UNION column.

You're mistaken.  It has to be *an output column name*.  Not anything else.

            regards, tom lane

Re: ORDER BY col is NULL in UNION causes error?

From

Bruce Momjian

Date:

11 January 2007, 19:04:41

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Well, it can be an expression _if_ the expression _matches_ an existing
> > UNION column.
>
> You're mistaken.  It has to be *an output column name*.  Not anything else.

Yea, I was thinking of this:

    SELECT *, state IS NULL AS state_is_null
    FROM income_tax_rate_us
    UNION
    SELECT *, state IS NULL AS state_is_null
    FROM income_tax_rate_us
    ORDER BY state_is_null

Here the column result is an expression, and you reference that.
Updated wording:

 test=> select * from test union select * from test order by x is null;
 ERROR:  A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: ORDER BY col is NULL in UNION causes error?

From

Tom Lane

Date:

11 January 2007, 19:18:59

Bruce Momjian <bruce@momjian.us> writes:
> Updated wording:

>  test=> select * from test union select * from test order by x is null;
>  ERROR:  A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names

This does not meet the style guidelines.

            regards, tom lane

Re: ORDER BY col is NULL in UNION causes error?

From

Alvaro Herrera

Date:

11 January 2007, 19:22:24

Bruce Momjian wrote:

> Here the column result is an expression, and you reference that.
> Updated wording:
>
>  test=> select * from test union select * from test order by x is null;
>  ERROR:  A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names

This seems at odds with our message style guidelines.  I'd suggest
putting the actual error message in errmsg() and the rest as a errhint,
maybe

(errmsg("invalid UNION/INTERSECT/EXCEPT ORDER BY clause"),
 errhint("You may specify only output result column names in the ORDER BY clauses."))

or something like that.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: ORDER BY col is NULL in UNION causes error?

From

Bruce Momjian

Date:

11 January 2007, 19:32:34

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Updated wording:
>
> >  test=> select * from test union select * from test order by x is null;
> >  ERROR:  A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names
>
> This does not meet the style guidelines.

Where are the style guidelines?  I looked before but can't find them.

I agree the second part could be a hint.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: ORDER BY col is NULL in UNION causes error?

From

Tom Lane

Date:

11 January 2007, 20:35:26

Bruce Momjian <bruce@momjian.us> writes:
> Where are the style guidelines?  I looked before but can't find them.

http://developer.postgresql.org/pgdocs/postgres/error-style-guide.html

            regards, tom lane

Re: ORDER BY col is NULL in UNION causes error?

From

Bruce Momjian

Date:

12 January 2007, 18:34:36

Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> > Here the column result is an expression, and you reference that.
> > Updated wording:
> >
> >  test=> select * from test union select * from test order by x is null;
> >  ERROR:  A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or functions, only result column names
>
> This seems at odds with our message style guidelines.  I'd suggest
> putting the actual error message in errmsg() and the rest as a errhint,
> maybe
>
> (errmsg("invalid UNION/INTERSECT/EXCEPT ORDER BY clause"),
>  errhint("You may specify only output result column names in the ORDER BY clauses."))
>
> or something like that.

Updated wording:

 test=> SELECT * FROM test UNION SELECT * FROM test ORDER BY X IS NULL;
 ERROR:  invalid UNION/INTERSECT/EXCEPT ORDER BY clause
 DETAIL:  Only result column names can be used, not expressions or functions.
 HINT:  Add the expression/function to every SELECT, or move the UNION into a FROM clause.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +