Support uncorrelated subqueries in join conditions

### CrateDB version

6.4.2

### CrateDB setup information

Number of nodes: ?
CRATE_HEAP_SIZE: ?
CRATE_JAVA_OPTS: ?

crate.yml contents:

### Problem description

CrateDB accepts the statement during parsing and analysis, but fails while
building the execution plan. The scalar subquery is nested under a `RIGHT
JOIN` and contains an `IN` subquery with aggregation-like arithmetic and an
`ORDER BY` clause.

Expected: a valid execution plan or a normal runtime result.

Actual:

```text
Couldn't create execution plan from logical plan because of:
Couldn't resolve value for subQuery: (SELECT subq_col_1 FROM (t7))
```

### Steps to Reproduce

Run on CrateDB 6.4.2. The statement does not depend on row contents; the
following minimal schema is sufficient.

```sql
CREATE TABLE t1 (c1 INTEGER, c2 TEXT, c3 INTEGER, c4 INTEGER,
c5 TIMESTAMP, c6 TEXT, c7 INTEGER, c8 INTEGER,
c9 INTEGER, c10 INTEGER, c11 INTEGER, c12 TEXT,
c13 INTEGER, c14 INTEGER, c15 INTEGER);
CREATE TABLE t2 (c1 INTEGER, c2 INTEGER, c3 INTEGER, c4 INTEGER,
c5 TIMESTAMP, c6 INTEGER, c7 TEXT, c8 INTEGER,
c9 INTEGER, c10 INTEGER, c11 INTEGER, c12 INTEGER,
c13 INTEGER, c14 INTEGER, c15 INTEGER);
CREATE TABLE t3 (c1 INTEGER, c2 TEXT, c3 INTEGER, c4 INTEGER,
c5 TIMESTAMP, c6 INTEGER, c7 INTEGER, c8 INTEGER,
c9 INTEGER, c10 INTEGER, c11 INTEGER, c12 TEXT,
c13 INTEGER, c14 INTEGER, c15 INTEGER);

SELECT COUNT(DISTINCT t1.c1) AS q1_col_1,
SUM(t1.c1) AS q1_col_2,
(SELECT t3.c15
FROM t2 AS t2
CROSS JOIN t3 AS t3
RIGHT JOIN t1 AS t6
ON NOT t2.c3 IN (
SELECT FLOOR(t7.c6 / NULLIF(t7.c7, 0))
FROM t3 AS t7
WHERE t7.c11 IS NULL
ORDER BY t7.c14 DESC
)
LIMIT 1) AS q1_col_3
FROM t1 AS t1
WHERE t1.c2 = 'sample_41'
ORDER BY q1_col_2 ASC;
```

### Actual Result

Actual:

```text
Couldn't create execution plan from logical plan because of:
Couldn't resolve value for subQuery: (SELECT subq_col_1 FROM (t7))
```

### Expected Result

Expected: a valid execution plan or a normal runtime result.

Reproduce the supplied SQL statement against CrateDB 6.4.2, then trace the logical-plan to execution-plan handling for the scalar subquery under the RIGHT JOIN and nested IN subquery. Done means the query produces a valid execution plan or a normal runtime result instead of the subQuery resolution error.

Written by the indexing model from the issue text.