Re: Key joins

On Sun, May 31, 2026, at 16:40, Joel Jacobson wrote:
> On Sun, May 31, 2026, at 10:05, Joel Jacobson wrote:
>> On Fri, May 29, 2026, at 18:20, Laurenz Albe wrote:
>>> On Fri, 2026-05-29 at 15:21 +0200, Joel Jacobson wrote:
>>>> We actually originally considered TO and FROM as keywords for indicating
>>>> direction, but FROM in a join clause causes confusion with the FROM
>>>> clause itself.  Our user discussions over the last three years indicates
>>>> that arrows are clearer and less ambiguous.
>>>>
>>>> It's also worth to mention that SQL/PGQ also uses ASCII arrows to
>>>> indicate direction for its graph pattern syntax.
>>>
>>> I understand the problem with FROM, and I have no objection to the
>>> arrows.
>>
>> Thanks for reviewing.
>>
>> v7 updates 0002 to match the revised Change Proposal wording for
>> GROUPING SETS/ROLLUP/CUBE:
>
> v8 fixes another nondeterministic isolation-test ordering issue seen by
> cfbot on the FreeBSD machine.
>
> 0001 and 0003 are unchanged from v7.

I noted a small error in 7.4.13 in the paper. The examples incorrectly
used "LEFT JOIN" instead of "JOIN", which made the claim "Both queries,
if accepted, would produce the same result rows" to not hold true.

Fixed subsection below:

===

7.4.13 Filtered Views and the PK Side

A key join through a filtered view on the PK side is rejected unless the
FK side supplies matching direct key-equality filter evidence; see
Section 7.3.2. This differs from a query that filters the base table
with a WHERE clause after the join:

For the examples below, assume orders has a NOT NULL customer_id that
references customers (id).

-- Accepted: join the full base table, then filter the result.
SELECT *
FROM orders AS o
JOIN customers AS c FOR KEY (id) <- o (customer_id)
WHERE c.active;

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE active;

-- Rejected: the view is the PK side and lacks row coverage.
SELECT *
FROM orders AS o
JOIN active_customers AS ac FOR KEY (id) <- o (customer_id);
ERROR: key join from referencing relation o to referenced relation ac cannot be proven
ERROR: key join from referencing relation o to referenced relation ac cannot be proven
LINE 3: JOIN active_customers AS ac FOR KEY (id) <- o (customer_id);
^
DETAIL: Not every o (customer_id) value can be proven to have a matching ac row. Referenced relation ac is filtered before this key join. The relevant operation occurs inside view public.active_customers.

Both queries, if accepted, would produce the same result rows, but would
differ from the key join's perspective. In the first query, the join is
against the full customers table: every order is guaranteed to find its
matching customer, and the WHERE clause filters the result after the
join has been evaluated. In the second query, active_customers is the PK
side, and it is missing inactive customers. An order that references an
inactive customer will not find a match.

This asymmetry is inherent in row coverage. A key join guarantees that
the join will behave as a proper foreign key traversal, producing
exactly one match for each FK row. A filtered PK side breaks this
guarantee unless the missing PK values are matched by filters that
remove the corresponding FK rows before the join.

===

/Joel