Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).

Motivation

Many SQL vendors expose additional metadata via so-called "pseudo columns" or "system columns" next to the physical columns.

Some examples can be found here:

• BigQuery exposes _PARTITIONTIME: Ingestion-time partitioned tables contain a pseudo-column named _PARTITIONTIME, which is the partitioning column.
• Oracle exposes ROWID: For each row in the database, the ROWID pseudocolumn returns a row’s address. 
• DB2 exposes LEVEL: A pseudo column is an identifier that shares the same namespace as columns and variables. After regular name resolution for a column has failed, Db2® attempts to match an unqualified identifier to a pseudo column name. 
• According to the SQL standard, system-versioned tables contain some system columns: Columns of a system-versioned table include a system-time period start column and a system-time period end column.
• Some parts of the Calcite stack already consider system columns (e.g. org.apache.calcite.sql.validate.SqlValidatorNamespace#getRowTypeSansSystemColumns)

Esp. for platform teams and connector/catalog implementers, it would be beneficial to offer system columns for commonly used properties such as the row's timestamp or row number.

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

Flink SQL already offers pseudo columns next to the physical columns exposed as metadata columns.

This proposal suggests to evolve the existing column design slightly to be more useful for platform providers.

Public Interfaces

• SELECT *

• SHOW CREATE TABLE

Proposed Changes

We propose to introduce the new concept of system columns.

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

Depending on the catalog implementation, system columns might be available in every table, thus conflicts with physical columns must be avoided.

We suggest to apply constraints on the prefix of a system column to make it immediately visible that a system column is used.

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 4: Configurable

Pro:

• Provides highest flexibility

Con:

• Could be confusing for the overall Flink experience
• 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 TIMESTAMP_LTZ(3)) when queried for t.
 
SELECT * FROM t;
-- returns (i INT, s STRING)
 
SELECT $rowtime, * FROM t;
-- returns ($rowtime TIMESTAMP_LTZ(3), i INT, s STRING)
 
INSERT INTO t SELECT * FROM t;
-- works
 
INSERT INTO t SELECT $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 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 during a SELECT *.
 
SELECT * FROM t;
-- returns ($rowtime TIMESTAMP_LTZ(3), i INT, s STRING)

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

Compatibility, Deprecation, and Migration Plan

Major compatibility issues should not arise as the whole behavior depends on metadata key of the connector.

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

Test Plan

Unit tests on table environment and maybe one ITCase should be sufficient.

Rejected Alternatives

Completely new concept

Metadata columns fit nicely and require no additional interfaces for connectors and/or changes within planner rules.