Query API

• current-contract: Project physical columns or *, combine column-to-literal filters with AND using =, !=, <, <=, >, or >=, order by supported keys, and apply numeric LIMIT and OFFSET. Strict < and > comparisons remain exclusive end to end. When null placement is omitted, ASC places NULL last and DESC places NULL first; explicit placement is supported only for the bounded result-shaping forms below.

Examples

• SELECT id, amount FROM commerce.sales.orders WHERE amount >= 100 AND status = 'paid' ORDER BY amount DESC LIMIT 25 OFFSET 5

Snowflake references

• Snowflake operators

• current-contract: Supported aggregate names are COUNT, SUM, AVG, MIN, and MAX. GROUP BY, single-input aggregate DISTINCT, HAVING, aliases, and ordinals are supported only within the bounded result-shaping contract below. COUNT_DISTINCT(column) remains a Loam-only compatibility alias for COUNT(DISTINCT column).

Examples

• SELECT region, SUM(amount), COUNT(*), COUNT_DISTINCT(customer_id) FROM commerce.sales.orders WHERE status = 'paid' GROUP BY region ORDER BY region LIMIT 100

Snowflake references

• Snowflake aggregate functions

• current-contract: Exactly two physical tables, one qualified equality predicate, qualified-column or * projection, and a positive LIMIT. Joined queries cannot use WHERE, GROUP BY, HAVING, or ORDER BY.

Examples

• SELECT o.id, c.segment FROM commerce.sales.orders o INNER JOIN commerce.sales.customers c ON o.customer_id = c.id LIMIT 100

Snowflake references

• Snowflake JOIN

• current-contract: SEMANTIC_SEARCH uses a configured embedding model. ANN accepts a literal query vector. Both are purpose-built top-level SELECT expressions rather than general SQL functions.

Examples

• SELECT SEMANTIC_SEARCH(content, 'renewal risk', limit = 10) FROM notes
• SELECT ANN(embedding, [0.1, 0.2, 0.3], limit = 10) FROM notes

Snowflake references

• Snowflake vector functions

• current-contract: INSERT accepts literal VALUES. UPDATE and DELETE target one physical table. Use operationKey when retrying an identical mutation.

Examples

• INSERT INTO commerce.sales.orders (id, amount) VALUES (1, 125)
• UPDATE commerce.sales.orders SET amount = 130 WHERE id = 1
• DELETE FROM commerce.sales.orders WHERE id = 1

Snowflake references

• Snowflake INSERT
• Snowflake UPDATE
• Snowflake DELETE

• current-contract: Low-cardinality aggregates stream. High-cardinality spill requires a positive LIMIT and currently supports SUM, COUNT, MIN, and MAX, but not AVG or COUNT_DISTINCT.
• spill-null-ordering: External sorting preserves each resolved key direction and NULL placement across forced disk-spill runs and returns the same exact order as in-memory sorting. Spill is a physical execution detail and does not create an order without ORDER BY.

Examples

• SELECT region, SUM(amount) FROM commerce.sales.orders GROUP BY region LIMIT 100

Named runtime evidence

• crates/loam-query-engine/src/sort/tests.rs::forced_spill_null_placement_matches_executor_for_every_direction
• crates/loam-query-engine/src/sort/tests.rs::forced_spill_mixed_multi_key_null_placement_matches_executor

Snowflake references

• Snowflake GROUP BY
• Snowflake ORDER BY
• Snowflake queries too large for memory

• current-contract: OVER definitions and QUALIFY predicates are rejected before execution. Ranking, LAG or LEAD, running totals, moving windows, and QUALIFY are not supported.

Examples

• SELECT ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) FROM orders
• SELECT id FROM orders QUALIFY ROW_NUMBER() OVER (ORDER BY created_at) = 1

Representative rejected statements

• SELECT SUM(amount) OVER (PARTITION BY customer_id) FROM orders;
• SELECT id FROM orders QUALIFY ROW_NUMBER() OVER (ORDER BY created_at) = 1;

Snowflake references

• Snowflake window functions
• Snowflake QUALIFY

