USE takes unqualified schema and points to invalid database/schema combination

[tekumara]

What happens?

USE with a unqualified schema will point to an invalid database/schema combination

To Reproduce

Given

ATTACH DATABASE ':memory:' AS DB1;
USE db1;
CREATE SCHEMA schema1;
USE schema1;             # or SET schema = schema1

Then the current database is now memory:

SELECT current_setting('schema'), CURRENT_SETTING('search_path'), current_database(), current_schema();
┌───────────────────────────┬────────────────────────────────┬────────────────────┬──────────────────┐
│ current_setting('schema') │ current_setting('search_path') │ current_database() │ current_schema() │
│          varchar          │            varchar             │      varchar       │     varchar      │
├───────────────────────────┼────────────────────────────────┼────────────────────┼──────────────────┤
│ schema1                   │ schema1                        │ memory             │ schema1          │
└───────────────────────────┴────────────────────────────────┴────────────────────┴──────────────────┘

However, schema1 exists in db1, and not the memory database:

SELECT * from information_schema.schemata;
┌──────────────┬────────────────────┬──────────────┬──────────────────────────┬──────────────────────────────┬────────────────────────────┬──────────┐
│ catalog_name │    schema_name     │ schema_owner │ default_character_set_…  │ default_character_set_schema │ default_character_set_name │ sql_path │
│   varchar    │      varchar       │   varchar    │          int32           │            int32             │           int32            │ varchar  │
├──────────────┼────────────────────┼──────────────┼──────────────────────────┼──────────────────────────────┼────────────────────────────┼──────────┤
│ DB1          │ information_schema │ duckdb       │                          │                              │                            │          │
│ DB1          │ main               │ duckdb       │                          │                              │                            │          │
│ DB1          │ pg_catalog         │ duckdb       │                          │                              │                            │          │
│ DB1          │ schema1            │ duckdb       │                          │                              │                            │          │
│ memory       │ information_schema │ duckdb       │                          │                              │                            │          │
│ memory       │ main               │ duckdb       │                          │                              │                            │          │
│ memory       │ pg_catalog         │ duckdb       │                          │                              │                            │          │
│ system       │ information_schema │ duckdb       │                          │                              │                            │          │
│ system       │ main               │ duckdb       │                          │                              │                            │          │
│ system       │ pg_catalog         │ duckdb       │                          │                              │                            │          │
│ temp         │ information_schema │ duckdb       │                          │                              │                            │          │
│ temp         │ main               │ duckdb       │                          │                              │                            │          │
│ temp         │ pg_catalog         │ duckdb       │                          │                              │                            │          │
├──────────────┴────────────────────┴──────────────┴──────────────────────────┴──────────────────────────────┴────────────────────────────┴──────────┤
│ 13 rows                                                                                                                                  7 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Therefore this fails:

CREATE table foo(i int);
Error: Catalog Error: Schema with name schema1 does not exist!

OS:

macos

DuckDB Version:

v0.7.0 f782739

DuckDB Client:

cli

Full Name:

Oliver Mannion

Affiliation:

Xero

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Tishj]

@Mytherin since you recently did work on use/attach semantics, can this be closed now / is this on your radar?

[Mytherin]

This should be fixed in the current master:

D SELECT current_setting('schema'), CURRENT_SETTING('search_path'), current_database(), current_schema();
┌───────────────────────────┬────────────────────────────────┬────────────────────┬──────────────────┐
│ current_setting('schema') │ current_setting('search_path') │ current_database() │ current_schema() │
│          varchar          │            varchar             │      varchar       │     varchar      │
├───────────────────────────┼────────────────────────────────┼────────────────────┼──────────────────┤
│ schema1                   │ db1.schema1                    │ db1                │ schema1          │
└───────────────────────────┴────────────────────────────────┴────────────────────┴──────────────────┘

Thanks for reporting!