sql: vectorized engine incorrectly evaluates NOT (x IN ())

[mgartner]

The unoptimized-query-oracle found a bug which appears to be in the vectorized engine.

The issue is that the vectorized engine does not correctly evaluate the expression NOT (x IN ()). Under normal operation, such an expression is normalized to true by the optimizer by FoldInEmpty and FoldNotInEmpty, so the vectorized engine never evaluates this expression.

CREATE TABLE t (
  i INTERVAL
);

INSERT INTO t (i) VALUES (NULL);

SET testing_optimizer_random_seed = 6320964980407535657;

SET testing_optimizer_disable_rule_probability = 0.500000;

SET vectorize = off;

-- Returns 1 row.
SELECT i
FROM t
WHERE NOT (i IN (
    SELECT '1 day'::INTERVAL
    FROM t t1 JOIN t t2 ON true
    WHERE false
));

RESET vectorize;

-- Returns 0 rows.
SELECT i
FROM t
WHERE NOT (i IN (
    SELECT '1 day'::INTERVAL
    FROM t t1 JOIN t t2 ON true
    WHERE false
));

The query plans for both queries are the same:

  project
   ├── columns: i:1
   ├── stats: [rows=333.3333]
   ├── cost: 1158.60333
   ├── distribution: us-east1
   ├── prune: (1)
   └── select
        ├── columns: t.i:1 t.rowid:2 t.crdb_internal_mvcc_timestamp:3 t.tableoid:4
        ├── stats: [rows=333.3333]
        ├── cost: 1155.25
        ├── key: (2)
        ├── fd: (2)-->(1,3,4)
        ├── distribution: us-east1
        ├── scan t
        │    ├── columns: t.i:1 t.rowid:2 t.crdb_internal_mvcc_timestamp:3 t.tableoid:4
        │    ├── stats: [rows=1000, distinct(2)=1000, null(2)=0]
        │    ├── cost: 1145.22
        │    ├── key: (2)
        │    ├── fd: (2)-->(1,3,4)
        │    └── distribution: us-east1
        └── filters
             └── NOT (t.i:1 IN ()) [outer=(1)]

Jira issue: CRDB-19708

[mgartner]

I am confused why the SET testing_optimizer_random_seed = 6320964980407535657; and SET testing_optimizer_disable_rule_probability = 0.500000; are necessary to reproduce this issue when the resulting query plans are the same.

[mgartner]

Ahh, it looks like the failure is only possible when some normalization rules do not fire. I'll narrow down which ones. This is most likely not a release blocker.

[mgartner]

I've confirmed that when FoldInEmpty and FoldNotInEmpty are disabled on v22.1.7, the issue is present. We should fix this, but I don't think it's a release blocker. I'll remove the label. I'll also mark these two rules as required during costfuzz and unoptimized-query-oracle so that we don't continue to rediscover this bug until the issue is fixed.