sql: old value for cluster setting sql.defaults.vectorize can prevent login after upgrade to 24.2

[michae2]

(Thanks to @spilchen for figuring this all out!)

In v20.2 we introduced the value 201auto for cluster setting sql.defaults.vectorize and session variable vectorize. This value was deprecated in v21.1, instead mapping to on. However, the remapping meant that any clusters running v21.1 would have the enum value '1' stored for 'on' (which in v20.2 was the enum value for '201auto'):

roachprod@localhost:26257/defaultdb> select version();
                                          version
--------------------------------------------------------------------------------------------
  CockroachDB CCL v21.1.0 (x86_64-unknown-linux-gnu, built 2021/05/17 13:49:40, go1.15.11)
(1 row)

roachprod@localhost:26257/defaultdb> set cluster setting sql.defaults.vectorize = 'on';
SET CLUSTER SETTING

roachprod@localhost:26257/defaultdb> select * from system.settings where name = 'sql.defaults.vectorize';
           name          | value |             lastUpdated             | valueType
-------------------------+-------+-------------------------------------+------------
  sql.defaults.vectorize | 1     | 2024-10-23 11:22:10.312679+00:00:00 | e
(1 row)

This remapping was subsequently removed in v21.2, and the enum value of '1' was no longer possible. The enum value '2' was stored for 'on'. Later, the enum value '0' was stored for 'on'.

In v24.2 we introduced stricter validation of enum settings. This validation throws an error when it encounters the enum value '1' for vectorize.

# Server version: CockroachDB CCL v24.2.3 (aarch64-apple-darwin21.2, built 2024/09/23 22:27:07, go1.22.5 X:nocoverageredesign) (same version as client)
# Cluster ID: 5c81eb9b-8873-4d27-aa7f-f7f21f29ed38
# Organization: Cockroach Demo
#
# Enter \? for a brief introduction.
#
demo@127.0.0.1:26257/defaultdb> UPSERT INTO system.settings (name, value, "valueType") VALUES ('sql.defaults.vectorize', '1', 'e');
INSERT 0 1

demo@127.0.0.1:26257/defaultdb> SHOW CLUSTER SETTING sql.defaults.vectorize;
  sql.defaults.vectorize
--------------------------
  1
(1 row)

demo@127.0.0.1:26257/defaultdb> SET vectorize = DEFAULT;
ERROR: invalid value for parameter "vectorize": "unknown(1)"
SQLSTATE: 22023
HINT: Available values: off,on,experimental_always

So any clusters that had SET CLUSTER SETTING sql.defaults.vectorize = 'on'; in v21.1 will throw an error when starting new sessions after upgrading to v24.2.

Jira issue: CRDB-43530

[michae2]

Marking as release-blocker so that we don't cut v24.2.5 or v24.3.0-beta.3 without the fix.