...

However, those columns should not be visible selectable by default and should not conflict with physical columns. Also for the sake of backward compatibility.

...

Public Interfaces

• SELECT *DESCRIBE table

• SHOW CREATE TABLE

Proposed Changes

...

System columns are a special case of a metadata column:

• They should be read-only.

• They should not influence the query-to-sink schema.

• Thus, only METADATA VIRTUAL columns qualify as system columns.

• They start with a reserved prefix.

• Metadata columns with different alias (using METADATA FROM) do not qualify as system columns.

Metadata Key Prefix Constraint

...

The prefix constraint is on the metadata key, not on the column name itself. This avoids backwards compatibility issues.

Open question: Which sign should indicate a system column?
Current preference: Option 3

Option 1: Underscore

Pro:

• Similar to BigQuery
• Queries are still nicely readable
  • SQL: SELECT _rowtime FROM t
  • Table API: tableEnv.from("t").select(Expressions.$("_rowtime"))

Con:

• Underscore is a common character for "private"/"internal" fields

...

• Likelihood of conflicts might be higher as it used quite often in physical columns (allowed in Avro, Protobuf, JSON)

Option 2: Double Underscore

Pro:

• Queries are also nicely readable

Con:

• Underscore is a common character for "private"/"internal" fields

• Still allowed in Avro, Protobuf, JSON but with less likelihood of causing conflicts

Option 3: Dollar sign

Pro:

• Less conflicts with existing physical columns

• Uncommon in JSON, not allowed in Avro and Protobuf

• Dollar signs are used for other generated things already (window properties, Paimon side tables) so users know that those come from the system

Con:

• Queries are not so nicely readable
  • SQL: SELECT $rowtime FROM t
  • Table API: tableEnv.from("t").select(Expressions.$("$rowtime"))


    But maybe we can advertise Expressions.col("$rowtime") in the future.

Option 4Option 3: Configurable

Pro:

• provides Provides highest flexibility

Con:

• could Could be confusing for the overall Flink experience
• query Query semantics would depend on the connector+system configuration

SQL Examples: SELECT / INSERT INTO

System columns are not contained in a SELECT * FROM t or tableEnv.from("t").select(Expressions.$("*")).

Users need to select them explicitly. This allows for adding more system columns in the future without breaking existing SQL queries.

DESCRIBE works as before and will list the column. 

SHOW CREATE TABLE will skip the column.

-- Given a table t (i INT, s STRING).
-- Catalog additionally exposes (_rowtime$rowtime TIMESTAMP_LTZ(3)) when queried for t.
 
SELECT * FROM t;
-- returns (i INT, s STRING)
 
SELECT _rowtime$rowtime, * FROM t;
-- returns (_rowtime$rowtime TIMESTAMP_LTZ(3), i INT, s STRING)
 
INSERT INTO t SELECT * FROM t;
-- works
 
INSERT INTO t SELECT _rowtime$rowtime, * FROM t;
-- will error because system column is not writable
 
DESCRIBE t
-- will only show (i INT, s STRING, $rowtime TIMESTAMP_LTZ(3))
 
SHOW CREATE TABLE
-- will only show (i INT, s STRING)

...

-- Given a table t (_rowtime$rowtime TIMESTAMP_LTZ(3), i INT, s STRING).
-- Catalog should not expose system column when queried for t. It should expose the physical column.
-- Ideally, we should support two columns with the same name but this is currently not supported in Flink.
-- If we support two columns with the same name in the future, the physical column should have precedence.
 
SELECT * FROM t;
-- returns (_rowtime$rowtime TIMESTAMP_LTZ(3), i INT, s STRING)

-- Given a table t (custom_rowtimets TIMESTAMP_LTZ(3) METADATA VIRTUAL FROM 'ts$rowtime', i INT, s STRING).
 
SELECT * FROM t;
-- returns (custom_rowtimets TIMESTAMP_LTZ(3), i INT, s STRING) as "custom_ts" does not start with "_" 
-- uses an alias and potentially a different data type to which the system 
-- column is casted.

Compatibility, Deprecation, and Migration Plan

...

No metadata key currently starts with "_", or "$" and no system column will be added by existing catalogs yet.

...