DDL Plan Model.

[yliuuuu]

Relevant Issues

• [Closes/Related To] Issue #XXX

Description

• Experimental DDL Plan Modeling.

Reviewer Note:

I recommend taking a look at the test cases first. Then the internal plan modeling.

Plan Vs Internal Plan: the diff between the two is large in this PR, this is because we want to continue experimenting with model of the ddl plan and type system, while maintain a relatively stable public plan.

Internal Plan:

DDL:

  d_d_l::{
    shape: static_type,
    op: ddl_op,
  },

The DDL Operation carries a shape properties and an op properties.

The shape properties describe the resulting shape of data after performed the DDL Operation. This split between Op and shape can be helpful when we incorporate other DDL operation, like ALTER

DdlOpCreateTable

  create_table::{
    name: identifier,
    shape: '.type.collection',
    partition_by: optional::partition_by,
    table_properties: list::[table_property],
}

• Name: Table name; support qualified and unqualified identifier. In this PR, no validation on table name is performed. This is, we do not attempts to validate on potential problems such as table already exists, no existing scope, etc.
• Shape: The shape of the data stored in the table. The internal plan comes with a helper type definition that helps to better traverse and manipulate the constraints putting on the type. Such type definition is validated, normalized, resolved, and converted to StaticType. In the public plan, the type definition is not kept to reduced the public API surface.
• Partition By: Despite PartiQL does not support partitioned table at this stage, the plan will carry this information to subsequent processor.
• Table_Properties: A arbitrary key value pair. PartiQL Planner at this stage does not use those information, the plan will carry this information to subsequent processor.

Public Plan:

  d_d_l::{
    op: ddl_op,
  }

ddl_op::[
  create_table::{
    name: identifier,
    shape: 'static_type',
    partition_by: optional::partition_by,
    table_properties: list::[table_property],
  },
]

The public plan is designed to have minimum interference with the existing public APIs. Hence we switch the shape in create table to use static type.

Notice that DDL node will not carry the static type in the public plan, this is a decision that can be revisited. But I opt to not included this properties in this PR. Depending on the status of the type work do over, we can potentially just use the new Type APIs instead of the migration plan. Because we are only working on Create Table, no information will be lost.

Constraints

We should distinguish those from the constraint concept from typed lambda calculus and the constraint concept from SQL Spec.

• SQL constraints:
  • A named database object, the primary purpose is to enforce data integrity.
  • SQL (spec) allows for query constraints object like other SQL objects.
  • SQL (spec) defines syntax/semantics for defining, manipulating and dropping constraints objects.
• Type constraints: Conditions or restrictions placed on the types of expressions or variables to ensure type correctness.
  • Type constraints are used in type inference and type checking processes, where the system if an expression conforms to a particular type or if a type assignment is valid.

Not Null Constraints

• Modeled as a type constraints. i.e., changes the result typing of an type expression.
• a INT2 -> the type of a will be unionOf(null, int2).
• a INT2 NOT NULL -> the type of a will be int2.
• The type expression in the first case is INT2, the type expression in the second case is INT2 NOT NULL.

Check constraints.

• Modeled as a SQL constraint.
• a INT2 CHECK(a > 0)
• The type expression in this case is INT2. The check constraint is NOT part of the type expression.
• Semantics rule with check constraint:
  • The expression of check constraints needs to be inferred as a boolean.
  • If the constraint is originally set as a attribute level constraints, the scope of check constraint expression is limited to such attribute.
  • If the constraint is originally set as a tuple level constraint, the scope of check constraint expression is limited to the attributes defined in the tuple.
  • We normalized attribute level check constraints to tuple level check constraints. This is, all check constraints are kept under type struct (record in this pr).

Primary key constraint:

• Primary key constraint is associated with collection level.
• If an attribute is declared to be primary key, then for all tuples within the collection, such attribute needs to be unique and not null.
• In this PR, if an attribute is declared as not null, then we will impose a not null constraint on the given attribute (notice that is a type constraint), and keep track of the primary key constraint at the collection level, to allow dropping of the primary key constraints (not yet supported).

This is, given

CREATE TABLE tbl (
     a INT2 PRIMARY KEY
)

