NULL Values ============ SQL's NULL comes with famously subtle semantics: predicates over NULLs follow a three-valued logic, set operations switch to a different notion of equality, aggregates skip NULL inputs, and queries that look equivalent stop being equivalent the moment a NULL appears. ProvSQL tracks provenance *through* these rules: the rewritten query returns what vanilla PostgreSQL returns, and the provenance and probabilities it computes are correct under SQL's own NULL semantics. This chapter states the rules ProvSQL follows and the few places where its behaviour is a deliberate choice. What NULL Means Here --------------------- ProvSQL implements the *SQL-operational* reading of NULL: a NULL is a value like any other, predicates over it evaluate to *unknown* per SQL's three-valued logic, and a row whose condition is unknown is not an answer. ProvSQL does **not** interpret NULL as an unknown quantity ranging over possible values (the "incomplete information" reading); NULL stays NULL in every possible world. Two rules pin the semantics down: * **Selection rule.** Every condition is evaluated under SQL's three-valued logic on the actual data; a tuple whose condition evaluates to *unknown* or *false* is not an answer: depending on the construct it is either removed from the result or kept with the semiring **zero** as its annotation, which is equivalent to absence (see `Zero-Annotated Rows May Stay Visible`_ below). Unknown never becomes "maybe" in the annotation, and never silently becomes "true". * **Possible-worlds criterion.** For Boolean provenance (and everything built on it: probabilities, Shapley values, knowledge compilation), a tuple's annotation must satisfy: the tuple is in the query answer over a world *W* -- evaluated under SQL's own semantics, NULLs included -- exactly when *W* satisfies the tuple's Boolean provenance. Behaviour by Construct ---------------------- **WHERE, JOIN, and HAVING predicates.** PostgreSQL itself evaluates deterministic predicates, three-valued logic included; ProvSQL only combines the tokens of rows that survive. ``WHERE b > 5`` drops rows with a NULL ``b``, a NULL join key matches nothing, and their tokens simply never enter the circuit. **IN and EXISTS.** ``x IN (SELECT …)`` with no true match makes the row a non-answer whether the comparison is false or unknown -- that is SQL's own top-level rule, and the annotation is zero either way. ``EXISTS`` / ``NOT EXISTS`` only ever ask whether a *true* match exists, so they compose correctly with NULLs as-is. **NOT IN and quantified comparisons** (``op ALL``). Negation is where NULLs bite: ``x NOT IN Q`` is *unknown* -- hence not an answer -- as soon as ``Q`` contains a NULL (or ``x`` is NULL and ``Q`` is non-empty). ProvSQL accounts for this: an unknown comparison counts as a match, so a subquery row with a NULL removes the outer row in every world containing it. Consequently, ``NOT IN``, ``NOT EXISTS``, and ``EXCEPT`` -- equivalent on NULL-free data -- get genuinely different provenance on data with NULLs, matching their different SQL answers. (See Step 12 of :doc:`case study 5 ` for the three side by side.) **Set operations, DISTINCT, GROUP BY.** SQL deliberately switches to *syntactic* equality here: two NULLs are the same value. ``DISTINCT`` and ``GROUP BY`` collapse NULL keys into one group whose annotation is the ⊕ of all contributing tokens; ``UNION`` merges NULL rows from both sides; ``EXCEPT`` matches NULL rows on the two sides against each other (its antijoin uses ``IS NOT DISTINCT FROM`` matching); ``INTERSECT`` matches them likewise, two NULL rows being the same row. **Aggregates.** ``sum`` / ``avg`` / ``min`` / ``max`` skip NULL inputs; ``count(col)`` counts non-NULL values while ``count(*)`` counts rows; an all-NULL (or empty) group yields a NULL aggregate whose formula readout is NULL as well. The same holds for aggregates over ``random_variable`` columns: a NULL cell contributes to neither the sum nor ``avg``'s count. **HAVING over a NULL aggregate.** A comparison against a NULL aggregate never passes -- neither on the actual instance (the group's annotation is zero) nor in any possible world where the aggregate comes out NULL (a world with no contributing row). ``HAVING IS [NOT] NULL`` is fully supported across possible worlds: ``sum(b) IS NULL`` holds in exactly the worlds where the group exists but no non-NULL-valued row is present. The same holds for an expression over aggregates (``CASE``, ``GREATEST`` / ``LEAST``, ``COALESCE``, ``NULLIF``, arithmetic): ``HAVING GREATEST(sum(b), min(b)) IS NULL`` holds in the worlds where the group has rows but none with a value. For a comparison over such an expression, see :ref:`what a guarded selection carries `. **Outer joins.** A LEFT/RIGHT/FULL JOIN between tracked relations gives its NULL-padded rows the provenance of the absence of a match (a monus). An untracked relation joined to a tracked one counts as certain, each of its rows present in every world: ``region LEFT JOIN deal`` keeps every region, padded with NULLs in the worlds where none of its deals is present. An outer join whose null-padded side is entirely *untracked* is also fine. An outer join with a tracked relation on a null-padded side is refused in a query with a ``LATERAL`` item, or when a column merged by ``USING`` / ``NATURAL`` is read through the join. **Comparisons on NULL random variables.** A comparison involving a NULL ``random_variable`` -- a NULL constant or a NULL cell -- is unknown in every world: the row is annotated zero (probability 0). ``IS NULL`` on a ``random_variable`` column is an ordinary deterministic predicate. (See Step 19 of :doc:`case study 6 `.) Zero-Annotated Rows May Stay Visible ------------------------------------- A row whose annotation is the semiring zero is equivalent to an absent row, and ProvSQL does not always filter such rows out: queries may return rows that vanilla SQL does not, whose annotation evaluates to zero, such as the antijoin arm of a difference (a row removed *on this instance* but present in worlds where its remover is absent) and ``HAVING`` groups that fail the predicate on this instance but pass it in other worlds. Which zero rows stay visible is not something to rely on. When the vanilla result set is wanted, filter explicitly with :sqlfunc:`present`: .. code-block:: postgresql SELECT * FROM (...) t WHERE provsql.present(provenance()); ``present(token)`` is true exactly when the row is in the answer the query has without provenance tracking (the circuit evaluated in the Boolean semiring with every leaf true). Its general form, :sqlfunc:`nonzero`, tests the annotation against a semiring's zero: ``'boolean'`` as above, ``'counting'`` for bag multiplicity, and -- with ``semiring`` omitted -- a *universal* zero test that is false only when the annotation is provably zero in every (m-)semiring under every leaf valuation, so filtering on it can never contradict any downstream evaluation. Both keep rows they cannot prove zero (and untracked rows, whose NULL token reads as the neutral 1); the cost is one circuit evaluation per candidate row. NULL Provenance Tokens ----------------------- Data NULLs are not the only NULLs around: a *provenance token* slot can itself be NULL, and each combinator maps a NULL token to its own neutral element: .. list-table:: :header-rows: 1 :widths: 30 20 50 * - Combinator - NULL token reads as - Situation that produces it * - ``provenance_times`` (⊗) - 1 (neutral) - untracked source in a join * - ``provenance_plus`` (⊕) - 0 (neutral) - padded/absent row's slot in a disjunction * - ``provenance_monus`` (⊖, right side) - 0 (neutral) - antijoin with no matching right rows * - ``provenance_delta`` (δ) - 1 - untracked source In particular, **a NULL token never means "false"**: whenever a condition is unknown or fails, ProvSQL produces an explicit zero annotation, never a NULL token, which ⊗ would read as *certainly true*. Unset Probabilities Are Certain -------------------------------- An input token that was never given a probability with :sqlfunc:`set_prob` is treated as **certain** (probability 1.0) by :sqlfunc:`probability_evaluate` and everything downstream, since in typical deployments most tuples are deterministic and only a few tables are probabilistic. :sqlfunc:`get_prob`, however, returns NULL for such a token ("never set"). Empty Groups in Evaluated Worlds --------------------------------- Probability and moment computations over aggregates follow these conventions in worlds where a group has no contributing row: * A ``HAVING`` comparison never passes in a world where the aggregate is NULL (no contributing row for ``sum``/``avg``/``min``/``max``). * The moment readouts (:sqlfunc:`expected` and friends) over aggregate-valued arguments are **conditional on the aggregate being defined**, returning NULL only when the aggregate is defined with probability 0. ``sum``, ``avg``, ``min``, ``max``, and a ``count`` under ``GROUP BY`` are defined exactly when some contributing row is present; a ``count`` without ``GROUP BY`` is defined in every world, 0 where no row is present. Wrap the aggregate in ``coalesce`` to count an empty group as 0 instead, e.g., ``expected(coalesce(sum(a), 0))``.