perf: SELECT FROM a single-file URI triggers recursive directory listing instead of reading the file directly

[bohutang]

Problem

SELECT * FROM 'fs:///par/foods.parquet' (file_format => 'parquet')
-- Takes 2.28s in a directory with many files, should be instant

When the URI explicitly names a single file (foods.parquet), Databend still lists all files in the parent directory recursively before reading. This affects all storage backends (fs, S3, GCS, Azure Blob, OSS, COS) and all file formats.

On cloud storage the impact is worse: S3 LIST returns max 1000 objects per call, so large directories require many network round-trips.

Root Cause

bind_location.rs:64-68 splits the URI into a directory path and leaves StageFilesInfo.files empty. Downstream, both ParquetTable::do_read_partitions (partition.rs:65) and StageTable::read_partitions_simple (stage_table.rs:94) see files = None and call StageFilesInfo::list(), which runs operator.lister_with(path).recursive(true) (stage.rs:263).

StageFilesInfo.files is only populated when using the explicit FILES => clause.

Fix

When the URI path has a file extension (i.e. points to a specific file, not a directory), populate StageFilesInfo.files with that filename so the list step is skipped entirely.

Workaround

SELECT * FROM 'fs:///par/' (files => ('foods.parquet'), file_format => 'parquet')

[youngsofun]

The path in Databend is treated as a prefix

1. A path like par/foods.parquet is not necessarily a file. It could be a directory containing many files generated by systems such as Databend, Spark, or Snowflake.
2. At databend level, we do not need to traverse parent directories. We only attempt to list /par/foods.parquet.
3. list the parent directory because the FS backend can not list by prefix by itself, opendal have to list its parent dir, to find other files with the prefix, e.g. /par/foods.parquet.bk. https://github.com/apache/opendal/blob/v0.54.1/core/src/layers/complete.rs#L168,

we have 2 options:

1. adjust the prefix semantics a little: if the path /par/foods.parquet is found to be a file, read it only, instead of treat it as a prefix； this can be combined with a checking of suffix.

   databend/src/common/storage/src/stage.rs

   Line 248 in c234f47

   let file_exact: Option<Result<StageFileInfo>> = match prefix_meta {

2. ignore it, keep prefix semantics clear and simple (like snowflake). the since FS backend is not typically used in production. Note even for s3, a list op return empty is wasted most of the time for this case. other backend that list_with_recursive=false: hdfs, webhdfs, ftp, http, ipfs, moka

cc @wubx @sundy-li

[sundy-li]

I think we should have three modes to process stage file by pattern and wildcard.

function resolve_stage_access(path, options):
    if options.FILES is not empty:
        return Mode.MULTI_FILE_EXPLICIT

    if options.PATTERN is not null:
        return Mode.PREFIX_SCAN

    if contains_wildcard(path):        # * ? []
        return Mode.PREFIX_SCAN

    if path.ends_with("/"):
        return Mode.PREFIX_SCAN

    return Mode.SINGLE_FILE

If mode is single/multi file we don't need to call list api any more.

[youngsofun]

this will be a big breaking change

    if path.ends_with("/"):
        return Mode.PREFIX_SCAN

    return Mode.SINGLE_FILE

support wildcard is another topic, we can discuss it in another issue