Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Computed columns are introduced in MS-SQL-2016 [1], MYSQL-5.6 [2] and ORACLE-11g [3].

Public Interfaces

Computed Column Syntax

Combined with the grammar of MS-SQL-2017[1] and MYSQL-5.6[2], we proposed computed column grammar as follows:

...

  • The optional DATA_TYPE, if you want to force the data type of the computed column, specify a data type as a type hint that Flink would use. Such as the Decimal, user can coerce to the precision/scale they want.
  • The VIRTUAL keyword: Column values are not stored, but are evaluated when rows are read, it would trigger computation when it outputs, specifically for proc time(we may do some materialization eagerly, see RelTimeIndicatorConverter for details). A virtual column will not output to the table sink.
  • The STORED keyword: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.(We may support this in the future if it is needed.)
  • The default keyword is VIRTUAL if neither keyword is specified.
  • It can be combined with a NULL attribute, or provide a comment. The nullability is decided by the expression and can not be specified.
Restrictions:
  • Literals, deterministic built-in functions, and operators are permitted. The function can only be non-deterministic when the column strategy is VIRTUAL. User defined functions and built-in functions are allowed.
  • It does not support sub-query.
  • The stored column can only be inserted into explicitly with DEFAULT value from ROW values expression, see example#1 below.
  • It can only refer to columns defined in the same table.


ColumnStrategy

ColumnStrategy defines the column constraints of each DDL declared column. These options are not orthogonal, i.e. you can not declare a column as both STORED and NULLABLE.

Code Block
languagejava
public enum ColumnStrategy {
    NULLABLE, // mark the column as nullable value, only used for normal column
    NOT_NULLABLE, // mark the column as not nullable, only used for normal column
    STORED, // Computed and stored, but can not insert into
    VIRTUAL; // Computed and not stored, can not insert into

    public String asSerializableString() {
      switch (this) {
      case NULLABLE:
        return "NULL";
      case NOT_NULLABLE:
        return "NOT NULL";
      case STORED:
        return "STORED";
      case VIRTUAL:
        return "VIRTUAL";
      default:
        throw new TableException("Unexpected column strategy " + this);
      }
    }

InitializerExpressionFactory

InitializerExpressionFactory defines the value generation strategy for computed columns. It would generate a RexNode for each VIRTUAL column which can be used in the logical plan to derive the value we want.

Code Block
languagejava
/**
 * InitializerExpressionFactory supplies default values for INSERT, UPDATE, and NEW.
 */
public interface InitializerExpressionFactory {
  //~ Methods ----------------------------------------------------------------

  /**
   * Returns how a column is populated.
   *
   * @param table   the table containing the column
   * @param iColumn the 0-based offset of the column in the table
   *
   * @return generation strategy, never null
   *
   * @see RelOptTable#getColumnStrategies()
   */
  ColumnStrategy generationStrategy(
      RelOptTable table,
      int iColumn);

  /**
   * Creates an expression which evaluates to the default value for a
   * particular column.
   *
   * @param table   the table containing the column
   * @param iColumn the 0-based offset of the column in the table
   * @param context Context for creating the expression
   *
   * @return default value expression
   */
  RexNode newColumnDefaultValue(
      RelOptTable table,
      int iColumn,
      InitializerContext context);

  /**
   * Creates an expression which evaluates to the initializer expression for a
   * particular attribute of a structured type.
   *
   * @param type            the structured type
   * @param constructor     the constructor invoked to initialize the type
   * @param iAttribute      the 0-based offset of the attribute in the type
   * @param constructorArgs arguments passed to the constructor invocation
   * @param context Context for creating the expression
   *
   * @return default value expression
   */
  RexNode newAttributeInitializer(
      RelDataType type,
      SqlFunction constructor,
      int iAttribute,
      List<RexNode> constructorArgs,
      InitializerContext context);
}

// End InitializerExpressionFactory.java

TableColumn

TableColumn describe a column definition of a table that includes the definition of column name, column data type and the column strategies.

Code Block
languagejava
/**
 * A table column represents a table's column structure with field name, data type
 * and column strategies.
 */
public class TableColumn {
  private String fieldName;
  private DataType fieldDataType;
  private Expression expression; // the computed column expression.
  private ColumnStrategy columnStrategy;

  private TableColumn(String fieldName,
      DataType fieldDataType,
      ColumnStrategy columnStrategy) {
    this.fieldName = Objects.requireNonNull(fieldName, "Column name can not be null.");
    this.fieldDataType = Objects.requireNonNull(fieldDataType,
      "Column data type can not be null.");
    this.expression = expression;
    this.columnStrategy = Objects.requireNonNull(columnStrategy);
  }

  public enum ColumnStrategy {
        ...
  }
  ...
}

TableSchema

TableSchema describes a table schema for internal and current connector use.

Code Block
languagejava
/**
 * A table schema that represents a table's structure with field names and data types.
 */
public class TableSchema {
  private final TableColumn[] tableColumns;
    ...
}

Proposed Changes

Plan Rewrite

...