opt: no home region error in explicit txn has no home region

[michae2]

With enforce_home_region set, it seems that there is different behavior for single statements using implicit transactions vs. statements in explicit transactions. Here is a repro, using cockroach demo --global --nodes 9 --multitenant=false --insecure:

CREATE DATABASE d PRIMARY REGION "us-east1" REGIONS "europe-west1", "us-west1";
SHOW CREATE DATABASE d;
USE d;

CREATE TABLE abc (
  a INT,
  b INT,
  c INT,
  PRIMARY KEY (a),
  INDEX (b)
) LOCALITY REGIONAL BY ROW;
SHOW CREATE TABLE abc;

INSERT INTO abc (a, b, c, crdb_region) VALUES (1, 1, 1, 'europe-west1'), (2, 2, 2, 'us-west1');

SET enforce_home_region = on;
SET enforce_home_region_follower_reads_enabled = on;

-- assuming locality is 'us-east1'
SHOW LOCALITY;

-- when this statement runs in an implicit transaction, it fails, but does have a home region (which is reported)
SELECT a FROM abc WHERE a = 1 LIMIT 1;

-- when run in an explicit transaction, however, it fails but does *not* have a home region:
BEGIN;
SELECT a FROM abc WHERE a = 1 LIMIT 1;

Here's how it looks on current tip of master (v23.1.0-alpha.8-dev):

root@127.0.0.1:26257/d> SELECT a FROM abc WHERE a = 1 LIMIT 1;
  a
-----
  1
(1 row)
(error encountered after some results were delivered)
ERROR: Query is not running in its home region. Try running the query from region 'europe-west1'. For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
SQLSTATE: XCHR1

root@127.0.0.1:26257/d> BEGIN;
BEGIN

Time: 0ms total (execution 0ms / network 0ms)

root@127.0.0.1:26257/d  OPEN> SELECT a FROM abc WHERE a = 1 LIMIT 1;
ERROR: Query has no home region. Try using a lower LIMIT value or running the query from a different region. For more information, see https://www.cockroachlabs.com/docs/stable/cost-based-optimizer.html#control-whether-queries-are-limited-to-a-single-region
SQLSTATE: XCHR2

Jira issue: CRDB-25921