### Summary

`ansible-galaxy collection install -r requirements.yml` fails with "No such file or directory" , while using `ansible-galaxy collection install <filename>` works.

### Issue Type

Bug Report

### Component Name

ansible-galaxy

### Ansible Version

```console
$ ansible-galaxy --version
ansible-galaxy [core 2.14.0]
config file = /<censored>/ansible.cfg
configured module search path = ['/opt/app-root/src/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
ansible python module location = /opt/app-root/lib64/python3.9/site-packages/ansible
ansible collection location = /opt/app-root/src/.ansible/collections:/usr/share/ansible/collections
executable location = /opt/app-root/bin/ansible-galaxy
python version = 3.9.13 (main, Nov  9 2022, 13:16:24) [GCC 8.5.0 20210514 (Red Hat 8.5.0-15)] (/opt/app-root/bin/python3.9)
jinja version = 3.1.2
libyaml = True
```


### Configuration

```console
# if using a version older than ansible-core 2.12 you should omit the '-t all'
$ ansible-config dump --only-changed -t all
CONFIG_FILE() = /<censored>/ansible.cfg
DEFAULT_REMOTE_USER(/<censored>/ansible.cfg) = <censored>
GALAXY_SERVER_LIST(/<censored>/ansible.cfg) = ['automation_hub']
INVENTORY_IGNORE_EXTS(/<censored>/ansible.cfg) = ['~', '.orig', '.bak', '.ini', '.cfg', '.retry', '.pyc', '.pyo', '.md', 'files.d']

CONNECTION:
==========

paramiko_ssh:
____________
remote_user(/<censored>/ansible.cfg) = <censored>
ssh_args(/<censored>/ansible.cfg) = -o ForwardAgent=yes -o ControlMaster=auto -o ControlPersist=60s

ssh:
___
remote_user(/<censored>/ansible.cfg) = <censored>
ssh_args(/<censored>/ansible.cfg) = -o ForwardAgent=yes -o ControlMaster=auto -o ControlPersist=60s
```


### OS / Environment

RHEL 8
(Run inside a container.)

### Steps to Reproduce

Using a simple example collection, that has no dependencies.
The censored path is in our internal LAN and is reachable.

requirements.yml
```yaml (paste below)
collections:
  - name: https://<censored>/ansible.posix-1.4.0.tar
    type: file
```

```bash
$ ansible-galaxy collection install -r requirements.yml
 ERROR! Unexpected Exception, this is probably a bug: [Errno 2] No such file or directory: b'https://<censored>/ansible.posix-1.4.0.tar
```

But with the same file:
```bash
$ ansible-galaxy collection install https://<censored>/ansible.posix-1.4.0.tar
Downloading https://<censored>/ansible.posix-1.4.0.tar to /opt/app-root/src/.ansible/tmp/ansible-local-11wq05v0n3/tmpmz8aw1bb/ansible.posix-1.-2whncpha
Starting galaxy collection install process
Process install dependency map
Starting collection install process
Installing 'ansible.posix:1.4.0' to '/opt/app-root/src/.ansible/collections/ansible_collections/ansible/posix'
ansible.posix:1.4.0 was installed successfully
```


### Expected Results

I expected `ansible-galaxy collection install -r requirements.yml` to work.

### Actual Results

```console
$ ansible-galaxy collection install -r requirements.yml
 ERROR! Unexpected Exception, this is probably a bug: [Errno 2] No such file or directory: b'https://<censored>/ansible.posix-1.4.0.tar


But with the same file:
```bash
$ ansible-galaxy collection install https://<censored>/ansible.posix-1.4.0.tar
Downloading https://<censored>/ansible.posix-1.4.0.tar to /opt/app-root/src/.ansible/tmp/ansible-local-11wq05v0n3/tmpmz8aw1bb/ansible.posix-1.-2whncpha
Starting galaxy collection install process
Process install dependency map
Starting collection install process
Installing 'ansible.posix:1.4.0' to '/opt/app-root/src/.ansible/collections/ansible_collections/ansible/posix'
ansible.posix:1.4.0 was installed successfully
```

### Code of Conduct

- [X] I agree to follow the Ansible Code of Conduct
dict_keys = set()  # t
    sentinel_keys = set()  # t
    for info in galaxy_yml_schema:
        if info.get('required', False):
            mandatory_keys.add(info['key'])
        key_list_type = {
            'str': string_keys,
            'list': list_keys,
            'dict': dict_keys,
            'sentinel': sentinel_keys,
        }[info.get('type', 'str')]
        key_list_type.add(info['key'])
    all_keys = frozenset(mandatory_keys | string_keys | list_keys | dict_keys | sentinel_keys)
    set_keys = set(galaxy_yml.keys())
    missing_keys = mandatory_keys.difference(set_keys)
    if missing_keys:
        msg = (
            "The collection galaxy.yml at '%s' is missing the following mandatory keys: %s"
            % (to_native(b_galaxy_yml_path), ", ".join(sorted(missing_keys)))
        )
        if require_build_metadata:
            raise AnsibleError(msg)
        display.warning(msg)
        raise ValueError(msg)
    extra_keys = set_keys.difference(all_keys)
    if len(extra_keys) > 0:
        display.warning("Found unknown keys in collection galaxy.yml at '%s': %s"
                        % (to_text(b_galaxy_yml_path), ", ".join(extra_keys)))
    # A
    for optional_string in string_keys: