[R] preserve hive partitions when opening along a path / path vector

### Describe the enhancement requested

If we use `open_dataset()` when giving a path or vector of paths in which the path includes a hive partition label, that label is lost to the dataset. Quick minimal example:

```r
# create a partitioned dataset:
arrow::write_dataset(mtcars, "~/testdir", partitioning = c("gear", "cyl"))

# open partitioned dataset subdir, witness "gear" column is lost:
cars <- arrow::open_dataset("~/testdir/gear=3")
"gear" %in% colnames(cars)
```

Compare this to opening the same partition dataset but using `duckdb` instead of `arrow`, where when using hive partitions, the hive partition is automatically identified and retained:

```r
cars <- duckdbfs::open_dataset("~/testdir/gear=3")
"gear" %in% colnames(cars)
```

I think arrow should be able to do have the same behavior as duckdb here and automatically recognize it's own hive partitions, even when we are not opening at the root of the hive partition.

_Why is this important_? The above is a minimal reprex and so obviously not representative of real world performance. We often have very large databases that we are opening on remote filesystems, for which opening at the partition root can take _a very long time_ -- from half a minute to half an hour -- often in cases where users can anticipate that they need only a particular subset (e.g. particular site_id or year, say), and can realize 1000x speedups by providing the full path (or vector of paths). This is great, but means that useful columns need to be added back on manually.

Yes, I know that arrow supports the pattern

```
open_dataset(path) |> mutate(file = add_filename())
```

and we can then use regex and more mutates to extract the hive partition labels and fill them back in, but that turns a really nice syntax into a really cumbersome one (it is also difficult to discover `add_filename()` for this, because most users appear to look in `open_dataset()` documentation for options to preserve hive partitions, and it makes no mention of this special function `add_filename()`.). Please consider auto-extracting the hive partitions from the path and preserving them in the open dataset calls?

(I've filed this as an R issue but maybe also applies to other interfaces).

### Component(s)

R

Start by reproducing the issue with the `write_dataset()` and `open_dataset()` examples, including a path vector and a subdirectory such as `gear=3`. Read the `open_dataset()` behavior and the `add_filename()` workaround first. Done means hive partition labels remain available as dataset columns when opening paths below the partition root, without manual extraction.

Written by the indexing model from the issue text.