Repository: duckdb/duckdb (language: python)

Issue:
Add tests for sha1 function
This is a followup to #13020 that adds some basic tests for the new `sha1` function, modeled after the existing tests for `sha512`.
Catalog Errors only offer one suggestion when multiple suggestions are returned with the same score
### What happens?

Consider the case where there are multiple schemas with the same matching score for a Catalog error.

```sql
create schema a;
create schema b;
create table a.foo(name text);
create table b.foo(name text);
select * from foo;
```

```
Catalog Error: Table with name foo does not exist!
Did you mean "b.foo"?
LINE 1: select * from foo;
```

There is an equally qualified match in schema `a`, but Catalog Error just presents the last match.

Here is the relevant snippet of code.

https://github.com/duckdb/duckdb/blob/45787e5f9f8bdb9dce97890c1ac7a7eb2dc3a49f/src/catalog/catalog.cpp#L639-L654

I'd suggest that the code should be changed to present all suggestions that have the same score so that the a user that has tables in multiple schemas with the same name can make the appropriate choice.

Thanks,

Rusty

### To Reproduce

```sql
create schema a;
create schema b;
create table a.foo(name text);
create table b.foo(name text);
select * from foo;
```


### OS:

Mac OS X

### DuckDB Version:

1.0.0

### DuckDB Client:

CLI

### Full Name:

Rusty Conover

### Affiliation:

self

### 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:
Thanks


