cockroachdb / cockroachdb/cockroach

sql: prepared statements don't respect AS OF SYSTEM TIME for schema resolution

Open
#166,124 2 comments 0 reactions 0 assignees View on GitHub
A-prepared-stmts branch-master C-bug T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

When a statement with an AS OF SYSTEM TIME clause is executed directly,
the AOST redirects schema resolution to the historical timestamp. This
allows referencing tables that have since been dropped.

When the same statement is prepared via `PREPARE`, the prepare step
resolves table names against the current schema before considering the
AOST clause. If the table has been dropped, the prepare fails with a
"relation does not exist" error.

Repro (in a logictest with `# LogicTest: local`):

```
statement ok
CREATE TABLE stock (item, quantity) AS VALUES ('cups', 10), ('plates', 15)

let $ts
SELECT cluster_logical_timestamp()

statement ok
DROP TABLE stock

# Direct execution works:
statement ok
CREATE TABLE t1 AS SELECT * FROM stock AS OF SYSTEM TIME '$ts'

statement ok
DROP TABLE t1

# Explicit PREPARE fails:
statement ok
PREPARE q AS CREATE TABLE t2 AS SELECT * FROM stock AS OF SYSTEM TIME '$ts'
```

The last statement fails with:
```
(42P01) relation "stock" does not exist
```

The prepare step should respect AOST for schema resolution, matching
the behavior of direct execution.

Found while enabling the `local-prepared` logictest config on more
files (part of #152139).

Contributor guide

Open the contributing guide

Research direction

Start by running the provided LogicTest reproduction with the local and local-prepared configurations, then trace how PREPARE resolves table names before executing the AS OF SYSTEM TIME query. Done means the prepared statement succeeds after stock is dropped and resolves it at the historical timestamp, matching direct execution.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.