Repository: duckdb/duckdb (language: python)

Issue:
sniff_csv fails to detect compression when reading from a url that has a query string
### What happens?

`sniff_csv` fails to  detect compression when reading from a url that has a query string.

For example this statement
```sql
from sniff_csv('https://github.com/duckdb/duckdb/raw/main/data/csv/who.csv.gz?v=1');
```
Fails withs this error
```text
Error: Invalid Input Error: Invalid unicode (byte sequence mismatch) detected in value construction
```

but this statement succeeds
```sql
from sniff_csv('https://github.com/duckdb/duckdb/raw/main/data/csv/who.csv.gz');
```

The root cause of this is in the file at line src/common/virtual_file_system.cpp at line 15 https://github.com/duckdb/duckdb/blob/4d24f5c660a205bf22a7fd99e36efece798452c4/src/common/virtual_file_system.cpp#L15 and could be fixed with a check for url and remove the query string path.


I will open a PR for this.


### To Reproduce

Run this statement
```sql
from sniff_csv('https://github.com/duckdb/duckdb/raw/main/data/csv/who.csv.gz?v=1');
```

### OS:

all

### DuckDB Version:

0.9.3

### DuckDB Client:

all

### Full Name:

Gabriel Hodoroaga

### Affiliation:

Bobsled

### Have you tried this on the latest [nightly build](https://duckdb.org/docs/installation/?version=main)?

I have tested with a nightly build

### Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

- [X] Yes, I have


Discussion / hints:
> I will open a PR for this.

Please don't do that today, we're trying to not put unnecessary pressure on the CI as we're looking to release later today
This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.
This issue was closed because it has been stale for 30 days with no activity.

Fix patch:
diff --git a/src/common/enums/file_compression_type.cpp b/src/common/enums/file_compression_type.cpp
index 5df6add19cb3..44066f32c743 100644
--- a/src/common/enums/file_compression_type.cpp
+++ b/src/common/enums/file_compression_type.cpp
@@ -19,4 +19,28 @@ FileCompressionType FileCompressionTypeFromString(const string &input) {
 	}
 }
 
+string CompressionExtensionFromType(const FileCompressionType type) {
+	switch (type) {
+	case FileCompressionType::GZIP:
+		return ".gz";
+	case FileCompressionType::ZSTD:
+		return ".zst";
+	default:
+		throw NotImplementedException("Compression Extension of file compression type is not implemented");
+	}
+}
+
+bool IsFileCompressed(string path, FileCompressionType type) {
+	auto extension = CompressionExtensionFromType(type);
+	std::size_t question_mark_pos = std::string::npos;
+	if (!StringUtil::StartsWith(path, "\\\\?\\")) {
+		question_mark_pos = path.find('?');
+	}
+	path = path.substr(0, question_mark_pos);
+	if (StringUtil::EndsWith(path, extension)) {
+		return true;
+	}
+	return false;
+}
+
 } // namespace duckdb
