Обсуждение: 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
integerLINE1: ... '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
BYclauseLINE 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/functionto 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(2rows)
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 +
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
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)
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-specoptions of * selecting an output column by name or number. Enforce by checking that *
transformSortClausedoesn't add any items to tlist.
Perhaps sometime we ought to make an effort to relax that.
regards, tom lane
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 +
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 +