The type will be:

Bag (
   STRUCT (
       a : INT2
   )
   PK : ["a"]
)

Other aspect of the PR

• ShapeNormalizer: Normalize constraints to the associated type.

  • Consider a INT2 PRIMARY KEY. The SQL syntax places primary key constraint, which is a collection level constraint, on attribute level.
  • The internal plan converted from AST will also associated this primary key constraints with Record.Field (struct field) to keep the conversion code simple.
  • The shape normalizer will "lift" the constraint to collection level.

• ConstraintResolver: Convert the internal type definition to Static Type.

• DDLFeatureGate: Gate certain features from customer usage. This is to unblock us from continuously doing internal experiments without the risk of breaking our customer.

Other Information

• Updated Unreleased Section in CHANGELOG: [YES/NO]

  • < If NO, why? >

• Any backward-incompatible changes? [YES/NO]

  • < If YES, why? >
  • < For this purpose, we define backward-incompatible changes as changes that—when consumed—can potentially result in errors for users that are using our public APIs or the entities that have public visibility in our code-base. >

• Any new external dependencies? [YES/NO]

  • < If YES, which ones and why? >
  • < In addition, please also mention any other alternatives you've considered and the reason they've been discarded >

• Do your changes comply with the Contributing Guidelines and Code Style Guidelines? [YES/NO]

License Information

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.

[github-actions[bot]]

Conformance comparison report-Cross Engine

	Base (eval)	legacy	+/-
% Passing	90.70%	92.51%	1.80%
:white_check_mark: Passing	5278	5382	104
:x: Failing	541	436	-105
:large_orange_diamond: Ignored	0	0	0
Total Tests	5819	5818	-1

Number passing in both: 5071

Number failing in both: 229

Number passing in eval engine but fail in legacy engine: 207

Number failing in eval engine but pass in legacy engine: 312 :interrobang: CONFORMANCE REPORT REGRESSION DETECTED :interrobang: The complete list can be found in GitHub CI summary, either from Step Summary or in the Artifact. 312 test(s) were failing in eval but now pass in legacy. Before merging, confirm they are intended to pass. The complete list can be found in GitHub CI summary, either from Step Summary or in the Artifact.

Conformance comparison report-Cross Commit-EVAL

	Base (4f89c2dd4cb6a6199f8056bedc2064db32ea2ce2)	4588ef844bf159add96505831fabbc78fc45a592	+/-
% Passing	90.70%	90.70%	0.00%
:white_check_mark: Passing	5278	5278	0
:x: Failing	541	541	0
:large_orange_diamond: Ignored	0	0	0
Total Tests	5819	5819	0

Number passing in both: 5278

Number failing in both: 541

Number passing in Base (4f89c2dd4cb6a6199f8056bedc2064db32ea2ce2) but now fail: 1

Number failing in Base (4f89c2dd4cb6a6199f8056bedc2064db32ea2ce2) but now pass: 1 :interrobang: CONFORMANCE REPORT REGRESSION DETECTED :interrobang:. The following test(s) were previously passing but now fail:

Click here to see

- Example 6 — Value Coercion, compileOption: LEGACY

The following test(s) were previously failing but now pass. Before merging, confirm they are intended to pass:

Click here to see

- Example 6 — Value Coercion, compileOption: LEGACY

Conformance comparison report-Cross Commit-LEGACY

	Base (4f89c2dd4cb6a6199f8056bedc2064db32ea2ce2)	4588ef844bf159add96505831fabbc78fc45a592	+/-
% Passing	92.51%	92.51%	0.00%
:white_check_mark: Passing	5382	5382	0
:x: Failing	436	436	0
:large_orange_diamond: Ignored	0	0	0
Total Tests	5818	5818	0

Number passing in both: 5382

Number failing in both: 436

Number passing in Base (4f89c2dd4cb6a6199f8056bedc2064db32ea2ce2) but now fail: 0

Number failing in Base (4f89c2dd4cb6a6199f8056bedc2064db32ea2ce2) but now pass: 0

[yliuuuu]

Close this PR for now.

We'd like to merge DDL as a post-V1 Feature.