opt: add an exploration rule to convert correlated self-join with LIMIT 1 into LATERAL

Inspired by this [support](https://github.com/cockroachlabs/support/issues/3390) ticket.

Consider the following setup:
```sql
CREATE TABLE activity (
shard
INT8 NOT NULL,
id
STRING NOT NULL,
created_at
TIMESTAMPTZ NOT NULL,
PRIMARY KEY (shard ASC, id ASC)
);

CREATE TABLE log (
shard
INT8 NOT NULL,
id
STRING NOT NULL,
created_at
TIMESTAMPTZ NOT NULL,
PRIMARY KEY (shard ASC, id ASC),
UNIQUE (id, created_at)
);

EXPLAIN
SELECT
*
FROM
activity
LEFT JOIN log ON
log.id = activity.id
AND log.created_at
= (
SELECT
log1.created_at
FROM
log AS log1
WHERE
log1.id = log.id
AND log1.created_at
< activity.created_at
ORDER BY
log1.created_at
DESC
LIMIT
1
);
```
Michael and I think that the query can be rewritten with an equivalent transformation like so
```sql

EXPLAIN
SELECT
*
FROM
activity
LEFT JOIN LATERAL (
SELECT
log.*
FROM
log
WHERE
log.created_at
< activity.created_at
ORDER BY
log.created_at DESC
LIMIT
1
)
AS log ON log.id = activity.id;
```
We should consider adding an exploration rule like this. Some necessary conditions are:
- LATERAL join will always return one row, whereas LEFT JOIN might return multiple. So it must be the case, that for a given `log.id` value, all `created_at` values are unique.
- all predicates on `log` table must be included within the correlated subquery (i.e. if we have a predicate on `log` outside, the transformation is not valid).
- perhaps need to also consider nullability of the columns.

Jira issue: CRDB-53316