• query-block-scope: The server-only SELECT surface supports the bounded query-block forms listed here. It is deliberately smaller than Snowflake; WASM remains analysis-only and does not execute query composition.
• nonrecursive-ctes: Non-recursive WITH binds named SELECT CTEs in declaration order, and each CTE may reference only earlier bindings. Self references, forward references, cycles, duplicate names, nested shadowing, and materialization modifiers fail closed. Recursive CTEs remain unsupported and are tracked as the separate LOA-383 production milestone.
• derived-tables: A derived SELECT in FROM requires a mandatory alias. Unaliased derived tables, LATERAL, and broader FROM composition remain unsupported.
• scalar-subquery-cardinality: An uncorrelated scalar subquery returns one column: zero rows produce typed NULL, one row produces its value, and multiple rows fail atomically with SCALAR_SUBQUERY_CARDINALITY.
• correlated-exists: EXISTS supports one correlation level with qualified equality predicates joined by AND. Non-equality, OR correlation, and LATERAL correlation fail planning.
• correlated-in: Correlated IN supports one correlation level with qualified equality and statically non-null single-column comparison values. Nullable IN, NOT IN, or row-valued IN, plus ANY and ALL, remain unsupported and fail planning. Any future nullable subquery IN promotion is owned outside LOA-373 and remains gated on a reviewed live Snowflake null and empty-set truth-table fixture plus normative tests.
• correlated-scalar: One-level qualified equi-correlated WHERE scalar subqueries support exactly one simple Float64 SUM, MIN, or MAX over one local column as an aggregate without GROUP BY or HAVING, using exact ordered keys, spill-capable keyed aggregation, and nullable Float64 output with typed NULL for a missing or null-only key. Correlated AVG, COUNT, COUNT_DISTINCT, non-Float64, computed, grouped, wrapped, and multi-measure shapes fail planning.
• recursive-leaf-acl: The engine authorizes every base table in every nested block before object reads, then pins one atomic catalog snapshot and holds its read lease until execution stops.
• bounded-query-block-memory: All admitted blocks and intermediates share the statement memory and spill scope; limit, capacity, cancellation, and deadline failures clean up atomically without partial results. Set operations remain unsupported.

Examples

• WITH recent AS (SELECT * FROM orders WHERE created_at >= '2026-01-01') SELECT customer_id, SUM(amount) FROM recent GROUP BY customer_id;
• SELECT * FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) totals WHERE total > 100;
• SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
• SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);
• SELECT c.id FROM customers c WHERE c.id IN (SELECT o.order_id FROM orders o WHERE o.order_id = c.id);
• SELECT o.order_id FROM orders o WHERE o.amount < (SELECT SUM(i.amount) FROM orders i WHERE i.customer_id = o.customer_id);
• SELECT o.order_id FROM orders o WHERE o.amount < (SELECT MIN(i.amount) FROM orders i WHERE i.customer_id = o.customer_id);
• SELECT o.order_id FROM orders o WHERE o.amount < (SELECT MAX(i.amount) FROM orders i WHERE i.customer_id = o.customer_id);

Representative rejected statements

• WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM r) SELECT n FROM r;
• WITH recent AS (SELECT * FROM recent) SELECT * FROM recent;
• WITH first AS (SELECT * FROM second), second AS (SELECT * FROM orders) SELECT * FROM first;
• WITH first AS (SELECT * FROM orders), FIRST AS (SELECT * FROM orders) SELECT * FROM first;
• WITH recent AS (SELECT * FROM orders) SELECT * FROM (WITH recent AS (SELECT * FROM orders) SELECT * FROM recent) nested;
• WITH recent AS MATERIALIZED (SELECT * FROM orders) SELECT * FROM recent;
• SELECT * FROM (SELECT id FROM orders);
• SELECT * FROM orders o, LATERAL (SELECT o.id) nested;
• SELECT (SELECT id, amount FROM orders) FROM customers;
• SELECT c.id FROM customers c WHERE c.id = (SELECT o.customer_id FROM orders o WHERE o.customer_id = c.id);
• SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id > c.id);
• SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id OR o.amount > 100);
• SELECT c.id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id ORDER BY o.order_id LIMIT 1);
• SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
• SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM orders);
• SELECT * FROM orders WHERE NULL IN (SELECT customer_id FROM orders);
• SELECT * FROM orders WHERE (customer_id, order_id) IN (SELECT customer_id, order_id FROM orders);
• SELECT * FROM orders WHERE amount > ANY (SELECT amount FROM orders);
• SELECT * FROM orders WHERE amount > ALL (SELECT amount FROM orders);
• SELECT o.order_id FROM orders o WHERE o.amount < (SELECT SUM(i.amount) FROM orders i WHERE i.customer_id = o.customer_id GROUP BY i.customer_id);
• SELECT o.order_id FROM orders o WHERE o.amount < (SELECT SUM(i.amount), MAX(i.amount) FROM orders i WHERE i.customer_id = o.customer_id);
• SELECT id FROM orders UNION ALL SELECT id FROM archived_orders;
• SELECT id FROM orders INTERSECT SELECT id FROM archived_orders;
• SELECT id FROM orders EXCEPT SELECT id FROM archived_orders;
• SELECT id FROM orders MINUS SELECT id FROM archived_orders;
• SELECT * FROM orders o WHERE (SELECT AVG(i.amount) FROM orders i WHERE i.customer_id = o.customer_id) IS NOT NULL;
• SELECT * FROM orders o WHERE o.amount > (SELECT COUNT(*) FROM orders i WHERE i.customer_id = o.customer_id);
• SELECT * FROM orders o WHERE (SELECT SUM(i.customer_id) FROM orders i WHERE i.customer_id = o.customer_id) IS NOT NULL;