Fix patch:
diff --git a/src/catalog/catalog.cpp b/src/catalog/catalog.cpp
index 9bbcb1f4825f..1adb05c5953a 100644
--- a/src/catalog/catalog.cpp
+++ b/src/catalog/catalog.cpp
@@ -367,9 +367,10 @@ SchemaCatalogEntry &Catalog::GetSchema(CatalogTransaction transaction, const str
 //===--------------------------------------------------------------------===//
 // Lookup
 //===--------------------------------------------------------------------===//
-SimilarCatalogEntry Catalog::SimilarEntryInSchemas(ClientContext &context, const string &entry_name, CatalogType type,
-                                                   const reference_set_t<SchemaCatalogEntry> &schemas) {
-	SimilarCatalogEntry result;
+vector<SimilarCatalogEntry> Catalog::SimilarEntriesInSchemas(ClientContext &context, const string &entry_name,
+                                                             CatalogType type,
+                                                             const reference_set_t<SchemaCatalogEntry> &schemas) {
+	vector<SimilarCatalogEntry> results;
 	for (auto schema_ref : schemas) {
 		auto &schema = schema_ref.get();
 		auto transaction = schema.catalog.GetCatalogTransaction(context);
@@ -378,12 +379,16 @@ SimilarCatalogEntry Catalog::SimilarEntryInSchemas(ClientContext &context, const
 			// no similar entry found
 			continue;
 		}
-		if (!result.Found() || result.score < entry.score) {
-			result = entry;
-			result.schema = &schema;
+		if (results.empty() || results[0].score <= entry.score) {
+			if (!results.empty() && results[0].score < entry.score) {
+				results.clear();
+			}
+
+			results.push_back(entry);
+			results.back().schema = &schema;
 		}
 	}
-	return result;
+	return results;
 }
 
 vector<CatalogSearchEntry> GetCatalogEntries(CatalogEntryRetriever &retriever, const string &catalog,
@@ -588,7 +593,7 @@ CatalogException Catalog::CreateMissingEntryException(CatalogEntryRetriever &ret
                                                       const reference_set_t<SchemaCatalogEntry> &schemas,
                                                       QueryErrorContext error_context) {
 	auto &context = retriever.GetContext();
-	auto entry = SimilarEntryInSchemas(context, entry_name, type, schemas);
+	auto entries = SimilarEntriesInSchemas(context, entry_name, type, schemas);
 
 	reference_set_t<SchemaCatalogEntry> unseen_schemas;
 	auto &db_manager = DatabaseManager::Get(context);
@@ -668,20 +673,36 @@ CatalogException Catalog::CreateMissingEntryException(CatalogEntryRetriever &ret
 	// entries in other schemas get a penalty
 	// however, if there is an exact match in another schema, we will always show it
 	static constexpr const double UNSEEN_PENALTY = 0.2;
-	auto unseen_entry = SimilarEntryInSchemas(context, entry_name, type, unseen_schemas);
-	string did_you_mean;
-	if (unseen_entry.Found() && (unseen_entry.score == 1.0 || unseen_entry.score - UNSEEN_PENALTY > entry.score)) {
+	auto unseen_entries = SimilarEntriesInSchemas(context, entry_name, type, unseen_schemas);
+	vector<string> suggestions;
+	if (!unseen_entries.empty() && (unseen_entries[0].score == 1.0 || unseen_entries[0].score - UNSEEN_PENALTY >
+	                                                                      (entries.empty() ? 0.0 : entries[0].score))) {
 		// the closest matching entry requires qualification as it is not in the default search path
 		// check how to minimally qualify this entry
-		auto catalog_name = unseen_entry.schema->catalog.GetName();
-		auto schema_name = unseen_entry.schema->name;
-		bool qualify_database;
-		bool qualify_schema;
-		FindMinimalQualification(retriever, catalog_name, schema_name, qualify_database, qualify_schema);
-		did_you_mean = unseen_entry.GetQualifiedName(qualify_database, qualify_schema);
-	} else if (entry.Found()) {
-		did_you_mean = entry.name;
+		for (auto &unseen_entry : unseen_entries) {
+			auto catalog_name = unseen_entry.schema->catalog.GetName();
+			auto schema_name = unseen_entry.schema->name;
+			bool qualify_database;
+			bool qualify_schema;
+			FindMinimalQualification(retriever, catalog_name, schema_name, qualify_database, qualify_schema);
+			suggestions.push_back(unseen_entry.GetQualifiedName(qualify_database, qualify_schema));
+		}
+	} else if (!entries.empty()) {
+		for (auto &entry : entries) {
+			suggestions.push_back(entry.name);
+		}
+	}
+
+	string did_you_mean;
+	std::sort(suggestions.begin(), suggestions.end());
+	if (suggestions.size() > 2) {
+		auto last = suggestions.back();
+		suggestions.pop_back();
+		did_you_mean = StringUtil::Join(suggestions, ", ") + ", or " + last;
+	} else {
+		did_you_mean = StringUtil::Join(suggestions, " or ");
 	}
+
 	return CatalogException::MissingEntry(type, entry_name, did_you_mean, error_context);
 }
 
diff --git a/src/include/duckdb/catalog/catalog.hpp b/src/include/duckdb/catalog/catalog.hpp
index 2bf7fad6e3c2..5460199c07c1 100644
--- a/src/include/duckdb/catalog/catalog.hpp
+++ b/src/include/duckdb/catalog/catalog.hpp
@@ -370,8 +370,9 @@ class Catalog {
 	                                                    QueryErrorContext error_context);
 
 	//! Return the close entry name, the distance and the belonging schema.
-	static SimilarCatalogEntry SimilarEntryInSchemas(ClientContext &context, const string &entry_name, CatalogType type,
-	                                                 const reference_set_t<SchemaCatalogEntry> &schemas);
+	static vector<SimilarCatalogEntry> SimilarEntriesInSchemas(ClientContext &context, const string &entry_name,
+	                                                           CatalogType type,
+	                                                           const reference_set_t<SchemaCatalogEntry> &schemas);
 
 	virtual void DropSchema(ClientContext &context, DropInfo &info) = 0;
 


Test patch:
diff --git a/test/sql/catalog/did_you_mean.test b/test/sql/catalog/did_you_mean.test
index bac86754556f..92d25f7f20ab 100644
--- a/test/sql/catalog/did_you_mean.test
+++ b/test/sql/catalog/did_you_mean.test
@@ -22,3 +22,35 @@ statement error
 SELECT * FROM bye;
 ----
 Did you mean "test.bye
+
+statement ok
+CREATE SCHEMA a;
+CREATE TABLE a.foo(name text);
+
+statement error
+SELECT * FROM foo;
+----
+Did you mean "a.foo"?
+
+statement ok
+CREATE SCHEMA b;
+CREATE TABLE b.foo(name text);
+
+statement error
+SELECT * FROM foo;
+----
+Did you mean "a.foo or b.foo"?
+
+statement ok
+CREATE SCHEMA c;
+CREATE TABLE c.foo(name text);
+
+statement error
+SELECT * FROM foo;
+----
+Did you mean "a.foo, b.foo, or c.foo"?
+
+statement error
+SELECT * FROM a.fooo;
+----
+Did you mean "foo"?