Обсуждение: casting in ORDER BY

I try to fix foreign_data regression test for Czech locale (HEAD). For
example I replaced

SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3,
4;

with following statement:

SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2,
3::name, 4;

which should use C sorting rules on thirds column, but it look likes
that third column is ignored. See diff:

  (4 rows)

  SET ROLE regress_test_role;
! SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
   authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
  --------------------------+------------------------+---------------------+-------------+--------------
   PUBLIC                   | regression             | st1                 | modified    | 1
   regress_test_role        | regression             | s5                  | modified    | 1
-  regress_test_role        | regression             | s6                  | username    | test
   regress_test_role        | regression             | st1                 | password    | boo
   regress_test_role        | regression             | st1                 | username    | bob
  (5 rows)

--- 711,723 ----
  (4 rows)

  SET ROLE regress_test_role;
! SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3::name, 4;
   authorization_identifier | foreign_server_catalog | foreign_server_name | option_name | option_value
  --------------------------+------------------------+---------------------+-------------+--------------
   PUBLIC                   | regression             | st1                 | modified    | 1
   regress_test_role        | regression             | s5                  | modified    | 1
   regress_test_role        | regression             | st1                 | password    | boo
+  regress_test_role        | regression             | s6                  | username    | test
   regress_test_role        | regression             | st1                 | username    | bob
  (5 rows)


Is it bug or I overlook something?

    Zdenek

PS: Regression test patch is attached.

Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> I try to fix foreign_data regression test for Czech locale (HEAD). For
> example I replaced 
> SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3,
> 4;
> with following statement:
> SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2,
> 3::name, 4;

That means something like '3'::name, ie it's a constant.  You'd need
to reference the actual column name.
        regards, tom lane

PS: I'm still of the opinion that it's a bad idea to commit to keeping
all the regression tests independent of whether digits sort before
or after letters.

Tom Lane píše v ne 15. 02. 2009 v 15:51 -0500:
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> > I try to fix foreign_data regression test for Czech locale (HEAD). For
> > example I replaced 
> > SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3,
> > 4;
> > with following statement:
> > SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2,
> > 3::name, 4;
> 
> That means something like '3'::name, ie it's a constant.  You'd need
> to reference the actual column name.

I see, thanks.

>             regards, tom lane
> 
> PS: I'm still of the opinion that it's a bad idea to commit to keeping
> all the regression tests independent of whether digits sort before
> or after letters.

It is not about how digits are sort, but if we will support "all" locale
for regression tests or not. Maybe add directory like
regress/expected/cs_CZ and put variant here. It should help to avoid
misinterpretation in case when result is not valid for other locale.
    Zdenek