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

Motivation

...

Describe the problems you are trying to solve.

Public Interfaces

Briefly list any new interfaces that will be introduced as part of this proposal or any existing interfaces that will be removed or changed. The purpose of this section is to concisely call out the public contract that will come along with this feature.

A public interface is any change to the following:

• Binary log format

• The network protocol and api behavior

• Any class in the public packages under clientsConfiguration, especially client configuration

  • org/apache/kafka/common/serialization

  • org/apache/kafka/common

  • org/apache/kafka/common/errors

  • org/apache/kafka/clients/producer

  • org/apache/kafka/clients/consumer (eventually, once stable)

• Monitoring

• Command line tools and arguments

• Anything else that will likely break existing users in some way when they upgrade

Proposed Changes

Describe the new thing you want to do in appropriate detail. This may be fairly extensive and have large subsections of its own. Or it may be a few sentences. Use judgement based on the scope of the change.

Compatibility, Deprecation, and Migration Plan

• What impact (if any) will there be on existing users?
• If we are changing behavior how will we phase out the older behavior?
• If we need special migration tools, describe them here.
• When will we remove the existing behavior?

Test Plan

Describe in few sentences how the FLIP will be tested. We are mostly interested in system tests (since unit-tests are specific to implementation details). How will we know that the implementation works as expected? How will we know nothing broke?

Rejected Alternatives

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 additional columns for commonly used properties such as the row's timestamp or row number.

However, those columns should not be selected by default when expaning SELECT *.  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 *

Proposed Changes

Instead of introducing a new concept around system columns, we propose to leverage virtual metadata column concept and adjust their expanding behavior.

A new ConfigOption configures the expanding behavior:

    @Documentation.TableOption(execMode = Documentation.ExecMode.BATCH_STREAMING)
    public static final ConfigOption<List<ColumnExpansionStrategy>>
            TABLE_COLUMN_EXPANSION_STRATEGY =
                    key("table.column-expansion-strategy")
                            .enumType(ColumnExpansionStrategy.class)
                            .asList()
                            .defaultValues()
                            .withDescription(
                                    "Configures the default expansion behavior of 'SELECT *'. "
                                            + "By default, all top-level columns of the table's "
                                            + "schema are selected and nested fields are retained.");

    enum ColumnExpansionStrategy {
        // Excludes `c METADATA VIRTUAL FROM k` from SELECT *
        EXCLUDE_ALIASED_VIRTUAL_METADATA_COLUMNS,
        
        // Excludes `c METADATA VIRTUAL` from SELECT *
        EXCLUDE_DEFAULT_VIRTUAL_METADATA_COLUMNS
    }

Notes:

• The ConfigOption is prepared for future extensions (e.g. excluding computed columns, system columns, or default columns)
• Since the ConfigOption is a list, multiple strategies can be defined.

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

The change can be applied to SqlValidatorImpl and the corresponding class in Table API.

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

DESCRIBE works as before and will list the column. 

SQL Example: SELECT / INSERT INTO

-- Given a table t with schema
-- (
--   i INT,
--   s STRING,
--   rt1 TIMESTAMP_LTZ(3) METADATA VIRTUAL,
--   rt2 TIMESTAMP_LTZ(3) METADATA VIRTUAL FROM 'timestamp',
--   rt3 TIMESTAMP_LTZ(3) METADATA,
--   rt4 TIMESTAMP_LTZ(3) METADATA FROM 'timestamp'
-- )


SELECT * FROM t;
-- returns (i, s, rt1, rt2, rt3, rt4)


-- TableEnvironment is configured as
-- table.column-expansion-strategy = EXCLUDE_DEFAULT_VIRTUAL_METADATA_COLUMNS

SELECT * FROM t;
-- returns (i, s, rt2, rt3, rt4)

SELECT rt1, * FROM t;
-- rt1 must be selected explicitly and returns (rt1, i, s, rt2, rt3, rt4)


-- TableEnvironment is configured as
-- table.column-expansion-strategy = EXCLUDE_ALIASED_VIRTUAL_METADATA_COLUMNS

SELECT * FROM t;
-- returns (i, s, rt1, rt3, rt4)

INSERT INTO t SELECT * FROM t;
-- always works

Compatibility, Deprecation, and Migration Plan

Major compatibility issues should not arise as the whole behavior depends on the ConfigOption.

Test Plan

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

Rejected Alternatives

Completely new concept of System Columns

New concept of system columns with a reserved prefix such as `$`.

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

→ Concept of system columns including columns that can be expose per operation are future workIf there are alternative ways of accomplishing the same thing, what were they? The purpose of this section is to motivate why the design is the way it is and not some other way.