opt: support = like IN for locality optimized search

[ajwerner]

Is your feature request related to a problem? Please describe.
I'm sure that I'm not using the right terminology here. I was following along with #91084 and I tried it out myself (without copy-paste) and I typed something marginally different from the query was shown as working. I believe that it's the same, on some level, but it behaves very differently.

Consider:

SET experimental_enable_unique_without_index_constraints = true;

CREATE TABLE accounts (
    account_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        STRING NOT NULL,
    crdb_region crdb_internal_region NOT NULL,
    UNIQUE WITHOUT INDEX (account_id, crdb_region)
) LOCALITY GLOBAL;

CREATE TABLE tweets (
    account_id UUID NOT NULL,
    tweet_id   UUID DEFAULT gen_random_uuid(),
    message    STRING NOT NULL,
    PRIMARY KEY (account_id, tweet_id),
    FOREIGN KEY (account_id, crdb_region) REFERENCES accounts (account_id, crdb_region) ON DELETE CASCADE ON UPDATE CASCADE
) LOCALITY REGIONAL BY ROW;

The example query which works today is:

EXPLAIN
SELECT tweet_id, message
FROM tweets
WHERE (crdb_region, account_id) IN
  (SELECT crdb_region, account_id
   FROM accounts
   WHERE account_id = '6f781502-4936-43cc-b384-04e5cf292cc8');

This works nicely. Now, instead, consider:

EXPLAIN
SELECT tweet_id, message
FROM tweets
WHERE (crdb_region, account_id) =
  (SELECT crdb_region, account_id
   FROM accounts
   WHERE account_id = '6f781502-4936-43cc-b384-04e5cf292cc8');

The differences is = instead of IN. I would expect that this can be equally optimized the same way, but it's not.

Jira issue: CRDB-22186