Repository: duckdb/duckdb (language: python)

Issue:
"Table with name [...] does not exist" when reading file that exists
### What happens?

Trying to read a zipped CSV file and getting an error that the file doesn't exist. I've verified it exists.

### To Reproduce

I'm trying to read a zipped CSV file from the DuckDB shell:

```
D select count(*) from '~/03nov2023.csv.zip';
```

but getting an error:

```sh
Catalog Error: Table with name ~/03nov2023.csv.zip does not exist!
Did you mean "temp.information_schema.schemata"?
LINE 1: select count(*) from '~/03nov2023.csv.zip';
```

I suspect that DuckDB may not like my ZIP file (or may not decompress ZIP files at all); if that's the case, I think the error message could be tweaked to say something like "DuckDB does not decompress ZIP files" rather than "the file doesn't exist". I'm new to the project, but happy to help contribute in any way I can 😄 

### OS:

macOS 14.4.1 M1 (arm64)

### DuckDB Version:

v1.0.0 1f98600c2c

### DuckDB Client:

DuckDB shell

### Hardware:

_No response_

### Full Name:

Robert Martin

### Affiliation:

Robert Martin

### 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?

- [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
"Table with name [...] does not exist" when reading file that exists
### What happens?

Trying to read a zipped CSV file and getting an error that the file doesn't exist. I've verified it exists.

### To Reproduce

I'm trying to read a zipped CSV file from the DuckDB shell:

```
D select count(*) from '~/03nov2023.csv.zip';
```

but getting an error:

```sh
Catalog Error: Table with name ~/03nov2023.csv.zip does not exist!
Did you mean "temp.information_schema.schemata"?
LINE 1: select count(*) from '~/03nov2023.csv.zip';
```

I suspect that DuckDB may not like my ZIP file (or may not decompress ZIP files at all); if that's the case, I think the error message could be tweaked to say something like "DuckDB does not decompress ZIP files" rather than "the file doesn't exist". I'm new to the project, but happy to help contribute in any way I can 😄 

### OS:

macOS 14.4.1 M1 (arm64)

### DuckDB Version:

v1.0.0 1f98600c2c

### DuckDB Client:

DuckDB shell

### Hardware:

_No response_

### Full Name:

Robert Martin

### Affiliation:

Robert Martin

### 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?

- [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:
The issue here is that `from 'file.csv.zip'` does not trigger the replacement scan that transforms to `from read_csv('file.csv.zip')` as the file extension isnt recognized

That might be fixed on 1.1.0 as work was recently done on this logic iirc
Thanks for the quick and helpful answer @Tishj!

Just to clarify, does DuckDB not perform ZIP decompression?
@bertday no unfortunately :(
That's helpful to know. Thank you @djouallah 😄 
The issue here is that `from 'file.csv.zip'` does not trigger the replacement scan that transforms to `from read_csv('file.csv.zip')` as the file extension isnt recognized

That might be fixed on 1.1.0 as work was recently done on this logic iirc
Thanks for the quick and helpful answer @Tishj!

Just to clarify, does DuckDB not perform ZIP decompression?
@bertday no unfortunately :(
That's helpful to know. Thank you @djouallah 😄 

Fix patch:
diff --git a/src/planner/binder/tableref/bind_basetableref.cpp b/src/planner/binder/tableref/bind_basetableref.cpp
index 40f2662d4500..d3e65e8b9fe7 100644
--- a/src/planner/binder/tableref/bind_basetableref.cpp
+++ b/src/planner/binder/tableref/bind_basetableref.cpp
@@ -202,6 +202,17 @@ unique_ptr<BoundTableRef> Binder::Bind(BaseTableRef &ref) {
 				return replacement_scan_bind_result;
 			}
 		}
+		auto &config = DBConfig::GetConfig(context);
+		if (context.config.use_replacement_scans && config.options.enable_external_access &&
+		    ExtensionHelper::IsFullPath(full_path)) {
+			auto &fs = FileSystem::GetFileSystem(context);
+			if (fs.FileExists(full_path)) {
+				throw BinderException(
+				    "No extension found that is capable of reading the file \"%s\"\n* If this file is a supported file "
+				    "format you can explicitly use the reader functions, such as read_csv, read_json or read_parquet",
+				    full_path);
+			}
+		}
 
 		// could not find an alternative: bind again to get the error
 		(void)entry_retriever.GetEntry(CatalogType::TABLE_ENTRY, ref.catalog_name, ref.schema_name, ref.table_name,


Test patch:
diff --git a/test/sql/copy/parquet/replacement_scan_custom_extension.test b/test/sql/copy/parquet/replacement_scan_custom_extension.test
new file mode 100644
index 000000000000..843e4a7458c1
--- /dev/null
+++ b/test/sql/copy/parquet/replacement_scan_custom_extension.test
@@ -0,0 +1,21 @@
+# name: test/sql/copy/parquet/replacement_scan_custom_extension.test
+# description: Test unknown extensions in replacement scans
+# group: [parquet]
+
+require parquet
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+COPY (SELECT 42 a) to '__TEST_DIR__/lists.my_file_extension' (FORMAT PARQUET);
+
+statement error
+FROM '__TEST_DIR__/lists.my_file_extension';
+----
+No extension found that is capable of reading the file
+
+query I
+FROM read_parquet('__TEST_DIR__/lists.my_file_extension')
+----
+42