Odd error when using UNION and COLLATE
Seems you can't use UNION and COLLATE in the same SELECT statement; you
have to put the UNION inside of WITH and then do the COLLATE outside:
test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C";
ERROR: collations are not supported by type integer
LINE 1: ... 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C...
^
test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
ERROR: invalid UNION/INTERSECT/EXCEPT ORDER BY clause
LINE 1: ...CT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE ...
^
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.
test=> WITH d AS (SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x) SELECT * FROM d ORDER BY x COLLATE "C";
x
-----
a-c
ab
(2 rows)
I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and
we don't allow a_expr in a UNION. Perhaps we are too strict here, but I
can't tell.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian <bruce@momjian.us> wrote:
SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ;
┌─────┐
│ x │
├─────┤
│ a-c │
│ ab │
└─────┘
(2 rows)
But I think I agree that it's surprising that the collate clause isn't
working in the ORDER BY on a column produced by a UNION. Certainly
that's where people usually want to put it.
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jul 20, 2016 at 5:38 PM, Bruce Momjian <bruce@momjian.us> wrote:
I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and
we don't allow a_expr in a UNION. Perhaps we are too strict here, but I
can't tell.
ORDER BY 1 COLLATE "C" is indeed an expression - the number no longer
refers to a column position but it is a constant. The presence or absence
of UNION doesn't factor into things here - the expression itself is useless
on its face.
This one is a bit different in cause but I suspect is working as well as
can be expected.
SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
David J.
Actually there's nothing about UNION here. It's true for any column alias:
::***> select 'a-c' AS x ORDER BY x COLLATE "C" ;
ERROR: 42703: column "x" does not exist
LINE 2: select 'a-c' AS x ORDER BY x COLLATE "C" ;
^
LOCATION: errorMissingColumn, parse_relation.c:2892
Time: 0.204 ms
Also you don't need WITH, just an old-fashioned inline view:
::***> select * from (select 'a-c'::text AS x) as subquery ORDER BY x
COLLATE "C" ;
┌─────┐
│ x │
├─────┤
│ a-c │
└─────┘
(1 row)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Greg Stark <stark@mit.edu> writes:
But I think I agree that it's surprising that the collate clause isn't
working in the ORDER BY on a column produced by a UNION. Certainly
that's where people usually want to put it.
See this ancient comment in transformSetOperationStmt:
* For now, we don't support resjunk sort clauses on the output of a
* setOperation tree --- you can only use the SQL92-spec options of
* selecting an output column by name or number. Enforce by checking that
* transformSortClause doesn't add any items to tlist.
Perhaps sometime we ought to make an effort to relax that.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jul 20, 2016 at 10:55:38PM +0100, Greg Stark wrote:
On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian <bruce@momjian.us> wrote:
SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";
::***> select 'a-c' COLLATE "C" AS x UNION ALL SELECT 'ab' AS x ORDER BY x ;
Oh, collate on the string, before AS. I never thought of that.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jul 20, 2016 at 06:03:08PM -0400, Tom Lane wrote:
Greg Stark <stark@mit.edu> writes:
But I think I agree that it's surprising that the collate clause isn't
working in the ORDER BY on a column produced by a UNION. Certainly
that's where people usually want to put it.See this ancient comment in transformSetOperationStmt:
* For now, we don't support resjunk sort clauses on the output of a
* setOperation tree --- you can only use the SQL92-spec options of
* selecting an output column by name or number. Enforce by checking that
* transformSortClause doesn't add any items to tlist.Perhaps sometime we ought to make an effort to relax that.
Oh, I didn't see that above the error block.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers