sql: unwanted correlation hides semantic errors in delegated SHOW statements

[knz]

Describe the problem

I typed the following INVALID query while investigating #99200:

SHOW JOBS SELECT job_id FROM system.jobs WHERE job_type='CHANGEFEED'

(The query is invalid because system.jobs does not have a job_id column.)

Observe: The query runs to completion (and returns bogus results).

The reason for this behavior is that under the hood SHOW JOBS XXX expands into
SELECT ... FROM crdb_internal.jobs WHERE job_id IN (XXX)

So in the example above we get:

SELECT ... FROM crdb_internal.jobs WHERE job_id IN (
  SELECT job_id FROM system.jobs ...
)

And then the query correlation rules apply: because job_id in the sub-query doesn't exist in system.jobs, it is picked from the outer scope (from crdb_internal.jobs). So we get effectively a cross-join between the two tables.

Expected behavior

Every time one of the "delegate" functions creates SQL syntax using a parameter that is also a "select clause", we need to be careful to disable the query correlation rules.

For example, in the above this can be achieved via

WITH ids AS (SELECT job_id FROM system.jobs)
SELECT ... FROM crdb_internal.jobs WHERE job_id IN (TABLE ids)

which, in this case, properly errors out.

We would also need to audit the other delegate functions accordingly.

Jira issue: CRDB-26737

[dt]

Not that this behavior is identical on 22.1, 22.2 -- this isn't new or related to more recent jobs changes.