Re: [HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread Bruce Momjian
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

Re: [HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread Bruce Momjian
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,

Re: [HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread Tom Lane
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

Re: [HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread Greg Stark
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,

Re: [HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread David G. Johnston
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

Re: [HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread Greg Stark
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

[HACKERS] Odd error when using UNION and COLLATE

2016-07-20 Thread Bruce Momjian
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