cockroachdb / cockroachdb/cockroach

sql: incorrect caching of database name for query on virtual table

Open
#112,259 1 comment 0 reactions 0 assignees View on GitHub
C-bug db-cy-23 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Consider the example below:

```
defaultdb> SET sql_safe_updates = false;
SET

defaultdb> CREATE DATABASE db1;
CREATE DATABASE

defaultdb> USE db1;
SET

db1> SELECT * FROM crdb_internal.create_schema_statements;
database_id | database_name | schema_name | descriptor_id | create_statement
--------------+---------------+-------------+---------------+-----------------------
104 | db1 | public | 105 | CREATE SCHEMA public
(1 row)

db1> ALTER DATABASE db1 RENAME TO db2;
ALTER DATABASE

db2> SELECT * FROM crdb_internal.create_schema_statements;
ERROR: database "db1" does not exist
SQLSTATE: 3D000
```

Something is caching the original name of the database, `db1`, from the first invocation of the `SELECT` statement and reusing it in the second invocation. My guess is that this is happening in the query cache or the opt catalog, but I haven't dug in yet.

Jira issue: CRDB-32318

Contributor guide

Open the contributing guide

Research direction

Reproduce the SQL sequence using crdb_internal.create_schema_statements: create and rename a database, then run the virtual-table query again. Inspect the query cache and opt catalog, which the issue identifies as likely areas; done means the second query resolves the renamed database instead of reporting that the old name does not exist.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, 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.