confusing fit failure when using set_config(transform_output="pandas")

### Describe the bug

Using `set_config(transform_output="pandas")` in combination with `ColumnTransformer` may result in a failed fit, since indices do not align as expected. The error will not be especially helpful: "ValueError: Found input variables with inconsistent numbers of samples: [705, 527]" (Error appears to be related to merging DataFrames with non-aligned indices, as evident by NaNs and change in shape).

### Steps/Code to Reproduce

```
import pandas as pd
from sklearn.ensemble import GradientBoostingRegressor
from sklearn.pipeline import Pipeline
from sklearn.decomposition import TruncatedSVD
from sklearn import set_config
from sklearn.feature_extraction.text import CountVectorizer

set_config(transform_output="pandas")

d = pd.DataFrame({
    'txt': ['cat eats milk', 'tree says hi', 'bone eats dog'],
}, index=[8, 5, 3])
d


Pipeline([('cv', CountVectorizer()), ('tsvd', TruncatedSVD(n_components=1))]).fit_transform(d.txt)


```

### Expected Results

expected index: [8, 5, 3]

expected behavior: works with ColumnTransformer

### Actual Results

actual index: [0, 1, 2]

actual behavior: doesn't work with ColumnTransformer if other transformers maintain aligned indices

### Versions

```shell
System:
    python: 3.8.16 | packaged by conda-forge | (default, Feb  1 2023, 16:01:55)  [GCC 11.3.0]
executable: /opt/conda/envs/py38/bin/python3.8
   machine: Linux-4.19.0-23-cloud-amd64-x86_64-with-glibc2.10

Python dependencies:
      sklearn: 1.2.2
          pip: 23.0.1
   setuptools: 67.6.1
        numpy: 1.23.5
        scipy: 1.10.1
       Cython: None
       pandas: 1.5.3
   matplotlib: 3.7.1
       joblib: 1.2.0
threadpoolctl: 3.1.0

Built with OpenMP: True

threadpoolctl info:
       user_api: blas
   internal_api: openblas
         prefix: libopenblas
       filepath: /opt/conda/envs/py38/lib/python3.8/site-packages/numpy.libs/libopenblas64_p-r0-742d56dc.3.20.so
        version: 0.3.20
threading_layer: pthreads
   architecture: Haswell
    num_threads: 4

       user_api: openmp
   internal_api: openmp
         prefix: libgomp
       filepath: /opt/conda/envs/py38/lib/python3.8/site-packages/scikit_learn.libs/libgomp-a34b3233.so.1.0.0
        version: None
    num_threads: 4

       user_api: blas
   internal_api: openblas
         prefix: libopenblas
       filepath: /opt/conda/envs/py38/lib/python3.8/site-packages/scipy.libs/libopenblasp-r0-41284840.3.18.so
        version: 0.3.18
threading_layer: pthreads
   architecture: Haswell
    num_threads: 4
```

COMMENT:
Note that Scikit-learn transformers will retain the index if there is one:

```python
from sklearn.preprocessing import StandardScaler
import pandas as pd
import sklearn

sklearn.set_config(transform_output="pandas")
X = pd.DataFrame({"a": [1, 2, 3]}, index=[4, 1, 2])

ss = StandardScaler()
X_trans = ss.fit_transform(X)
print(X_trans.index)
# Index([4, 1, 2], dtype='int64')
```

There are two issues with the `ColumnTransformer` snippet in https://github.com/scikit-learn/scikit-learn/issues/26210#issuecomment-1514692919

1. It's possible for transformers in the `ColumnTransformer` to output different indexes. Internally, ColumnTransformer will call `pd.concat` which cares about the indices by default. I prefer raising an error in this case, but checking the indexes add more overhead.
2. `CountVectorizer` outputs a sparse matrix, which should have failed with `set_config(transform_output="pandas")` The `set_output` API does not support pandas output. Since the vectorizer's output was a sparse matrix, the index was not passed to the `TruncatedSVD`. I opened #26253 to raise an error for Vectorizers.

Labels: Bug, module:compose, Pandas compatibility