"CREATE INDEX IF NOT EXISTS" doesn't work

[yyuu]

What happens?

DuckDB accepts CREATE INDEX statement with IF NOT EXISTS, however, it seems IF NOT EXISTS isn't actually working with SQLite3 tables.

To Reproduce

If I were invoke CREATE INDEX IF NOT EXISTS for the first time, it creates index on the table.

% rm -f foo.sqlite
% duckdb -c "ATTACH 'foo.sqlite' AS foo (TYPE SQLITE); USE foo; CREATE TABLE IF NOT EXISTS t1 (key VARCHAR, value VARCHAR); CREATE INDEX IF NOT EXISTS t1_key ON t1 (key);"
% sqlite3 foo.sqlite .schema
-- Loading resources from /Users/yyuu/.sqliterc
CREATE TABLE t1("key" VARCHAR, "value" VARCHAR);
CREATE INDEX t1_key ON t1("key");
CREATE TABLE temp.sqlite_parameters(
  key TEXT PRIMARY KEY,
  value
) WITHOUT ROWID;

Although if I were invoking identical CREATE INDEX IF NOT EXISTS again, it fails.

% duckdb -c "ATTACH 'foo.sqlite' AS foo (TYPE SQLITE); USE foo; CREATE TABLE IF NOT EXISTS t1 (key VARCHAR, value VARCHAR); CREATE INDEX IF NOT EXISTS t1_key ON t1 (key);"
Invalid Error: Failed to execute query "CREATE INDEX t1_key ON t1("key")": index t1_key already exists

OS:

macOS

SQLite Version:

3.43.2 2023-10-10 13:08:14 1b37c146ee9ebb7acd0160c0ab1fd11017a419fa8a3187386ed8cb32b709aapl (64-bit)

DuckDB Version:

v1.1.1 af39bd0dcf

DuckDB Client:

CLI

Full Name:

Yuu Yamashita

Affiliation:

Treasure Data, Inc.

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