sql: perform time zone name comparison case-insensitively #36847
Description
In Postgres, the session timezone setting can always be set by name case-insensitively, but this isn't the case in CockroachDB. Here are examples using set timezone in Postgres 11:
PG 11, interactive
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
postgres=# set timezone=utc;
SET
postgres=# show timezone;
TimeZone
----------
UTC
(1 row)
postgres=# set timezone='america/new_york';
SET
postgres=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
postgres=# set timezone='est';
SET
postgres=# show timezone;
TimeZone
----------
EST
(1 row)
postgres=# set timezone='eUrOPe/iSLe_Of_mAN';
SET
postgres=# show timezone;
TimeZone
--------------------
Europe/Isle_of_Man
(1 row)
postgres=# set timezone='PACIFIC/GALAPAGOS';
SET
postgres=# show timezone;
TimeZone
-------------------
Pacific/Galapagos
(1 row)
CockroachDB's behavior is not internally consistent and I'm not sure what the pattern is. When using set timezone interactively, utc and est are accepted, but america/new_york is not:
CockroachDB 19.1, interactive
root@localhost:26257/defaultdb> set timezone='utc';
SET
Time: 670.411µs
root@localhost:26257/defaultdb> show timezone;
timezone
+----------+
UTC
(1 row)
Time: 929.086µs
root@localhost:26257/defaultdb> set timezone='est';
SET
Time: 709.276µs
root@localhost:26257/defaultdb> show timezone;
timezone
+----------+
EST
(1 row)
Time: 919.475µs
root@localhost:26257/defaultdb> set timezone='america/new_york';
pq: invalid value for parameter "timezone": "'america/new_york'"
DETAIL: cannot find time zone "america/new_york": timezone data cannot be found
root@localhost:26257/defaultdb> set timezone='America/New_York';
SET
Time: 685.353µs
root@localhost:26257/defaultdb> show timezone;
timezone
+------------------+
America/New_York
(1 row)
Time: 960.559µs
And when pasing the timezone in the connection URL, the case-sensitive zone name appears to always be required: utc and est don't work, and neither does america/new_york:
CockroachDB 19.1, connection string
$ cockroach sql --url 'postgres://localhost:26257/defaultdb?sslmode=disable&timezone=utc' -e 'show timezone'
Error: pq: invalid value for parameter "TimeZone": "utc"
Failed running "sql"
$ cockroach sql --url 'postgres://localhost:26257/defaultdb?sslmode=disable&timezone=UTC' -e 'show timezone'
timezone
+----------+
UTC
(1 row)
$ cockroach sql --url 'postgres://localhost:26257/defaultdb?sslmode=disable&timezone=est' -e 'show timezone'
Error: pq: invalid value for parameter "TimeZone": "est"
Failed running "sql"
$ cockroach sql --url 'postgres://localhost:26257/defaultdb?sslmode=disable&timezone=EST' -e 'show timezone'
timezone
+----------+
EST
(1 row)
$ cockroach sql --url 'postgres://localhost:26257/defaultdb?sslmode=disable&timezone=america/new_york' -e 'show timezone'
Error: pq: invalid value for parameter "TimeZone": "america/new_york"
Failed running "sql"
$ cockroach sql --url 'postgres://localhost:26257/defaultdb?sslmode=disable&timezone=America/New_York' -e 'show timezone'
timezone
+------------------+
America/New_York
(1 row)
Directly motivated by #14988, but we've seen the same issue before (e.g. #31297). In the past we've said "timezones are case sensitive on Linux", but that's a distinction that Postgres doesn't make, so that seems like an unnecessary restriction.