sql: missing validation for recursive CTEs

Postgres [disallows](https://github.com/postgres/postgres/blob/7167e05fc7d191bfd59f16d0852705d8f4a3fa08/src/backend/parser/parse_cte.c#L30-L39) the recursive reference for a recursive CTE to be located in certain contexts, like the right side of a left-join, or a subquery. We currently do not return an error in some of these cases. Example:
In Postgres:
```
postgres=# create table ab (a int, b int);
CREATE TABLE
postgres=# insert into ab values (1, 10), (2, 20), (3, 30);
INSERT 0 3
postgres=# WITH RECURSIVE foo (x, y) AS (
-> SELECT a, b FROM ab WHERE a = 1
-> UNION ALL
-> SELECT a, b FROM ab WHERE a = (SELECT max(x) + 1 FROM foo)
-> )SELECT array_agg(y) FR^C foo;
postgres=# WITH RECURSIVE foo (x, y) AS (
SELECT a, b FROM ab WHERE a = 1
UNION ALL
SELECT a, b FROM ab WHERE a = (SELECT max(x) + 1 FROM foo)
) SELECT array_agg(y) FROM foo;
ERROR: recursive reference to query "foo" must not appear within a subquery
LINE 4: SELECT a, b FROM ab WHERE a = (SELECT max(x) + 1 FROM foo)
^
```
In CRDB:
```
root@localhost:26257/defaultdb> create table ab (a int, b int);
CREATE TABLE

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

root@localhost:26257/defaultdb> insert into ab values (1, 10), (2, 20), (3, 30);
INSERT 0 3

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

root@localhost:26257/defaultdb> WITH RECURSIVE foo (x, y) AS (
-> SELECT a, b FROM ab WHERE a = 1
-> UNION ALL
-> SELECT a, b FROM ab WHERE a = (SELECT max(x) + 1 FROM foo)
-> )SELECT array_agg(y) FROM foo;
array_agg
--------------
{10,20,30}
(1 row)

Time: 5ms total (execution 4ms / network 1ms)
```
It's not clear to me that we have to disallow a case like this, but we should at least track the difference.

Jira issue: CRDB-45170