I know this is just moving the function, but I wonder if it's useful to examine Passthrough columns too and peek into the child operation to look for the constant. Maybe there are no use cases where this is needed though.

This looks like when intersecting spans the result is empty. Do you think it would be worth opening an issue to eliminate the UNION ALL when one side is known to return zero rows.

I don't think it's necessary because normalization rules should merge all the Projects together, or merge the Projects into a subexpression that is a single Values row.

For example:

optsteps
SELECT a + 1 FROM (VALUES (1)) AS v(a)
----
================================================================================
Initial expression
  Cost: 0.05
================================================================================
  project
   ├── columns: "?column?":2!null
   ├── cardinality: [1 - 1]
   ├── immutable
   ├── key: ()
   ├── fd: ()-->(2)
   ├── values
   │    ├── columns: column1:1!null
   │    ├── cardinality: [1 - 1]
   │    ├── key: ()
   │    ├── fd: ()-->(1)
   │    └── (1,)
   └── projections
        └── column1:1 + 1 [as="?column?":2, outer=(1), immutable]
================================================================================
InlineProjectConstants
  Cost: 0.05
================================================================================
   project
    ├── columns: "?column?":2!null
    ├── cardinality: [1 - 1]
    ├── immutable
    ├── key: ()
    ├── fd: ()-->(2)
    ├── values
    │    ├── columns: column1:1!null
    │    ├── cardinality: [1 - 1]
    │    ├── key: ()
    │    ├── fd: ()-->(1)
    │    └── (1,)
    └── projections
  -      └── column1:1 + 1 [as="?column?":2, outer=(1), immutable]
  +      └── 1 + 1 [as="?column?":2, immutable]
================================================================================
FoldBinary
  Cost: 0.05
================================================================================
   project
    ├── columns: "?column?":2!null
    ├── cardinality: [1 - 1]
  - ├── immutable
    ├── key: ()
    ├── fd: ()-->(2)
    ├── values
    │    ├── columns: column1:1!null
    │    ├── cardinality: [1 - 1]
    │    ├── key: ()
    │    ├── fd: ()-->(1)
    │    └── (1,)
    └── projections
  -      └── 1 + 1 [as="?column?":2, immutable]
  +      └── 2 [as="?column?":2]
================================================================================
MergeProjectWithValues
  Cost: 0.02
================================================================================
  -project
  +values
    ├── columns: "?column?":2!null
    ├── cardinality: [1 - 1]
    ├── key: ()
    ├── fd: ()-->(2)
  - ├── values
  - │    ├── columns: column1:1!null
  - │    ├── cardinality: [1 - 1]
  - │    ├── key: ()
  - │    ├── fd: ()-->(1)
  - │    └── (1,)
  - └── projections
  -      └── 2 [as="?column?":2]
  + └── (2,)
================================================================================
Final best expression
  Cost: 0.02
================================================================================
  values
   ├── columns: "?column?":2!null
   ├── cardinality: [1 - 1]
   ├── key: ()
   ├── fd: ()-->(2)
   └── (2,)

Anything beneath the Project should be non-constant (or at least not provably constant).

Great eye! I spent all day yesterday working on ways to eliminate the scan with a contradiction constraint and the union all. It's a bit tricky, but I'll keep at it. I've created an issue to track: #78026

The problem is that normalization rules only apply when norm.Factory.ConstructXxxx functions are called. When SplitDisjunction calls Factory.ConstructUnionAll, the left side is an expression like (Select (Scan ...) Filters). This doesn't appear to be a contradiction, so the left side is not simplified to an empty values expression, thus the UnionAll is not eliminated.

Only later on, when GenerateConstrainedScans fires, is it determined that the Select's filters and the computed column expression form a contradiction. At this point, there is no opportunity for EliminateSetLeft to fire because the UnionAll has already been constructed.

I think we'd also have trouble making an exploration rule that eliminates the UnionAll. The (Select (Scan ...) Filters) created on the left side of the UnionAll by SplitDisjunction is the first member of a new group. The Cardinality in the logical properties of this group is not zero because, like I mentioned above, it's not apparent that there is a contradiction. GenerateConstrainedScans adds the (Scan contradiction) to this same group. However, the cardinality of the scan remains non-zero because logical properties are shared by all members of a group.

I do have a somewhat promising idea, though. We can add the computed column equality into the filters during normalization, and the other normalization rules should fire to eliminate the OR entirely. Then SplitDisjunction never matches, and the UnionAll is never created. I mentioned this in #78026. It is a non-trivial rule, however, mostly because there's not an obvious way to prevent the rule from matching over and over again forever.

nit: I'd call this isFK

can you remove this TODO now?

what is this expression? looks like a full table scan?

awesome!

We could potentially optimize this to remove the constraint check altogether in this case (in a future PR)

Edit: I see you added a TODO for this elsewhere

wonder if we can remove this TODO

Not related to this PR, but here's an example where I think we probably want to enable @msirek's stats logic to get distinct counts / histograms from check constraints even when there are no stats available.

It's actually a span with a contradiction constraint. See the discussion Mark and I had above. It's quite peculiar. I've already created an issue to track this: #78026