`ATTACH` fails with `~/some-db.sqlite3` path expansion

[abhillman]

What happens?

[nix-shell:~]$ duckdb
v1.2.2 7c039464e4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D INSTALL sqlite;
D LOAD sqlite;
D ATTACH '~/netflix.db' AS netflix (TYPE SQLITE);
D .tables
Invalid Error:
Unable to open database "~/netflix.db": unable to open database file

[nix-shell:~]$ sqlite3 ~/netflix.db
SQLite version 3.45.3 2024-04-15 13:34:05
Enter ".help" for usage hints.
sqlite> .tables
Netflix
sqlite> select * from Netflix limit 10;
2430508|8548|3|2005-10-31
1895600|8548|2|2005-12-12
2285825|8548|2|2005-11-03
1131364|8548|3|2005-11-25
1227322|8548|1|2005-11-22
902279|8548|5|2005-11-30
95673|8548|3|2005-10-12
1203779|8548|3|2005-11-21
890085|8548|1|2005-10-13
1888522|8548|3|2005-10-12

To Reproduce

Download and unzip the dataset from https://archive.org/details/netflixprize-dataset-sql.

OS:

x64

SQLite Version:

3.45.3 works (unkown which version created it)

DuckDB Version:

v1.2.2

DuckDB Client:

CLI

Full Name:

Aryeh Hillman

Affiliation:

self

Have you tried this on the latest main 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

[abhillman]

Okay -- curiously, this works:

[nix-shell:~]$ duckdb ~/netflix.db
v1.2.2 7c039464e4
Enter ".help" for usage hints.
D .tables
100% ▕████████████████████████████████████████████████████████████▏
Netflix
D select * from Netflix limit 10;
┌─────────┬───────┬────────┬────────────┐
│   CID   │  MID  │ RATING │   DATED    │
│  int64  │ int64 │ int64  │  varchar   │
├─────────┼───────┼────────┼────────────┤
│ 2430508 │  8548 │      3 │ 2005-10-31 │
│ 1895600 │  8548 │      2 │ 2005-12-12 │
│ 2285825 │  8548 │      2 │ 2005-11-03 │
│ 1131364 │  8548 │      3 │ 2005-11-25 │
│ 1227322 │  8548 │      1 │ 2005-11-22 │
│  902279 │  8548 │      5 │ 2005-11-30 │
│   95673 │  8548 │      3 │ 2005-10-12 │
│ 1203779 │  8548 │      3 │ 2005-11-21 │
│  890085 │  8548 │      1 │ 2005-10-13 │
│ 1888522 │  8548 │      3 │ 2005-10-12 │
├─────────┴───────┴────────┴────────────┤
│ 10 rows                     4 columns │
└───────────────────────────────────────┘

[abhillman]

Issue appears to be with relative paths.

[nix-shell:~]$ duckdb
v1.2.2 7c039464e4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D ATTACH '~/netflix.db' AS netflix;
D .tables
Invalid Error:
Unable to open database "~/netflix.db": unable to open database file
D


[nix-shell:~]$ duckdb
v1.2.2 7c039464e4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D ATTACH '/home/aryehh/netflix.db' AS netflix;
D .tables
100% ▕████████████████████████████████████████████████████████████▏
Netflix
D

Unsure if this is a duckdb or sqlite extension issue. This tentatively looks like an issue with the extension (ie attaching ordinary DuckDB databases using ~ path expansion seems to work):

[nix-shell:~]$ duckdb
v1.2.2 7c039464e4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D ATTACH '~/idk.duckdb';
D .tables
foo

[abhillman]

Appears to fail on sqlite3 -- so would have to expand before passing to sqlite3 (if we want to support this):

[nix-shell:~]$ sqlite3
SQLite version 3.45.3 2024-04-15 13:34:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open '~/netflix.db'
Error: unable to open database "~/netflix.db": unable to open database file
Notice: using substitute in-memory database instead of "~/netflix.db"
sqlite> .open 'netflix.db'
sqlite> .tables
Netflix

I think it would make sense to support this as I don't think that this extension seeks to support sqlite3 connection strings:

D ATTACH 'file:netflix.db?mode=readonly' as Netflix (type SQLITE);
D .tables -- nothing returned here
D use Netflix;
D show tables;
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘
D SELECT current_database();
┌────────────────────┐
│ current_database() │
│      varchar       │
├────────────────────┤
│ Netflix            │
└────────────────────┘
D show tables; -- silent failure?
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