Re: [HACKERS] Odd error when using UNION and COLLATE
On Wed, Jul 20, 2016 at 06:03:08PM -0400, Tom Lane wrote: > Greg Stark 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 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
Re: [HACKERS] Odd error when using UNION and COLLATE
On Wed, Jul 20, 2016 at 10:55:38PM +0100, Greg Stark wrote: > On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian 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 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
Re: [HACKERS] Odd error when using UNION and COLLATE
Greg Stark 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
Re: [HACKERS] Odd error when using UNION and COLLATE
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
Re: [HACKERS] Odd error when using UNION and COLLATE
On Wed, Jul 20, 2016 at 5:38 PM, Bruce Momjian 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.
Re: [HACKERS] Odd error when using UNION and COLLATE
On Wed, Jul 20, 2016 at 10:38 PM, Bruce Momjian 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
[HACKERS] 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 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