logictest: EXPLAIN results different under multi-tenancy

[otan]

As of #69267, we have multi-region multi-tenant tests. However, multi_region_tpcc and regional_by_row fails as the explain output looks different:

        testdata/logic_test/multi_region_tpcc:189: EXPLAIN INSERT
        INTO
          history (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_amount, h_date, h_data)
        VALUES
          (2057, 4, 3, 4, 3, 2100.9, '2021-04-15 15:22:14', '9    zmssaF9m')
        expected:
            distribution: local
            vectorized: true
            ·
            • root
            │
            ├── • insert
            │   │ into: history(rowid, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data, crdb_region)
            │   │
            │   └── • buffer
            │       │ label: buffer 1
            │       │
            │       └── • values
            │             size: 11 columns, 1 row
            │
            ├── • constraint-check
            │   │
            │   └── • error if rows
            │       │
            │       └── • lookup join (anti)
            │           │ estimated row count: 0
            │           │ table: customer@primary
            │           │ equality cols are key
            │           │ lookup condition: (((column3 = c_w_id) AND (column2 = c_d_id)) AND (column1 = c_id)) AND (crdb_region IN ('ca-central-1', 'us-east-1'))
            │           │
            │           └── • lookup join (anti)
            │               │ estimated row count: 1
            │               │ table: customer@primary
            │               │ equality cols are key
            │               │ lookup condition: (((column3 = c_w_id) AND (column2 = c_d_id)) AND (column1 = c_id)) AND (crdb_region = 'ap-southeast-2')
            │               │
            │               └── • scan buffer
            │                     label: buffer 1
            │
            └── • constraint-check
                │
                └── • error if rows
                    │
                    └── • lookup join (anti)
                        │ estimated row count: 0
                        │ table: district@primary
                        │ equality cols are key
                        │ lookup condition: ((column5 = d_w_id) AND (column4 = d_id)) AND (crdb_region IN ('ca-central-1', 'us-east-1'))
                        │
                        └── • lookup join (anti)
                            │ estimated row count: 1
                            │ table: district@primary
                            │ equality cols are key
                            │ lookup condition: ((column5 = d_w_id) AND (column4 = d_id)) AND (crdb_region = 'ap-southeast-2')
                            │
                            └── • scan buffer
                                  label: buffer 1
        but found (query options: "") :
            distribution: local
            vectorized: true
            ·
            • root
            │
            ├── • insert
            │   │ into: history(rowid, h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data, crdb_region)
            │   │
            │   └── • buffer
            │       │ label: buffer 1
            │       │
            │       └── • values
            │             size: 11 columns, 1 row
            │
            ├── • constraint-check
            │   │
            │   └── • error if rows
            │       │
            │       └── • lookup join (anti)
            │           │ estimated row count: 0
            │           │ table: customer@primary
            │           │ equality cols are key
            │           │ lookup condition: (((column3 = c_w_id) AND (column2 = c_d_id)) AND (column1 = c_id)) AND (crdb_region IN ('ap-southeast-2', 'ca-central-1', 'us-east-1'))
            │           │
            │           └── • scan buffer
            │                 label: buffer 1
            │
            └── • constraint-check
                │
                └── • error if rows
                    │
                    └── • lookup join (anti)
                        │ estimated row count: 0
                        │ table: district@primary
                        │ equality cols are key
                        │ lookup condition: ((column5 = d_w_id) AND (column4 = d_id)) AND (crdb_region IN ('ap-southeast-2', 'ca-central-1', 'us-east-1'))
                        │
                        └── • scan buffer
                              label: buffer 1

cc @rytaft

[arulajmani]

I think this is because the optimizer uses the SystemConfigSpan to do its locality aware planning. The SystemConfigSpan is only a thing for the host tenant so it doesn't have this information for secondary tenants. As part of the multi-tenant zone configurations work we've talked about replacing all usages of the SystemConfigSpan with a different datastructure that includes zone/span config information for tenants as well. This would include using it in the optimizer as well.

I thought we had an issue for this, but I can't seem to find it. I'll circle back and file one if the issue I speak of doesn't exist.

[otan]

is this planned to be in v21.2? this seems to make RBR borderline unusable in tenants if so.

[rytaft]

Should I assign this issue to you then, @arulajmani?

[arulajmani]

I don't think this is planned for v21.2 but I can assign myself for now. I'll also add this to the zone configurations board.

[irfansharif]

Duplicate of #70558.