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 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

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, 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

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 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

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, 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

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 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

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 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

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 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