Settled, Known Limitation, Unspecified¶

• Rulings: semantics undertsood and supported
• Known Limitations: semantics understood but there is too much current cost to implement
• Unspecified: semantics not understood

This reference is an attempt to enumerate all semantics and the syntax that manifests those semantics. These we may call rulings.

The enumeration of these rulings may be incomplete. A language that is designed of composable units may have compositions that create unsettled semantics.

To better specify delightql, the enumeration of known limitations and/or unspecified behavior creates both an acknowledgment of boundaries and an inventory for future work.

This section attempts the enumeration of the negative space where delightql has knowingly refused to support something or has thrown up its hands and said “this is too hard for the moment.”

Unspecified behaviour¶

Categorizing functionality or code as “unspecified behavior” leaves a clear warning to the programmer.

The contents of this category are places where delightql or SQL or both have no clear idea as to what the proper semantics should be.

This does not preclude a latter enlightment or clarification.

Language Limitations¶

Delightql has limitations on its expressive power and its expressive guarantees.

The source of these limitations can be any of the following:

• a missing feature in SQL itself
• a feature that is inconsistently implemented across SQL targets
• a feature from logic that has no expressive abstraction at all in SQL
• a feature that is possible in SQL but requires an admixture of DDL and DML and querying
• any feature that does not yet have a satisfactory semantics

The last point expresses the fact that delightql may choose to not support a feature at the current time while leaving the door open for its support upon future semantic clarity. This is a statement that can apply to all SQL targets or to selective SQL targets.

The current section aims to enumerate all known limitations.

Higher-Order Moding Limitations¶

Because there is no clear standard in SQL with regards to higher-order tables, delightql has two paths whenever a higher-order abstraction is used or invoked:

• it can lower the abstraction directly to the SQL target via a default transpilation rules
• it can rewrite with a detail intended semantics into a composition of normal relational values

The former states that if delightql sees:

json_tree("[1, 2]")(*)

it will lower to:

select * from json_tree('[1, 2]');

which is useful for well-known table-value functions like json_each.

The latter is exclusively the concern of the transpiler when the higher-order rule is programmer authored.

Programmer authored¶

Non-deterministic functions¶

Delightql makes NO guarantee about impure function-level referential transparency or delimited referential transparency. The rest of this section details the challenges that will need to be overcome to permit any future guarantees.

True referential transparency is a strong guarantee: that a function with the exact same arguments may be substituted with the one value it produces in all situations.

Delimited referential transparency is a looser guarantee: that within one calling context, a function form with the exact same arguments may be substituted with the one value it produces.

Non-deterministic functions like certain vendors’ random() may not have clearly defined semantics around delimited referential transparency.

To motivate, in SQLite SQL, a random value across many rows:

with
  _x as ( VALUES (1), (2), (3))
select random() as r from _x;

feels correct, as does when calling random twice in the same expression:

with
  _x as ( VALUES (1), (2), (3))
select random() as r1, random() as r2 from _x;

When we desire our expected flavor of delimited referential transparency, the random function “materialized” once seems to work like we think it should:

with
  _random as (
    select random() as r
      union all
    select random() as r)
select
    r,
    r+1 as r_plus_one
  from _random;

select
    r,
    r+1 as r_plus_one
  from
  (
    select random() as r
      union all
    select random() as r)

but when SQLite’s rules about flattening mark the expression differently, we may not get what we think we are going to get:

WITH input(n) AS (
  VALUES (1), (2), (3)
)
SELECT r AS first_read, r AS second_read
FROM (
  SELECT random() AS r
  FROM input
);

SQLite does, however, adhere to a certain form of referential transparency for those non-deterministic functions that are part of the SQL standard: CURRENT_TIME,CURRENT_DATE,CURRENT_TIMESTAMP.

The difference in this behaviour can be understood by the taxonomic Postgres terminology:

• IMMUTABLE: a function is guaranteed to be referentially transparent in all situations
• STABLE: a function is guaranteed to be referentially transparent within one context
• VOLATILE: a function is not guaranteed to be referentially transparent

NOTE: the term “context” may mean different things between different SQL targets: for some, at the unit of a query, for others at the unit of a transaction.

Delightql’s challenge¶

Delightql has several challenges in this corner of the SQL world:

• is there a semantics that can be guaranteed equivalent across SQL targets?
• can the standard semantic of “one delightql query expression = one SQL query expression” be maintained?
• is the IMMUTABLE/STABLE/VOLATILE taxonomy a suitable standard for coloring SQL functions?
• can rewrite equivalences match any expected semantics?

Cross-target¶

The SQLite example above shows that its implementations of the functions random() and current_timestamp() are VOLATILE and STABLE respectively. That the function current_timestamp is part of the SQL standard implies that SQL vendors should maintain STABLE semantics for these.

A matrix of implementation strategies across vendors shows that even the STABLE semantic comes with some vendor-specific caveats:

The challenge therefore may be answered with vendor-specific transpilation using correct transactional boundaries.

Query-only Semantics and Materialization¶

The primary semantic that delightql guarantees is that all pure queries are equivalent to the SQL string that they produce. That delightql does not have a direct mapping between query string to query string when directives are utilized is not a contradiction: directives are impure and are marked as such (!).

Therefore if delightql wishes to support support referential transparency for functions syntactically marked as pure:

_(a@1;2;3) as anon
    |> +(random:() as r)
    |> (r, r+1)

it cannot result in an admixture of DDL and SQL:

BEGIN;

CREATE TEMPORARY TABLE __dql_stage AS
WITH input(a) AS (
  VALUES (1), (2), (3), (3)
)
SELECT
  input.a,
  random() AS r
FROM input;

SELECT
  a,
  r,
  r + 1
FROM __dql_stage;

DROP TABLE __dql_stage;

COMMIT;

If delightql eventually does allow for these impure functions, it will require marking them as impure:

_(a@1;2;3) as anon
    |> +(random!:() as r)
    |> (r, r+1)

which will

• give a syntactic indication to the programmer that both the primary semantic is void and that a new (to-be-determined) referential semantic is now in force
• instruct the delightql compiler to create an effect plan of DDL and transactions

This remains a challenge under the current epistemic regime whereby unknown functions are default transpiled into the target SQL:

employee(*) |> +( do_it:(last_name) as di)

select *, do_it(last_name) as di from employee;

The IMMUTABLE/STABLE/VOLATILE¶

Relational Equational Rewriting¶

Recursion Limitations¶

Autogenerated Names¶

JSON-awareness and cataloging¶

partner_sale(*)
  |> (sale_id, payload:{.buyer} as buyer)
  |> %(sale_id ~> max:(buyer) as buyer)
  |> (sale_id, {"buyer": buyer} as packet)

Today, without the max step, the buyer re-enters the packet as an object (sale 1):

{"buyer":{"email":"[email protected]","country":"BR"}}

With the max step it re-enters as a string:

{"buyer":"{\"email\":\"[email protected]\",\"country\":\"BR\"}"}

This is a known limitation.

Lateral Join Limitations¶

An inner-relation can only reference an outer relation prior to a scope barrier.