CREATE UNIQUE INDEX IF NOT EXISTS doesn't work any more

[davetapley]

What happens?

CREATE UNIQUE INDEX IF NOT EXISTS works as expected on 0.9.2, but throws Catalog Error on 0.10.1.

Note: this only occurs with UNIQUE, otherwise it's okay 🤔

---

I'll concede that CREATE INDEX IF NOT EXISTS it isn't 'explicitly mentioned in the docs,
but it is covered by tests (albeit not the failing UNIQUE case) ⬇️,
and since it worked as expected in 0.9.2 I'm hoping we can call this a Real Bug 🤞🏻

duckdb/test/sql/catalog/test_catalog_errors.test

Lines 36 to 46 in 4750ce2

	statement ok
	CREATE INDEX i_index ON integers(i);
	# cannot create an index that already exists
	statement error
	CREATE INDEX i_index ON integers(i);
	----
	# with IF NOT EXISTS it does not fail!
	statement ok
	CREATE INDEX IF NOT EXISTS i_index ON integers(i);

To Reproduce

Now:

v0.10.1 4a89d97db8

create table foo(bar int);
create unique index foo_bar_unique on foo(bar);
create unique index if not exists foo_bar_unique on foo(bar);

Catalog Error: An index with the name foo_bar_uniquealready exists!

But previously:

v0.9.2 3c695d7ba9

create table foo(bar int);
create unique index foo_bar_unique on foo(bar);
create unique index if not exists foo_bar_unique on foo(bar);

OS:

Ubuntu

DuckDB Version:

20.04.6

DuckDB Client:

Python and CLI

Full Name:

Dave Tapley

Affiliation:

JE Fuller

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

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

[ewencp]

This was introduced by 8188cd959f3b45d1c2009bda25f663c19eef7434, which tries to handle automatic indexes (pk, fk, unique) better. But in doing so, it short circuits later checks. Since unique indexes could come from a column constraint or just be created by the user, we need to at least handle the IGNORE_ON_CONFLICT case when checking the index types.