opt: enforce_home_region seems to not work for RBT with locality other than primary region

[yuzefovich]

Consider the following logic test:

# tenant-cluster-setting-override-opt: allow-multi-region-abstractions-for-secondary-tenants
# LogicTest: multiregion-9node-3region-3azs !metamorphic-batch-sizes

# Set the closed timestamp interval to be short to shorten the amount of time
# we need to wait for the system config to propagate.
statement ok
SET CLUSTER SETTING kv.closed_timestamp.side_transport_interval = '10ms';

statement ok
SET CLUSTER SETTING kv.closed_timestamp.target_duration = '10ms';

# Start with SURVIVE ZONE FAILURE for positive tests.
# SURVIVE REGION FAILURE cases will always error out.
statement ok
CREATE DATABASE multi_region_test_db PRIMARY REGION "ca-central-1" REGIONS "ap-southeast-2", "us-east-1" SURVIVE ZONE FAILURE;

# Zone configs sometimes are not available right away. Add a sleep time to the
# test to ensure they're available before running tests.
sleep 2s

statement ok
USE multi_region_test_db

query T
SELECT gateway_region();
----
ap-southeast-2

statement ok
CREATE TABLE messages_rbt (
    account_id INT NOT NULL,
    message_id   UUID DEFAULT gen_random_uuid(),
    message    STRING NOT NULL,
    PRIMARY KEY (account_id),
    INDEX msg_idx(message)
) LOCALITY REGIONAL BY TABLE IN "us-east-1"

statement ok
CREATE TABLE messages_rbr (
    account_id INT NOT NULL,
    message_id   UUID DEFAULT gen_random_uuid(),
    message    STRING NOT NULL,
    crdb_region crdb_internal_region NOT NULL,
    PRIMARY KEY (account_id),
    INDEX msg_idx(message)
)
LOCALITY REGIONAL BY ROW

statement ok
SET enforce_home_region = true

# Locality optimized lookup join is allowed.
query TTTTTTT retry
SELECT * FROM messages_rbr rbr, messages_rbt rbt WHERE rbr.account_id = rbt.account_id LIMIT 1
----

query T retry
EXPLAIN SELECT * FROM messages_rbr rbr, messages_rbt rbt WHERE rbr.account_id = rbt.account_id LIMIT 1
----
distribution: local
vectorized: true
·
• limit
│ count: 1
│
└── • lookup join
    │ table: messages_rbr@messages_rbr_pkey
    │ equality cols are key
    │ lookup condition: (crdb_region = 'ap-southeast-2') AND (account_id = account_id)
    │ remote lookup condition: (crdb_region IN ('ca-central-1', 'us-east-1')) AND (account_id = account_id)
    │
    └── • scan
          missing stats
          table: messages_rbt@messages_rbt_pkey
          spans: FULL SCAN (SOFT LIMIT)

statement ok
RESET enforce_home_region

IIUC the queries with the locality optimized lookup join should not be allowed because they contain a full scan of a RBT table for which the primary region ("us-east-1") is different from the primary region of the database ("ca-central-1") and is also different from the gateway region for the query ("ap-southeast-2").

I could definitely be wrong about the expectations in this case though.

Jira issue: CRDB-20012