opt: do not generate empty Scans with SplitDisjunction and computed columns #78026
Description
In some cases, the SplitDisjunction[AddKey] rule can lead to a Scan being
generated with a contradicion as the constraint.
For example, consider the table and query:
CREATE TABLE t (
k INT PRIMARY KEY,
a INT,
b STRING,
v INT AS (a + 1) STORED,
INDEX (v, a) STORING (b),
INDEX (b) STORING (a, b)
)
SELECT * FROM t WHERE a = 1 AND (v != 2 OR b != 'foo')SplitDisjunction will create an expression like:
distinct-on
├── columns: k:1!null a:2!null b:3 v:4
├── grouping columns: k:1!null
└── union-all
├── columns: k:1!null a:2!null b:3 v:4
├── left columns: k:7 a:8 b:9 v:10
├── right columns: k:13 a:14 b:15 v:16
├── select
│ ├── columns: k:7!null a:8!null b:9 v:10!null
│ ├── scan t
│ │ ├── columns: k:7!null a:8 b:9 v:10
│ │ └── computed column expressions
│ │ └── v:10
│ │ └── a:8 + 1
│ └── filters
│ ├── a:8 = 1
│ └── v:10 != 2
└── select
├── columns: k:13!null a:14!null b:15!null v:16
├── scan t
│ ├── columns: k:13!null a:14 b:15 v:16
│ └── computed column expressions
│ └── v:16
│ └── a:14 + 1
└── filters
├── a:14 = 1
└── b:15 != 'foo'
Later, GenerateConstrainedScans will generate a Scan on the left side of the
UnionAll with contradiction as the constraint. This is because the Select
filters a = 1 AND v != 2 combine with an implicit filter based on the computed
column expression v = a + 1 to form a contradiction.
This case is may not be common in queries written by users, but it can occur in
expressions synthesized for uniqueness checks. See
#77943 (review).
If possible, we should remove the UnionAll altogether when this occurs. Ideally,
however, the computed column expression could be added to the original query
filters to simplify them during normalization. This would eliminate the
disjunction and prevent SplitDisjunction from ever firing.
Notice that a conjunction with the original filters and the computed column
expression can be simplified such that the disjunction is eliminated:
a = 1 AND (v != 2 OR b != 'foo') AND v = a + 1
--- InlineConstVar inlines a = 1 ---
a = 1 AND (v != 2 OR b != 'foo') AND v = 2
--- InlineConstVar inlines v = 2 ---
a = 1 AND (2 != 2 OR b != 'foo') AND v = 2
--- FoldComparison simplifies 2 != 2 ---
a = 1 AND (false OR b != 'foo') AND v = 2
--- SimplifyFalseOr eliminates the disjunction ---
a = 1 AND b != 'foo' AND v = 2
Jira issue: CRDB-13925