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.
Attachments:
reg.patchtext/x-patch; charset=UTF-8; name=reg.patchDownload
*** pgsql.orig.3b9482182009/src/test/regress/sql/foreign_data.sql 2009-02-15 21:41:53.423041398 +0100
--- /export/home/zk200664/work/mercurial/pgsql.orig/src/test/regress/sql/foreign_data.sql 2009-02-02 20:31:16.104197100 +0100
***************
*** 262,280 ****
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
! SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
! SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
! SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3;
! SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
SET ROLE regress_test_role;
! SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
DROP USER MAPPING FOR current_user SERVER st1;
SET ROLE regress_test_role2;
! SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
RESET ROLE;
--- 262,280 ----
SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
! SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2::name;
! SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2::name, 3;
! SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3::name;
! SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3::name, 4;
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
SET ROLE regress_test_role;
! SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3::name, 4;
SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5;
DROP USER MAPPING FOR current_user SERVER st1;
SET ROLE regress_test_role2;
! SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3::name, 4;
RESET ROLE;
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