Named runtime evidence

• crates/loam-integration/tests/subquery_larger_than_memory.rs::persistent_exact_four_queries_match_in_memory_oracle
• crates/loam-integration/tests/subquery_larger_than_memory.rs::persistent_sources_share_atomic_snapshot_and_hold_read_leases
• crates/loam-integration/tests/subquery_larger_than_memory.rs::persistent_two_source_lease_fences_replacement_gc_until_query_finishes
• crates/loam-integration/tests/subquery_larger_than_memory.rs::persistent_composition_bounds_memory_and_cleans_spill_on_all_exits
• crates/loam-integration/tests/query_composition_production.rs::exact_ticket_statements_and_subquery_semantics_work_on_production_backend
• crates/loam-integration/tests/query_composition_production.rs::exact_ticket_statements_work_on_authenticated_production_http_route
• crates/loam-integration/tests/query_composition_production.rs::actual_engine_startup_reclaims_only_stale_owned_spills
• crates/loam-integration/tests/query_composition_production.rs::actual_engine_http_spill_quota_errors_are_atomic_and_recoverable
• crates/loam-integration/tests/query_composition_production.rs::persistent_spill_quota_errors_are_atomic_and_recoverable
• crates/loam-integration/tests/query_composition_production.rs::persistent_global_spill_capacity_is_shared_across_concurrent_requests
• crates/loam-integration/tests/query_composition_production.rs::persistent_composition_authorizes_every_nested_leaf_before_pinning
• crates/loam-wasm/src/lib.rs::loa_373_analysis_json_names_the_explicit_server_only_boundary
• crates/loam-integration/tests/query_composition_production.rs::recursive_and_nullable_composition_shapes_fail_closed
• crates/loam-compute/src/relational/lower.rs::correlated_scalar_null_identity_aggregates_preserve_typed_nulls
• crates/loam-compute/src/relational/lower.rs::correlated_average_fails_closed_before_non_spillable_execution
• crates/loam-compute/src/relational/lower.rs::correlated_count_fails_closed_until_missing_keys_can_emit_zero
• crates/loam-compute/src/relational/lower.rs::correlated_integer_aggregates_fail_closed_before_lossy_execution

Snowflake references

• Snowflake CTEs
• Snowflake subqueries
• Snowflake subquery operators
• Snowflake [NOT] IN
• Snowflake ternary logic

• current-contract: LEFT, RIGHT, FULL, CROSS, NATURAL, lateral, ASOF, non-equality, multi-predicate, multi-join, unbounded, filtered, grouped, and ordered join queries are not supported.

Examples

• SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id
• SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE o.amount > 100 LIMIT 100

Representative rejected statements

• SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id LIMIT 100

Snowflake references

• Snowflake JOIN

