There are some edge cases that custom keys do not support. This issue is opened to discuss/track the work to decide if we should support and the work needed to be done to support.
After some testing, these are things that work/don't work with custom key columns so far:
TLDR: it has to be an actual column that exists on the table - not something created in CDC queries or created afterwards.
using a key column that user created in CDC queries
CREATE CHANGEFEED WITH key_column='double_b', unordered AS SELECT concat(b, c) AS double_b FROM foo
selecting on a virtual column does not work
CREATE TABLE foo (a INT PRIMARY KEY, b STRING, c STRING, d STRING AS (concat(b, c)) VIRTUAL)
CREATE CHANGEFEED WITH key_column='d', unordered AS SELECT d FROM foo
selecting on a hidden column works
CREATE TABLE foo (a INT PRIMARY KEY, b STRING NOT VISIBLE)
CREATE CHANGEFEED WITH key_column='b', unordered AS SELECT b FROM foo
selecting on cdc_prev or cdc_is_delete doesn't seem to work, and cdc_is_delete is not recognized as a function either when used in CDC queries
CREATE CHANGEFEED WITH key_column='b', unordered AS SELECT cdc_is_delete() FROM foo
selecting on crdb_internal_mvcc_timestamp works
CREATE CHANGEFEED WITH key_column='crdb_internal_mvcc_timestamp', unordered AS SELECT crdb_internal_mvcc_timestamp FROM foo WHERE crdb_internal_mvcc_timestamp > 0
using CDC queries with event_op works
CREATE CHANGEFEED WITH key_column='b', unordered AS SELECT * FROM foo WHERE event_op() = 'delete'
There are some edge cases that custom keys do not support. This issue is opened to discuss/track the work to decide if we should support and the work needed to be done to support.
After some testing, these are things that work/don't work with custom key columns so far: TLDR: it has to be an actual column that exists on the table - not something created in CDC queries or created afterwards.
crdb_internal_mvcc_timestamp
worksJira issue: CRDB-33949