coord: Implement introspection source migrations - #2

Turns out that having even a single row in the source can
cause the non-determinism observed in #16048 to show up.

Therefore, use a source with no rows for the affected portions
of the test.

Relates to: #16048

…-sources-td-try-2

testdrive: Fortify cvs-sources.td against #16048 (Part #2)

Fix the case of the T-testing label

…ration-reports

.github: Route the "debugging experience" issues better (Part #2)

Add additional backup+restore scenarios, including one where
Mz is allowed to continue to run and execute queries after the backup
has been taken.

To accomodate the scenario, which needs to run the manipulate(#2) phase
once again after the restore, the `@externally_idempotent(False)` decorator
is introduced to annotate Checks that perform actions against third-party
services that are not idempotent.

…lizeInc#25999)

This PR changes the Fivetran Destination to mark primary key columns as
`NOT NULL`. Primary keys are not allowed to be `NULL` and adding this
constraint allows us to plan queries that are currently a three-way join
as a two-way join.

```
CREATE TABLE logs      (id text, ts int);
CREATE TABLE logs_temp (id text, ts int);

EXPLAIN SELECT FROM logs WHERE (id, ts) IN (SELECT id, ts FROM logs_temp);

Explained Query:
  Project () // { arity: 0 }
    Join on=(#0{id} = #2{id} = #4{id} AND #1{ts} = #3{ts} = #5{ts}) type=delta // { arity: 6 }
      ArrangeBy keys=[[#0{id}, #1{ts}]] // { arity: 2 }
        ReadStorage materialize.parker_timmerman.logs // { arity: 2 }
      ArrangeBy keys=[[#0{id}, #1{ts}]] // { arity: 2 }
        Distinct project=[#0{id}, #1{ts}] // { arity: 2 }
          Filter (#0{id}) IS NOT NULL AND (#1{ts}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.parker_timmerman.logs // { arity: 2 }
      ArrangeBy keys=[[#0{id}, #1{ts}]] // { arity: 2 }
        Distinct project=[#0{id}, #1{ts}] // { arity: 2 }
          ReadStorage materialize.parker_timmerman.logs_temp // { arity: 2 }
```

Now with the `NOT NULL` constraints

```
CREATE TABLE logs      (id text NOT NULL, ts int NOT NULL);
CREATE TABLE logs_temp (id text NOT NULL, ts int NOT NULL);

EXPLAIN SELECT FROM logs WHERE (id, ts) IN (SELECT id, ts FROM logs_temp);

Explained Query:
  Project () // { arity: 0 }
    Join on=(#0{id} = #2{id} AND #1{ts} = #3{ts}) type=differential // { arity: 4 }
      ArrangeBy keys=[[#0{id}, #1{ts}]] // { arity: 2 }
        ReadStorage materialize.parker_timmerman.logs // { arity: 2 }
      ArrangeBy keys=[[#0{id}, #1{ts}]] // { arity: 2 }
        Distinct project=[#0{id}, #1{ts}] // { arity: 2 }
          ReadStorage materialize.parker_timmerman.logs_temp // { arity: 2 }
```

Note: Frank currently has a draft PR that allows the optimizer to move
the `Filter` in the first plan so even without these constraints we can
plan the query as a two-way join. I put up this PR because making the
change for the Fivetran Destination was faster.

### Motivation

Improve the performance of the destination, which should reduce the
amount of time the global write lock gets held

### Checklist

- [ ] This PR has adequate test coverage / QA involvement has been duly
considered.
- [ ] This PR has an associated up-to-date [design
doc](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/design/README.md),
is a design doc
([template](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/design/00000000_template.md)),
or is sufficiently small to not require a design.
  <!-- Reference the design in the description. -->
- [ ] If this PR evolves [an existing `$T ⇔ Proto$T`
mapping](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/command-and-response-binary-encoding.md)
(possibly in a backwards-incompatible way), then it is tagged with a
`T-proto` label.
- [ ] If this PR will require changes to cloud orchestration or tests,
there is a companion cloud PR to account for those changes that is
tagged with the release-blocker label
([example](https://github.com/MaterializeInc/cloud/pull/5021)).
<!-- Ask in #team-cloud on Slack if you need help preparing the cloud
PR. -->
- [x] This PR includes the following [user-facing behavior
changes](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/guide-changes.md#what-changes-require-a-release-note):
  - Improve the performance of the Fivetran Destination

…ing-2

storage: prevent load generators with unused options, try #2

Currently, within `LetRec` blocks we only perform relation CSE within
each binding. We do not optimize across bindings, nor do we take
advantage of bindings outside the `LetRec` region. This PR addresses
this with extended code for the `LetRec` case that both references
`LetRec`-external bindings, and shares across `LetRec`-internal
bindings.

There are some potential gotchas, documented in the code:
1. References to `LetRec` bindings should not be equated across the
moment the binding is refreshed. Each binding has a "before" identifier
and an "after" identifier, and this is sufficient to avoid
unintentionally equating the two.
2. The `body` should not borrow much from within the scope. Although all
terms are available for use, the current suffix of the optimizer
pipeline goes sideways if this happens. Concretely, `JoinImplementation`
ends up surprised about the location of arrangements it thought would
certainly exist, but do not. There are some unspecified requirements of
`RelationCSE`, and the careful treatment of `body` seems to be necessary
(unknown if sufficient).
3. There was some initial concern that various iteration limits would
complicate the applicability of CSE, but it seems that all new terms can
be treated as if having no iteration limits, and things "just work out"
because two terms are equated only if they are exact syntactic matches,
which happens only when they depend on the same terms, in which case
those terms will have the same limits.

As a demonstration of what can change, these two WMR blocks are merged,
and the necessary arrangement of `t` by `b` is shared between the two
terms. On `main` that sharing cannot happen, and two identical copies
are made.
```sql
create table t(a int, b int);

create view foo as with mutually recursive
foo0 (a int, b int) as (
    select t.a, foo0.b from t, foo0 where t.b = foo0.a 
    union 
    select * from t where t.a > 3
)
select * from foo0;

create view bar as with mutually recursive
bar0 (a int, b int) as (
    select t.a, bar0.b from t, bar0 where t.b = bar0.a 
    union 
    select * from t where t.a > 2
)
select * from bar0;

-- demonstrates re-use of `t` arranged by `b`.
explain select * from foo union select * from bar;
```
this results in a shared term `l0` seen below
```
materialize=> explain select * from foo union select * from bar;
                 Optimized Plan                  
-------------------------------------------------
 Explained Query:                               +
   Return                                       +
     Distinct project=[#0, #1]                  +
       Union                                    +
         Get l1                                 +
         Get l2                                 +
   With Mutually Recursive                      +
     cte l2 =                                   +
       Distinct project=[#0, #1]                +
         Union                                  +
           Project (#0, #3)                     +
             Join on=(#1 = #2) type=differential+
               Get l0                           +
               ArrangeBy keys=[[#0]]            +
                 Filter (#0) IS NOT NULL        +
                   Get l2                       +
           Filter (#0 > 2)                      +
             ReadStorage materialize.public.t   +
     cte l1 =                                   +
       Distinct project=[#0, #1]                +
         Union                                  +
           Project (#0, #3)                     +
             Join on=(#1 = #2) type=differential+
               Get l0                           +
               ArrangeBy keys=[[#0]]            +
                 Filter (#0) IS NOT NULL        +
                   Get l1                       +
           Filter (#0 > 3)                      +
             ReadStorage materialize.public.t   +
     cte l0 =                                   +
       ArrangeBy keys=[[#1]]                    +
         Filter (#1) IS NOT NULL                +
           ReadStorage materialize.public.t     +
                                                +
 Source materialize.public.t                    +
                                                +
 Target cluster: quickstart                     +
 
(1 row)

materialize=> 
```

There is a missed opportunity for further sharing, where loop-invariant
structure is common to both `values` and `body`. We could perform more
work to hoist all bindings that do not depend on `values` out of `body`,
and install those bindings before considering `values`. In some sense,
the CSE must be done "outside the scope" in order to avoid the peril of
`body` depending on terms that will later be made unavailable.

### Motivation

<!--
Which of the following best describes the motivation behind this PR?

  * This PR fixes a recognized bug.

    [Ensure issue is linked somewhere.]

  * This PR adds a known-desirable feature.

    [Ensure issue is linked somewhere.]

  * This PR fixes a previously unreported bug.

    [Describe the bug in detail, as if you were filing a bug report.]

  * This PR adds a feature that has not yet been specified.

[Write a brief specification for the feature, including justification
for its inclusion in Materialize, as if you were writing the original
     feature specification.]

   * This PR refactors existing code.

[Describe what was wrong with the existing code, if it is not obvious.]
-->

### Tips for reviewer

<!--
Leave some tips for your reviewer, like:

    * The diff is much smaller if viewed with whitespace hidden.
    * [Some function/module/file] deserves extra attention.
* [Some function/module/file] is pure code movement and only needs a
skim.

Delete this section if no tips.
-->

### Checklist

- [ ] This PR has adequate test coverage / QA involvement has been duly
considered. ([trigger-ci for additional test/nightly
runs](https://trigger-ci.dev.materialize.com/))
- [ ] This PR has an associated up-to-date [design
doc](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/design/README.md),
is a design doc
([template](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/design/00000000_template.md)),
or is sufficiently small to not require a design.
  <!-- Reference the design in the description. -->
- [ ] If this PR evolves [an existing `$T ⇔ Proto$T`
mapping](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/command-and-response-binary-encoding.md)
(possibly in a backwards-incompatible way), then it is tagged with a
`T-proto` label.
- [ ] If this PR will require changes to cloud orchestration or tests,
there is a companion cloud PR to account for those changes that is
tagged with the release-blocker label
([example](MaterializeInc/cloud#5021)).
<!-- Ask in #team-cloud on Slack if you need help preparing the cloud
PR. -->
- [ ] If this PR includes major [user-facing behavior
changes](https://github.com/MaterializeInc/materialize/blob/main/doc/developer/guide-changes.md#what-changes-require-a-release-note),
I have pinged the relevant PM to schedule a changelog post.

…terializeInc#36739)

### Motivation

Closes MaterializeInc/database-issues#9376

### Description

Makes default `EXPLAIN` give substantially cleaner output, more like
`EXPLAIN OPTIMIZED PLAN`, with a particular eye to identifying cross
joins.

Old janky:
```
    →Differential Join %0 » %1
      Join stage 0 in %1
        project=(#2)
        map=((#0{v} + #1{v}))
```

New hotness:
```
    →Differential Cross Join %0[×] » %1[×]
      after %1:
        Project: #2
        Map: (#0{v} + #1{v})
      →Arranged l0
      →Arranged l0
```

We call a join a cross join if _any_ part of it involves a cross join:

```
query T multiline
EXPLAIN WITH(humanized expressions)
SELECT a, b, c, d, e, f FROM t, u, v WHERE a = c
----
Explained Query:
  →Differential Cross Join %0[#0{a}] » %1[#0{c}] » %2[×]
    after %1:
      Filter: (#0{a}) IS NOT NULL
    →Arranged materialize.public.t
    →Arranged materialize.public.u
    →Arrange (empty key)
      →Arranged materialize.public.v
```

### Verification

Rewrote `default.slt` and added cross join cases.

projection_thinning (transform #6): operators produce only columns
consumers demand. Consumers process before producers — the root with
all columns demanded, then bindings last-to-first against the union of
use-site demands. thin() rewrites in place, returns the produced
original-column list, and callers remap through it; exact narrowing is
forced only where layouts must agree (binding bodies, Union branches).
Use sites become Project-over-Get markers fixed up once layouts are
final. The only deletions are Map scalars and Reduce aggregates no
consumer observes (only error possibilities can disappear, never rows)
and Constant columns. LetRec stays opaque: anything used inside one is
demanded in full. The pipeline re-runs linear_fuse afterwards to
restore the M-F-P normal form.

Two refinements earned from the LEFT JOIN probe:
- join_flatten emission rewrites region outputs to each equivalence
  class's least column member (datum-equal on every surviving row), so
  duplicated join outputs become undemanded and thin away;
- a binding whose body ends in a Project with duplicate outputs stores
  the deduplicated layout, and use sites re-duplicate (markers compose
  the duplication; bare full-demand Gets get wrapped during fixup).

The LEFT JOIN cte now stores 3 columns with Project (#0,#1,#0,#2) at
the use site — main's shape exactly.

Sweep-010 (12 files): pass=4 plandiff=8 resultdiff=0 panic=0;
joins.slt failing EXPLAINs 6 -> 3.

Co-Authored-By: Claude Fable 5 <[email protected]>