• projection-arithmetic: The documented arithmetic subset is +, -, *, and / for the tested Int64 and finite Float64 values, with NULL propagated through the result. Modulo and other arithmetic operators are outside this initial subset and fail closed.
• projection-searched-case: Searched CASE evaluates WHEN arms in order, selects only TRUE, and uses ELSE for FALSE or UNKNOWN.
• projection-cast-date: The documented CAST subset is CAST(timestamp AS DATE), which returns a nullable DATE value. Other CAST shapes are outside this public support contract and must not be relied on as supported.
• projection-aliases: Aliases on supported projection expressions are preserved as output column names.
• projection-expression-order: ORDER BY accepts supported expressions and one unambiguous projected alias, matched ASCII case-insensitively. Duplicate aliases or alias-to-column collisions are rejected as ambiguous. Implicit ordering keeps NULL last; explicit NULLS FIRST or NULLS LAST remains unsupported.
• scalar-lower: LOWER accepts exactly one positional String argument, returns a lowercase String, and propagates NULL.
• scalar-upper: UPPER accepts exactly one positional String argument, returns an uppercase String, and propagates NULL.
• scalar-length: LENGTH accepts exactly one positional String argument, returns its character count as Int64, and propagates NULL.
• scalar-abs: ABS accepts exactly one positional numeric argument, returns its absolute value, and propagates NULL.

Examples

• SELECT amount * 1.2 AS gross_amount, CASE WHEN status = 'paid' THEN amount ELSE 0 END AS paid_amount, CAST(created_at AS DATE) AS order_date FROM orders
• SELECT LOWER(email) AS normalized_email, UPPER(region), LENGTH(customer_id), ABS(amount - 150) FROM orders

Representative rejected statements

• SELECT amount % 2 AS remainder FROM orders;

Named runtime evidence

• crates/loam-compute/tests/analytical_expressions.rs::ticket_projection_statement_preserves_values_nulls_types_and_aliases
• crates/loam-compute/tests/analytical_null_semantics.rs::arithmetic_operators_propagate_null_and_preserve_aliases
• crates/loam-compute/tests/analytical_null_semantics.rs::modulo_outside_the_initial_arithmetic_surface_fails_closed
• crates/loam-compute/tests/analytical_expressions.rs::expression_order_by_sorts_computed_values_and_preserves_alias
• crates/loam-compute/tests/analytical_expressions.rs::projected_alias_can_be_referenced_from_order_by
• crates/loam-planner/src/convert/tests.rs::planner_rejects_ambiguous_order_alias
• crates/loam-planner/src/convert/tests.rs::planner_resolves_order_alias_case_insensitively
• crates/loam-compute/tests/analytical_expressions.rs::documented_initial_scalar_functions_execute_with_aliases_types_and_nulls

Snowflake references

• Snowflake arithmetic operators
• Snowflake CASE
• Snowflake CAST
• Snowflake SELECT aliases
• Snowflake ORDER BY
• Snowflake LOWER
• Snowflake UPPER
• Snowflake LENGTH
• Snowflake ABS

• current-contract: The initial general scalar-function set is exactly LOWER, UPPER, LENGTH, and ABS. Other names, named arguments, wrong arity, and statically incompatible argument types are rejected before execution.

Examples

• SELECT SQRT(amount) FROM orders
• SELECT LOWER(1) FROM orders

Representative rejected statements

• SELECT SQRT(amount) FROM orders;
• SELECT LOWER(1) FROM orders;
• SELECT LOWER() FROM orders;
• SELECT LOWER(value => email) FROM orders;

Named runtime evidence

• crates/loam-compute/tests/analytical_error_contract.rs::unsupported_scalar_functions_fail_as_parse_errors

Snowflake references

• Snowflake function reference

• predicate-or: OR follows SQL three-valued logic.
• predicate-in: IN compares an expression with a literal list and preserves UNKNOWN when NULL participates without a match.
• predicate-not-in: NOT IN negates only TRUE or FALSE; a NULL operand or unmatched list containing NULL remains UNKNOWN.
• predicate-between: BETWEEN and NOT BETWEEN support compatible bounds, include both endpoints, and preserve NULL as UNKNOWN. A date-looking timestamp literal denotes midnight, so use a half-open bound such as created_at >= '2026-03-31' AND created_at < '2026-04-01' to include the whole day.
• predicate-is-null: IS NULL returns a non-null boolean.
• predicate-is-not-null: IS NOT NULL returns a non-null boolean.
• predicate-like: LIKE is case-sensitive and matches the whole string with % and _ wildcards; NULL remains UNKNOWN.
• predicate-not-like: NOT LIKE negates only a non-null LIKE result; NULL remains UNKNOWN.
• predicate-column-comparison: Compatible column-to-column comparisons execute; incompatible types return a typed query error without results.
• predicate-expression-comparison: Compatible nested expression comparisons execute and propagate NULL.
• predicate-null-semantics: WHERE retains only TRUE and excludes both FALSE and UNKNOWN for these predicates.

