sql: unoptimized, vectorized query incorrectly returns a row when it should return none

[mgartner]

I've reduce this failure to:

CREATE TABLE t (
  k STRING PRIMARY KEY,
  a STRING,
  UNIQUE (lower(k::STRING))
);

SET testing_optimizer_random_seed = 4774601380782706250;

SET testing_optimizer_disable_rule_probability = 0.500000;

SET vectorize = off;

SELECT 'a'
FROM t AS t1
JOIN t AS t2 ON t1.k = t2.a
WHERE false
GROUP BY t2.tableoid;

RESET vectorize;

SELECT 'a'
FROM t AS t1
JOIN t AS t2 ON t1.k = t2.a
WHERE false
GROUP BY t2.tableoid;

The second SELECT returns a single row, while the first returns no rows. Both should return no rows.

Both queries have the same query plan:

  project
   ├── columns: "?column?":11
   ├── cardinality: [0 - 0]
   ├── immutable
   ├── stats: [rows=0]
   ├── cost: 0.09
   ├── key: ()
   ├── fd: ()-->(11)
   ├── distribution: us-east1
   ├── group-by (streaming)
   │    ├── cardinality: [0 - 0]
   │    ├── immutable
   │    ├── stats: [rows=0]
   │    ├── cost: 0.08
   │    ├── key: ()
   │    ├── distribution: us-east1
   │    └── project
   │         ├── columns: tableoid:10
   │         ├── cardinality: [0 - 0]
   │         ├── immutable
   │         ├── stats: [rows=0]
   │         ├── cost: 0.06
   │         ├── key: ()
   │         ├── fd: ()-->(10)
   │         ├── distribution: us-east1
   │         └── inner-join (cross)
   │              ├── columns: k:1 a:2 crdb_internal_idx_expr:3 crdb_internal_mvcc_timestamp:4 tableoid:5 k:6 a:7 crdb_internal_idx_expr:8 crdb_internal_mvcc_timestamp:9 tableoid:10
   │              ├── cardinality: [0 - 0]
   │              ├── multiplicity: left-rows(zero-or-one), right-rows(zero-or-one)
   │              ├── immutable
   │              ├── stats: [rows=0]
   │              ├── cost: 0.05
   │              ├── key: ()
   │              ├── fd: ()-->(1-10)
   │              ├── distribution: us-east1
   │              ├── project
   │              │    ├── columns: crdb_internal_idx_expr:3 k:1 a:2 crdb_internal_mvcc_timestamp:4 tableoid:5
   │              │    ├── cardinality: [0 - 0]
   │              │    ├── immutable
   │              │    ├── stats: [rows=0]
   │              │    ├── cost: 0.02
   │              │    ├── key: ()
   │              │    ├── fd: ()-->(1-5)
   │              │    ├── distribution: us-east1
   │              │    ├── values
   │              │    │    ├── columns: k:1 a:2 crdb_internal_mvcc_timestamp:4 tableoid:5
   │              │    │    ├── cardinality: [0 - 0]
   │              │    │    ├── stats: [rows=0]
   │              │    │    ├── cost: 0.01
   │              │    │    ├── key: ()
   │              │    │    ├── fd: ()-->(1,2,4,5)
   │              │    │    └── distribution: us-east1
   │              │    └── projections
   │              │         └── lower(k:1) [as=crdb_internal_idx_expr:3, outer=(1), immutable]
   │              ├── project
   │              │    ├── columns: crdb_internal_idx_expr:8 k:6 a:7 crdb_internal_mvcc_timestamp:9 tableoid:10
   │              │    ├── cardinality: [0 - 0]
   │              │    ├── immutable
   │              │    ├── stats: [rows=0]
   │              │    ├── cost: 0.02
   │              │    ├── key: ()
   │              │    ├── fd: ()-->(6-10)
   │              │    ├── distribution: us-east1
   │              │    ├── values
   │              │    │    ├── columns: k:6 a:7 crdb_internal_mvcc_timestamp:9 tableoid:10
   │              │    │    ├── cardinality: [0 - 0]
   │              │    │    ├── stats: [rows=0]
   │              │    │    ├── cost: 0.01
   │              │    │    ├── key: ()
   │              │    │    ├── fd: ()-->(6,7,9,10)
   │              │    │    └── distribution: us-east1
   │              │    └── projections
   │              │         └── lower(k:6) [as=crdb_internal_idx_expr:8, outer=(6), immutable]
   │              └── filters (true)
   └── projections
        └── 'a' [as="?column?":11]

So, my guess it that there is a bug in the vectorized execution engine.

Jira issue: CRDB-19346

[mgartner]

The testing_optimizer_random_seed and testing_optimizer_disable_rule_probability settings are not supported in 22.1 or before, so it's not easy to tell if this is a regression or not. I've added the release blocker label for now until we can prove that it is present on older versions or not.

[michae2]

We might be able to determine if this is a regression without backporting those settings, once we know the cause.

[yuzefovich]

This is a pre-existing bug, so it's not a release blocker, but I do have a fix.