### 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
collection_path, collection_tar = collection_artifact
    shutil.rmtree(collection_path)
    collections_dir = ('%s' % os.path.sep).join(to_text(collection_path).split('%s' % os.path.sep)[:-2])
    temp_path = os.path.join(os.path.split(collection_tar)[0], b'temp')
    os.makedirs(temp_path)
    mock_display = MagicMock()
    monkeypatch.setattr(Display, 'display', mock_display)
    concrete_artifact_cm = collection.concrete_artifact_manager.ConcreteArtifactsManager(temp_path, validate_certs=False)
    mock_download = MagicMock()
    mock_download.return_value = collection_tar
    monkeypatch.setattr(concrete_artifact_cm, 'get_galaxy_artifact_path', mock_download)
    req = Candidate('ansible_namespace.collection', '0.1.0', 'https://downloadme.com', 'galaxy', None)
    collection.install(req, to_text(collections_dir), concrete_artifact_cm)
    actual_files = os.listdir(collection_path)
    actual_files.sort()
    assert actual_files == [b'FILES.json', b'MANIFEST.json', b'README.md', b'docs', b'playbooks', b'plugins', b'roles',
                            b'runme.sh']
    assert mock_display.call_count == 2
    assert mock_display.mock_calls[0][1][0] == "Installing 'ansible_namespace.collection:0.1.0' to '%s'" \
        % to_text(collection_path)
    assert mock_display.mock_calls[1][1][0] == "ansible_namespace.collection:0.1.0 was installed successfully"
    assert mock_download.call_count == 1
    assert mock_download.mock_calls[0][1][0].src == 'https://downloadme.com'
    assert mock_download.mock_calls[0][1][0].type == 'galaxy'
def test_install_collections_from_tar(collection_artifact, monkeypatch):