[Tracking] Standard SQL conformance

[siyul-park]

Tracking issue for full standard-SQL support. The minivm-based engine currently supports SELECT (projection, WHERE, arithmetic, scalar functions, GROUP BY + HAVING, ORDER BY, LIMIT, DISTINCT), two-table INNER JOIN, bind variables, and single-table INSERT/UPDATE/DELETE.

Remaining work to reach standard-SQL conformance:

Queries

• Support OUTER, CROSS, and multi-table JOINs #18 OUTER / CROSS / multi-table JOINs
• Support set operations: UNION / UNION ALL / INTERSECT / EXCEPT #19 Set operations (UNION / INTERSECT / EXCEPT)
• Support standard predicates and conditional expressions #20 Predicates and conditional expressions (IN, BETWEEN, LIKE, IS NULL, CASE, COALESCE)
• Support window functions (OVER / PARTITION BY / ranking / frames) #21 Window functions (OVER / PARTITION BY / ranking / frames)
• Support common table expressions (WITH, recursive) #22 Common table expressions (WITH, recursive)
• Support advanced aggregation (COUNT DISTINCT, GROUPING SETS, FILTER) #23 Advanced aggregation (COUNT DISTINCT, GROUPING SETS, FILTER)
• Enable WHERE Clause Sub-Queries via Sub-Plan and Values() #6 Sub-queries

Expressions and types

• Complete scalar function library, CAST/CONVERT, and ORDER BY options #24 Scalar function library, CAST/CONVERT, ORDER BY options
• Complete type system: DECIMAL, full temporal, BOOLEAN, three-valued logic #25 Type system (DECIMAL, temporal, BOOLEAN, three-valued logic)

DML / DDL / transactions

• Support schema DDL for tables, views, and indexes #26 Schema DDL (tables, views, indexes)
• Support transaction control statements #27 Transaction control
• Extend DML write statements (INSERT ... SELECT, upsert, RETURNING, DEFAULT) #28 Extended DML (INSERT ... SELECT, upsert, RETURNING, DEFAULT)

Engine

• Add Index Pushdown Support via Table Index Hints #7 Index push-down utilization
• Native-opcode specialization of numeric expressions for the minivm JIT
• Cost-based planner / optimizer (the bundled minivm O1 optimizer is incompatible with emitted branch offsets)