BUG: find_common_type incorrect for Categorical with NaNs

[jbrockmendel]

import pandas as pd
from pandas.core.dtypes.cast import find_common_type

left = pd.Index([1, 2, 3, 4])
right = pd.CategoricalIndex([1, None], categories=left)

dtype = find_common_type([left.dtype, right.dtype])

>>> right.astype(dtype)
ValueError: Cannot convert float NaN to integer

xref #37930; I expect this would also be relevant for #19371

[jorisvandenbossche]

This is not a "bug", but rather unfortunate but intentional behaviour. We handle this specifically here:

pandas/pandas/core/dtypes/concat.py

Lines 60 to 75 in 22dbef1

	def _cast_to_common_type(arr: ArrayLike, dtype: DtypeObj) -> ArrayLike:
	"""
	Helper function for `arr.astype(common_dtype)` but handling all special
	cases.
	"""
	if (
	is_categorical_dtype(arr.dtype)
	and isinstance(dtype, np.dtype)
	and np.issubdtype(dtype, np.integer)
	):
	# problem case: categorical of int -> gives int as result dtype,
	# but categorical can contain NAs -> fall back to object dtype
	try:
	return arr.astype(dtype, copy=False)
	except ValueError:
	return arr.astype(object, copy=False)

See this comment on the original PR about exactly this case: #33607 (comment)

This whole _cast_to_common_type function is meant to deal with the current inconsistencies (it also handles a few other cases as well).
Of course, if you use find_common_type outside of the concat.py / _cast_to_common_type context (eg like you did in #38122), then it can give problems. We might need to use this _cast_to_common_type then in those places as well, instead of a plain astype.

---

We should also see if we can actually change this behaviour, to get rid of the value-dependent behaviour (which I think we generally want). But that doesn't seem easy to deprecate, so might be 2.0 material.

[jbrockmendel]

Marking as blocker for 1.4rc; we should make a decision about deprecation before then.

[jreback]

I agree i am not sure how you would deprecate this.