diff --git a/src/common/virtual_file_system.cpp b/src/common/virtual_file_system.cpp
index 3bc099a2bd01..74892a4e0590 100644
--- a/src/common/virtual_file_system.cpp
+++ b/src/common/virtual_file_system.cpp
@@ -13,15 +13,15 @@ unique_ptr<FileHandle> VirtualFileSystem::OpenFile(const string &path, FileOpenF
                                                    optional_ptr<FileOpener> opener) {
 	auto compression = flags.Compression();
 	if (compression == FileCompressionType::AUTO_DETECT) {
-		// auto detect compression settings based on file name
+		// auto-detect compression settings based on file name
 		auto lower_path = StringUtil::Lower(path);
 		if (StringUtil::EndsWith(lower_path, ".tmp")) {
 			// strip .tmp
 			lower_path = lower_path.substr(0, lower_path.length() - 4);
 		}
-		if (StringUtil::EndsWith(lower_path, ".gz")) {
+		if (IsFileCompressed(path, FileCompressionType::GZIP)) {
 			compression = FileCompressionType::GZIP;
-		} else if (StringUtil::EndsWith(lower_path, ".zst")) {
+		} else if (IsFileCompressed(path, FileCompressionType::ZSTD)) {
 			compression = FileCompressionType::ZSTD;
 		} else {
 			compression = FileCompressionType::UNCOMPRESSED;
diff --git a/src/function/table/copy_csv.cpp b/src/function/table/copy_csv.cpp
index a94df0b7a6ad..b2c16a671721 100644
--- a/src/function/table/copy_csv.cpp
+++ b/src/function/table/copy_csv.cpp
@@ -182,13 +182,13 @@ static unique_ptr<FunctionData> WriteCSVBind(ClientContext &context, CopyFunctio
 
 	switch (bind_data->options.compression) {
 	case FileCompressionType::GZIP:
-		if (!StringUtil::EndsWith(input.file_extension, ".gz")) {
-			input.file_extension += ".gz";
+		if (!IsFileCompressed(input.file_extension, FileCompressionType::GZIP)) {
+			input.file_extension += CompressionExtensionFromType(FileCompressionType::GZIP);
 		}
 		break;
 	case FileCompressionType::ZSTD:
-		if (!StringUtil::EndsWith(input.file_extension, ".zst")) {
-			input.file_extension += ".zst";
+		if (!IsFileCompressed(input.file_extension, FileCompressionType::ZSTD)) {
+			input.file_extension += CompressionExtensionFromType(FileCompressionType::ZSTD);
 		}
 		break;
 	default:
diff --git a/src/function/table/read_csv.cpp b/src/function/table/read_csv.cpp
index 7d78c8f23561..934641567e87 100644
--- a/src/function/table/read_csv.cpp
+++ b/src/function/table/read_csv.cpp
@@ -390,9 +390,9 @@ unique_ptr<TableRef> ReadCSVReplacement(ClientContext &context, ReplacementScanI
 	auto table_name = ReplacementScan::GetFullPath(input);
 	auto lower_name = StringUtil::Lower(table_name);
 	// remove any compression
-	if (StringUtil::EndsWith(lower_name, ".gz")) {
+	if (StringUtil::EndsWith(lower_name, CompressionExtensionFromType(FileCompressionType::GZIP))) {
 		lower_name = lower_name.substr(0, lower_name.size() - 3);
-	} else if (StringUtil::EndsWith(lower_name, ".zst")) {
+	} else if (StringUtil::EndsWith(lower_name, CompressionExtensionFromType(FileCompressionType::ZSTD))) {
 		if (!Catalog::TryAutoLoad(context, "parquet")) {
 			throw MissingExtensionException("parquet extension is required for reading zst compressed file");
 		}
diff --git a/src/function/table/sniff_csv.cpp b/src/function/table/sniff_csv.cpp
index 0df333517afd..56aa4a957c5a 100644
--- a/src/function/table/sniff_csv.cpp
+++ b/src/function/table/sniff_csv.cpp
@@ -110,13 +110,14 @@ static void CSVSniffFunction(ClientContext &context, TableFunctionInput &data_p,
 	const CSVSniffFunctionData &data = data_p.bind_data->Cast<CSVSniffFunctionData>();
 	auto &fs = duckdb::FileSystem::GetFileSystem(context);
 
-	if (data.path.rfind("http://", 0) != 0 && data.path.rfind("https://", 0) != 0 && fs.HasGlob(data.path)) {
-		throw NotImplementedException("sniff_csv does not operate on globs yet");
+	auto paths = fs.GlobFiles(data.path, context, FileGlobOptions::DISALLOW_EMPTY);
+	if (paths.size() > 1) {
+		throw NotImplementedException("sniff_csv does not operate on more than one file yet");
 	}
 
 	// We must run the sniffer.
 	auto sniffer_options = data.options;
-	sniffer_options.file_path = data.path;
+	sniffer_options.file_path = paths[0];
 
 	auto buffer_manager = make_shared_ptr<CSVBufferManager>(context, sniffer_options, sniffer_options.file_path, 0);
 	if (sniffer_options.name_list.empty()) {
@@ -204,7 +205,7 @@ static void CSVSniffFunction(ClientContext &context, TableFunctionInput &data_p,
 	std::ostringstream csv_read;
 
 	// Base, Path and auto_detect=false
-	csv_read << "FROM read_csv('" << data.path << "'" << separator << "auto_detect=false" << separator;
+	csv_read << "FROM read_csv('" << paths[0] << "'" << separator << "auto_detect=false" << separator;
 	// 10.1. Delimiter
 	if (!sniffer_options.dialect_options.state_machine_options.delimiter.IsSetByUser()) {
 		csv_read << "delim="
diff --git a/src/include/duckdb/common/enums/file_compression_type.hpp b/src/include/duckdb/common/enums/file_compression_type.hpp
index 98fe5e75c532..77b66b3920d4 100644
--- a/src/include/duckdb/common/enums/file_compression_type.hpp
+++ b/src/include/duckdb/common/enums/file_compression_type.hpp
@@ -16,4 +16,8 @@ enum class FileCompressionType : uint8_t { AUTO_DETECT = 0, UNCOMPRESSED = 1, GZ
 
 FileCompressionType FileCompressionTypeFromString(const string &input);
 
+string CompressionExtensionFromType(const FileCompressionType type);
+
+bool IsFileCompressed(string path, FileCompressionType type);
+
 } // namespace duckdb
diff --git a/src/include/duckdb/function/replacement_scan.hpp b/src/include/duckdb/function/replacement_scan.hpp
index edaf455e56b2..75ce069a846e 100644
--- a/src/include/duckdb/function/replacement_scan.hpp
+++ b/src/include/duckdb/function/replacement_scan.hpp
@@ -10,6 +10,7 @@
 
 #include "duckdb/common/common.hpp"
 #include "duckdb/common/string_util.hpp"
+#include "duckdb/common/enums/file_compression_type.hpp"
 
 namespace duckdb {
 
@@ -59,9 +60,9 @@ struct ReplacementScan {
 	static bool CanReplace(const string &table_name, const vector<string> &extensions) {
 		auto lower_name = StringUtil::Lower(table_name);
 
-		if (StringUtil::EndsWith(lower_name, ".gz")) {
+		if (StringUtil::EndsWith(lower_name, CompressionExtensionFromType(FileCompressionType::GZIP))) {
 			lower_name = lower_name.substr(0, lower_name.size() - 3);
-		} else if (StringUtil::EndsWith(lower_name, ".zst")) {
+		} else if (StringUtil::EndsWith(lower_name, CompressionExtensionFromType(FileCompressionType::ZSTD))) {
 			lower_name = lower_name.substr(0, lower_name.size() - 4);
 		}
 
diff --git a/src/main/extension/extension_install.cpp b/src/main/extension/extension_install.cpp
index af93a0dd613c..f45f3413cc9d 100644
--- a/src/main/extension/extension_install.cpp
+++ b/src/main/extension/extension_install.cpp
@@ -209,7 +209,7 @@ string ExtensionHelper::ExtensionUrlTemplate(optional_ptr<const DatabaseInstance
 	versioned_path = versioned_path + ".wasm";
 #else
 	string default_endpoint = ExtensionRepository::DEFAULT_REPOSITORY_URL;
-	versioned_path = versioned_path + ".gz";
+	versioned_path = versioned_path + CompressionExtensionFromType(FileCompressionType::GZIP);
 #endif
 	string url_template = repository.path + versioned_path;
 	return url_template;
@@ -290,7 +290,7 @@ static unique_ptr<ExtensionInstallInfo> DirectInstallExtension(DatabaseInstance
 	bool exists = fs.FileExists(file);
 
 	// Recheck without .gz
-	if (!exists && StringUtil::EndsWith(file, ".gz")) {
+	if (!exists && StringUtil::EndsWith(file, CompressionExtensionFromType(FileCompressionType::GZIP))) {
 		file = file.substr(0, file.size() - 3);
 		exists = fs.FileExists(file);
 	}


Test patch:
diff --git a/test/sql/copy/csv/test_sniff_csv.test b/test/sql/copy/csv/test_sniff_csv.test
index 1b4ba47f0b06..ed65b4f06796 100644
--- a/test/sql/copy/csv/test_sniff_csv.test
+++ b/test/sql/copy/csv/test_sniff_csv.test
@@ -27,7 +27,7 @@ FROM sniff_csv('data/csv/real/lineitem_sample.csv');
 statement error
 FROM sniff_csv('data/csv/real/non_ecziste.csv');
 ----
-Cannot open file "data/csv/real/non_ecziste.csv": No such file or directory
+No files found that match the pattern "data/csv/real/non_ecziste.csv"
 
 # Test different sample sizes
 
diff --git a/test/sql/copy/csv/test_sniff_csv_options.test b/test/sql/copy/csv/test_sniff_csv_options.test
index e19e72adcf1d..da8cdaa66f03 100644
--- a/test/sql/copy/csv/test_sniff_csv_options.test
+++ b/test/sql/copy/csv/test_sniff_csv_options.test
@@ -118,7 +118,7 @@ FROM read_csv('data/csv/autotypecandidates.csv', auto_detect=false, delim='|', q
 statement error
 FROM sniff_csv('data/csv/hive-partitioning/simple/*/*/test.csv');
 ----
-sniff_csv does not operate on globs yet
+Not implemented Error: sniff_csv does not operate on more than one file yet
 
 # don't accept madeup options
 statement error
diff --git a/test/sql/copy/csv/test_sniff_httpfs.test b/test/sql/copy/csv/test_sniff_httpfs.test
new file mode 100644
index 000000000000..ea36b9be17ab
--- /dev/null
+++ b/test/sql/copy/csv/test_sniff_httpfs.test
@@ -0,0 +1,14 @@
+# name: test/sql/copy/csv/test_sniff_httpfs.test
+# description: Test sniff_csv functions over httpfs with auto-detection on compression
+# group: [csv]
+
+require httpfs
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+from sniff_csv('https://github.com/duckdb/duckdb/raw/main/data/csv/who.csv.gz');
+
+statement ok
+from sniff_csv('https://github.com/duckdb/duckdb/raw/main/data/csv/who.csv.gz?v=1');
diff --git a/test/sqlite/sqllogic_test_runner.cpp b/test/sqlite/sqllogic_test_runner.cpp
index 762928175ac3..daf384750bee 100644
--- a/test/sqlite/sqllogic_test_runner.cpp
+++ b/test/sqlite/sqllogic_test_runner.cpp
@@ -893,7 +893,7 @@ void SQLLogicTestRunner::ExecuteFile(string script) {
 
 			// file name
 			idx_t filename_start_pos = input_path.find_last_of("/") + 1;
-			if (!StringUtil::EndsWith(input_path, ".gz")) {
+			if (!StringUtil::EndsWith(input_path, CompressionExtensionFromType(FileCompressionType::GZIP))) {
 				parser.Fail("unzip: input has not a GZIP extension");
 			}
 			string filename = input_path.substr(filename_start_pos, input_path.size() - filename_start_pos - 3);