Examples

• SELECT * FROM orders WHERE (status = 'paid' OR status = 'refunded') AND region IN ('NZ', 'AU') AND created_at BETWEEN '2026-01-01' AND '2026-03-31' AND deleted_at IS NULL AND email LIKE '%@example.com'

Named runtime evidence

• crates/loam-compute/tests/analytical_expressions.rs::ticket_predicate_statement_uses_sql_three_valued_logic
• crates/loam-compute/tests/analytical_null_semantics.rs::analytical_predicates_keep_only_true_and_never_unknown_rows
• crates/loam-compute/tests/analytical_expressions.rs::compatible_predicates_execute_and_incompatible_types_fail_at_the_typed_boundary

Snowflake references

• Snowflake ternary logic
• Snowflake IN
• Snowflake BETWEEN
• Snowflake IS NULL
• Snowflake LIKE

• row-distinct: SELECT DISTINCT removes duplicates using the complete projected row across all input batches; NULL values in the same field form one duplicate class. DISTINCT does not imply output order. DISTINCT ON, nested projected values, and ORDER BY dependencies absent from the visible DISTINCT result are rejected.
• aggregate-distinct: COUNT, SUM, AVG, MIN, and MAX accept DISTINCT on exactly one supported input expression. NULL input is excluded; COUNT returns zero for empty or all-NULL input, SUM and AVG return NULL, and MIN or MAX DISTINCT has the same result as its non-DISTINCT form. Snowflake's multi-expression tuple form, COUNT(DISTINCT *), DISTINCT expressions outside the bounded column form, and DISTINCT window aggregates are rejected.
• count-distinct-compatibility-alias: COUNT_DISTINCT(column) is a Loam-only compatibility alias for the standard COUNT(DISTINCT column) spelling. Both count unique non-NULL values and return Int64 zero for empty or all-NULL input; prefer the standard spelling.
• aggregate-having: HAVING runs after grouping and aggregate materialization and keeps only TRUE rows; FALSE and SQL UNKNOWN are removed. Constants, grouped outputs, supported aggregate calls, and exactly resolved SELECT aliases are accepted, while non-grouped row columns, unresolved inputs, and unsupported expressions fail planning.
• aggregate-aliases: Unquoted aggregate and grouping aliases are preserved and resolve ASCII case-insensitively in supported GROUP BY, HAVING, and ORDER BY clauses only when exactly one target matches. Duplicate aliases and alias-to-source-column collisions are rejected instead of using Snowflake's broader precedence rules.
• aggregate-ordinals: Positive integers in top-level GROUP BY and ORDER BY are one-based SELECT-list positions. GROUP BY positions must resolve to a supported non-aggregate expression; zero, negative, non-integer, out-of-range, wildcard, and aggregate grouping positions fail closed.
• explicit-null-ordering: NULLS FIRST and NULLS LAST are honored independently of ASC or DESC for each supported ORDER BY key in both in-memory and external spill sorting. Explicit placement survives aliases, aggregate ordering, multi-key ordering, LIMIT handling, and forced spill.
• default-null-ordering: When NULLS FIRST or NULLS LAST is omitted, Loam fixes the policy equivalent to Snowflake DEFAULT_NULL_ORDERING=LAST: ASC places NULL last and DESC places NULL first. Unlike Snowflake, Loam does not expose a session parameter to change this default.

Examples

• SELECT DISTINCT customer_id FROM orders;
• SELECT COUNT(DISTINCT customer_id) FROM orders;
• SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 100 ORDER BY total DESC NULLS LAST;
• SELECT region, SUM(amount) AS total FROM orders GROUP BY 1 ORDER BY 2 DESC;

Representative rejected statements

• SELECT DISTINCT ON (region) region FROM orders;
• SELECT COUNT(DISTINCT customer_id, region) FROM orders;
• SELECT COUNT(DISTINCT *) FROM orders;
• SELECT customer_id AS region FROM orders ORDER BY region;
• SELECT region, SUM(amount) FROM orders GROUP BY 3;
• SELECT region, SUM(amount) AS total FROM orders GROUP BY region HAVING amount > 1;

