sql: SHOW JOBS SELECT query should not see the SHOW JOBS columns

[RaduBerinde]

The following statement runs when it shouldn't:

SHOW JOBS SELECT job_id FROM system.jobs;

system.jobs does not have a job_id column. SHOW JOBS produces a job_id column, so this is handled like a correlated subquery.

This is not surprising because internally it gets rewritten as a subquery:

cockroach/pkg/sql/delegate/show_jobs.go

Line 76 in 88f311d

whereClause = fmt.Sprintf(`WHERE job_id in (%s)`, n.Jobs.String())

The fix might be as easy as changing (%s) to [%s] here. As a reminder [ query ] is syntactic sugar for a top-level WITH x AS query. It might be cleaner to just generate the top-level WITH instead of using [ .. ] though.

Jira issue: CRDB-37172

[RaduBerinde]

plan:

EXPLAIN (VERBOSE) SHOW JOBS SELECT job_id FROM system.jobs;

  • cross join (inner)
  │ columns: (job_id, job_type, description, statement, user_name, status, running_status, created, started, finished, modified, fraction_completed, error, coordinator_id, trace_id, last_run, next_run, num_runs, execution_errors)
  │ estimated row count: 990 (missing stats)
  │
  ├── • filter
  │   │ columns: (job_id, job_type, description, statement, user_name, status, running_status, created, started, finished, modified, fraction_completed, error, coordinator_id, trace_id, last_run, next_run, num_runs, execution_errors)
  │   │ estimated row count: 990 (missing stats)
  │   │ filter: job_id IS DISTINCT FROM CAST(NULL AS INT8)
  │   │
  │   └── • virtual table
  │         columns: (job_id, job_type, description, statement, user_name, status, running_status, created, started, finished, modified, fraction_completed, error, coordinator_id, trace_id, last_run, next_run, num_runs, execution_errors)
  │         estimated row count: 1,000 (missing stats)
  │         table: jobs@primary
  │
  └── • scan
        columns: ()
        estimated row count: 1 (missing stats)
        table: jobs@jobs_job_type_idx
        spans: LIMITED SCAN
        limit: 1

[rafiss]

This appears to duplicate #100996