Repository: duckdb/duckdb (language: python)

Issue:
Cannot reference user defined type across schemas
### What happens?

When creating a type in a different schema, I cannot seem to reference it across schemas

### To Reproduce

```sql
CREATE SCHEMA s;
CREATE TYPE s.u AS STRUCT (a int, b int);
-- This works
CREATE TABLE s.t (
  u s.u
);

-- This doesn't work
CREATE TABLE main.t (
  u s.u
);
```

The error message is kind of ironic:

```
Catalog Error: Type with name u does not exist!
Did you mean "s.u"?
```

Related:

- https://github.com/duckdb/duckdb/issues/8836

### OS:

Microsoft Windows [Version 10.0.22631.4169]

### DuckDB Version:

1.1.0

### DuckDB Client:

JDBC

### Hardware:

_No response_

### Full Name:

Lukas Eder

### Affiliation:

Data Geekery

### 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?

Yes

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/planner/binder/statement/bind_create.cpp b/src/planner/binder/statement/bind_create.cpp
index f03f260c4889..a35d6e13c802 100644
--- a/src/planner/binder/statement/bind_create.cpp
+++ b/src/planner/binder/statement/bind_create.cpp
@@ -249,6 +249,13 @@ SchemaCatalogEntry &Binder::BindCreateFunctionInfo(CreateInfo &info) {
 	return BindCreateSchema(info);
 }
 
+static bool IsValidUserType(optional_ptr<CatalogEntry> entry) {
+	if (!entry) {
+		return false;
+	}
+	return entry->Cast<TypeCatalogEntry>().user_type.id() != LogicalTypeId::INVALID;
+}
+
 void Binder::BindLogicalType(LogicalType &type, optional_ptr<Catalog> catalog, const string &schema) {
 	if (type.id() == LogicalTypeId::LIST || type.id() == LogicalTypeId::MAP) {
 		auto child_type = ListType::GetChildType(type);
@@ -297,24 +304,34 @@ void Binder::BindLogicalType(LogicalType &type, optional_ptr<Catalog> catalog, c
 		type.SetModifiers(modifiers);
 	} else if (type.id() == LogicalTypeId::USER) {
 		auto user_type_name = UserType::GetTypeName(type);
+		auto user_type_schema = UserType::GetSchema(type);
 		auto user_type_mods = UserType::GetTypeModifiers(type);
 
 		bind_type_modifiers_function_t user_bind_modifiers_func = nullptr;
 
 		if (catalog) {
 			// The search order is:
-			// 1) In the same schema as the table
-			// 2) In the same catalog
-			// 3) System catalog
-			auto entry = entry_retriever.GetEntry(CatalogType::TYPE_ENTRY, *catalog, schema, user_type_name,
-			                                      OnEntryNotFound::RETURN_NULL);
-			if (!entry || entry->Cast<TypeCatalogEntry>().user_type.id() == LogicalTypeId::INVALID) {
+			// 1) In the explicitly set schema (my_schema.my_type)
+			// 2) In the same schema as the table
+			// 3) In the same catalog
+			// 4) System catalog
+
+			optional_ptr<CatalogEntry> entry = nullptr;
+			if (!user_type_schema.empty()) {
+				entry = entry_retriever.GetEntry(CatalogType::TYPE_ENTRY, *catalog, user_type_schema, user_type_name,
+				                                 OnEntryNotFound::RETURN_NULL);
+			}
+			if (!IsValidUserType(entry)) {
+				entry = entry_retriever.GetEntry(CatalogType::TYPE_ENTRY, *catalog, schema, user_type_name,
+				                                 OnEntryNotFound::RETURN_NULL);
+			}
+			if (!IsValidUserType(entry)) {
 				entry = entry_retriever.GetEntry(CatalogType::TYPE_ENTRY, *catalog, INVALID_SCHEMA, user_type_name,
 				                                 OnEntryNotFound::RETURN_NULL);
-				if (!entry || entry->Cast<TypeCatalogEntry>().user_type.id() == LogicalTypeId::INVALID) {
-					entry = entry_retriever.GetEntry(CatalogType::TYPE_ENTRY, INVALID_CATALOG, INVALID_SCHEMA,
-					                                 user_type_name, OnEntryNotFound::THROW_EXCEPTION);
-				}
+			}
+			if (!IsValidUserType(entry)) {
+				entry = entry_retriever.GetEntry(CatalogType::TYPE_ENTRY, INVALID_CATALOG, INVALID_SCHEMA,
+				                                 user_type_name, OnEntryNotFound::THROW_EXCEPTION);
 			}
 			auto &type_entry = entry->Cast<TypeCatalogEntry>();
 			type = type_entry.user_type;


Test patch:
diff --git a/test/sql/types/alias/type_with_schema.test b/test/sql/types/alias/type_with_schema.test
new file mode 100644
index 000000000000..f142ad1960b7
--- /dev/null
+++ b/test/sql/types/alias/type_with_schema.test
@@ -0,0 +1,20 @@
+# name: test/sql/types/alias/type_with_schema.test
+# group: [alias]
+
+statement ok
+CREATE SCHEMA my_schema;
+
+statement ok
+CREATE TYPE my_schema.my_type AS STRUCT (
+	a int, b int
+);
+
+statement ok
+CREATE TABLE my_schema.tbl (
+	c0 my_schema.my_type
+);
+
+statement ok
+CREATE TABLE main.tbl (
+	c0 my_schema.my_type
+);