Named runtime evidence

• crates/loam-compute/tests/distinct_having_ordering.rs::exact_distinct_statements_deduplicate_rows_and_exclude_null_from_count
• crates/loam-compute/tests/distinct_having_ordering.rs::all_standard_aggregate_distinct_modifiers_deduplicate_and_ignore_null_across_batches
• crates/loam-compute/tests/distinct_having_ordering.rs::exact_having_statement_keeps_true_only_and_preserves_alias
• crates/loam-compute/tests/distinct_having_ordering.rs::aliases_resolve_in_group_by_having_and_order_by
• crates/loam-compute/tests/distinct_having_ordering.rs::exact_ordinal_statement_groups_and_orders_by_select_positions
• crates/loam-compute/tests/distinct_having_ordering.rs::explicit_nulls_first_and_last_are_honored_for_aggregate_ordering
• crates/loam-compute/tests/distinct_having_ordering.rs::omitted_null_ordering_uses_snowflake_default_last_session_semantics
• crates/loam-compute/tests/grouping_error_contract.rs::unsupported_distinct_shapes_fail_before_execution
• crates/loam-compute/tests/grouping_error_contract.rs::ambiguous_or_missing_alias_references_are_plan_errors
• crates/loam-compute/tests/grouping_error_contract.rs::invalid_group_and_order_ordinals_fail_before_execution
• crates/loam-compute/tests/grouping_error_contract.rs::invalid_having_references_and_types_are_plan_errors
• crates/loam-executor/tests/distinct_semantics_oracle.rs::row_distinct_uses_complete_mixed_row_identity_across_batches
• crates/loam-executor/tests/distinct_semantics_oracle.rs::aggregate_distinct_ignores_nulls_and_deduplicates_across_batches_per_group

Snowflake references

• Snowflake SELECT
• Snowflake COUNT
• Snowflake SUM
• Snowflake AVG
• Snowflake MIN
• Snowflake MAX
• Snowflake HAVING
• Snowflake GROUP BY
• Snowflake ORDER BY

• current-contract: LIKE ANY, LIKE with ESCAPE, ILIKE, SIMILAR TO, and regular-expression predicates are not supported. Ordinary case-sensitive LIKE and NOT LIKE with % and _ are supported.

Examples

• SELECT * FROM orders WHERE email ILIKE '%@example.com'
• SELECT * FROM orders WHERE email LIKE '%!_%' ESCAPE '!'

Representative rejected statements

• SELECT * FROM orders WHERE email ILIKE '%@example.com'
• SELECT * FROM orders WHERE email LIKE '%!_%' ESCAPE '!'

Snowflake references

• Snowflake LIKE ANY
• Snowflake ILIKE
• Snowflake regular expressions

• current-contract: ROLLUP, CUBE, GROUPING SETS, SQL PIVOT and UNPIVOT, MATCH_RECOGNIZE, TABLESAMPLE, and historical AT or BEFORE reads are not supported.

Examples

• SELECT region, SUM(amount) FROM orders GROUP BY ROLLUP(region)
• SELECT * FROM orders PIVOT(SUM(amount) FOR region IN ('NZ', 'US'))

Representative rejected statements

• SELECT region, SUM(amount) FROM orders GROUP BY ROLLUP(region)
• SELECT * FROM orders PIVOT(SUM(amount) FOR region IN ('NZ', 'US'))

Snowflake references

• Snowflake GROUP BY
• Snowflake PIVOT

• current-contract: INSERT ... SELECT, MERGE, UPDATE ... FROM, DELETE ... USING, CREATE TABLE ... AS SELECT, explicit multi-statement transactions, stored procedures, and UDFs are not supported.

Examples

• INSERT INTO target SELECT * FROM source
• MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET amount = source.amount
• CREATE TABLE summary AS SELECT region, SUM(amount) FROM orders GROUP BY region

Representative rejected statements

• INSERT INTO target SELECT * FROM source
• MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET amount = source.amount
• CREATE TABLE summary AS SELECT region, SUM(amount) FROM orders GROUP BY region

Snowflake references

• Snowflake MERGE
• Snowflake transactions