COPY FROM DATABASE doesn't work well with unique index #14909
Description
What happens?
I created a table with an unique index in an on-disk database, then copied this database to a new on-disk database.
With the new database, the unique index did not work well with wrong query results returned.
To Reproduce
It can be reproduced by following script.
#!/bin/bash
rm old.db new.db
duckdb -version
# Return single row with _id = 3 as expected
duckdb old.db "CREATE TABLE items (_id INTEGER); INSERT INTO items (_id) VALUES (1), (2), (3); CREATE UNIQUE INDEX idx_id ON items(_id); FROM items WHERE _id > 2;"
duckdb :memory: "ATTACH 'old.db' AS old; ATTACH 'new.db' AS new; COPY FROM DATABASE old TO new;"
# Return unexpected empty rows
duckdb new.db "FROM items WHERE _id > 2;"
rm old.db new.db
# Without unique index
# Return single row with _id = 3 as expected
duckdb old.db "CREATE TABLE items (_id INTEGER); INSERT INTO items (_id) VALUES (1), (2), (3); FROM items WHERE _id > 2;"
duckdb :memory: "ATTACH 'old.db' AS old; ATTACH 'new.db' AS new; COPY FROM DATABASE old TO new;"
# Return single row with _id = 3 as expected
duckdb new.db "FROM items WHERE _id > 2;"
With v1.1.3, the output is:
v1.1.3 19864453f7
┌───────┐
│ _id │
│ int32 │
├───────┤
│ 3 │
└───────┘
┌────────┐
│ _id │
│ int32 │
├────────┤
│ 0 rows │
└────────┘
┌───────┐
│ _id │
│ int32 │
├───────┤
│ 3 │
└───────┘
┌───────┐
│ _id │
│ int32 │
├───────┤
│ 3 │
└───────┘
With v1.1.4-dev2077, the output is:
v1.1.4-dev2077 448b46937f
┌───────┐
│ _id │
│ int32 │
├───────┤
│ 3 │
└───────┘
┌────────┐
│ _id │
│ int32 │
├────────┤
│ 0 rows │
└────────┘
┌───────┐
│ _id │
│ int32 │
├───────┤
│ 3 │
└───────┘
┌───────┐
│ _id │
│ int32 │
├───────┤
│ 3 │
└───────┘
OS:
WSL x86_64
DuckDB Version:
v1.1.3
DuckDB Client:
CLI
Hardware:
No response
Full Name:
Lun Wang
Affiliation:
N/A
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a nightly build
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?
- Yes, I have