[ENH/QST]: Behaviour of type promotion in `__setitem__`

# Summary

CUDF is not consistent with Pandas (under a bunch of circumstances) in
its behaviour when upcasting during `__setitem__`. In some cases, we
might want to mimic pandas behaviour (though they are very keen to use
value-based type promotion). In others, where we have more structured
dtypes than pandas, we need to decide what to do (current behaviour is
internally inconsistent and buggy in a bunch of cases).

I summarise what I think the current state is (by way of experiment),
and then discuss some options. Opinions welcome!

cc: @vyasr, @mroeschke, @shwina
# Pandas behaviour

Pandas version 1.5.1, MacOS (Apple Silicon)

Edit: updated code for generating more tables.

I should note that these tables are for single index `__setitem__` (`s.iloc[i] = value`). I should check if the same behaviour also occurs for:
- [x] slice-based `__setitem__` with single value `s.iloc[:1] = [value]`
- [x] slice-based `__setitem__` with list of values `s.iloc[:2] = [value for _ in range(2)]`
- [x] mask-based `__setitem__` with singleton value `s.iloc[[True, False]] = [value]`
- [x] mask-based `__setitem__` with multiple values `s.iloc[[True, False, True]] = [value, value]`
- [x] index-based `__setitem__` with single value `s.iloc[[1]] = value`
- [x] index-based `__setitem__` with multiple values `s.iloc[[1, 2]] = [value, value]`

Code to generate tables

```python
from __future__ import annotations

import os
from enum import Enum, IntEnum, auto
from itertools import filterfalse, repeat
from operator import not_
from pathlib import Path

import numpy as np
import pandas as pd
import typer

try:
import cudf
import cupy

class Backend(str, Enum):
PANDAS = "pandas"
CUDF = "cudf"

except ImportError:

class Backend(str, Enum):
PANDAS = "pandas"

def numeric_series(values, dtype, *, pandas):
if pandas:
return pd.Series(values, dtype=dtype)
else:
return cudf.Series(values, dtype=dtype)

def format_val(v):
try:
dt = v.dtype
return f"np.{dt.type.__name__}({v})"
except AttributeError:
return f"{v}"

class IndexType(IntEnum):
SINGLE_INT = auto()
SINGLETON_SLICE = auto()
CONTIG_SLICE = auto()
STRIDED_SLICE = auto()
SINGLETON_MASK = auto()
GENERAL_MASK = auto()
SINGLETON_SCATTER = auto()
GENERAL_SCATTER = auto()

def indexing(index_type: IndexType, n: int) -> tuple[int | slice | list, slice | list]:
assert n >= 3
if index_type == IndexType.SINGLE_INT:
return n - 1, slice(0, n - 1, None)
elif index_type == IndexType.SINGLETON_SLICE:
return slice(1, 2, 1), [0, *range(2, n)]
elif index_type == IndexType.CONTIG_SLICE:
return slice(1, n - 2, 1), [0, *range(n - 2, n)]
elif index_type == IndexType.STRIDED_SLICE:
return slice(0, n, 2), slice(1, n, 2)
elif index_type == IndexType.SINGLETON_MASK:
yes = [False, True, *repeat(False, n - 2)]
no = list(map(not_, yes))
return yes, no
elif index_type == IndexType.GENERAL_MASK:
yes = [True, False, True, *repeat(False, n - 3)]
no = list(map(not_, yes))
return yes, no
elif index_type == IndexType.SINGLETON_SCATTER:
yes = [1]
# Oh for Haskell-esque sections
no = list(filterfalse(yes.__contains__, range(n)))
return yes, no
elif index_type == IndexType.GENERAL_SCATTER:
yes = [0, 2]
no = list(filterfalse(yes.__contains__, range(n)))
return yes, no
else:
raise ValueError("Unhandled case")

def generate_table(f, initial_values, values_to_try, dtype, *, index_type, pandas):
initial_values = np.asarray(initial_values, dtype=object)
f.write("| Initial dtype | New value | Final dtype | Lossy? |\n")
f.write("|---------------|-----------|-------------|--------|\n")

yes, no = indexing(index_type, len(initial_values))
for value in values_to_try:
s = numeric_series(initial_values, dtype=dtype, pandas=pandas)
otype = f"np.{type(s.dtype).__name__}"
try:
if index_type == IndexType.SINGLETON_SLICE:
value = cupy.asarray([value])
s.iloc[yes] = value
except BaseException as e:
f.write(f"| `{otype}` | `{format_val(value)}` | N/A | {e} |\n")
continue
ntype = f"np.{type(s.dtype).__name__}"
expect = (np.asarray if pandas else cupy.asarray)(
initial_values[no], dtype=dtype
)
original_lost_info = (s.iloc[no].astype(dtype) != expect).any()
try:
new_vals = s.iloc[yes].astype(value.dtype)
except AttributeError:
if pandas:
new_vals = np.asarray(s.iloc[yes])
else:
new_vals = cupy.asarray(s.iloc[yes])
new_lost_info = (new_vals != value).any()
lossy = "Yes" if original_lost_info or new_lost_info else "No"
f.write(f"| `{otype}` | `{format_val(value)}` | `{ntype}` | {lossy} |\n")

def generate_tables(output_directory: Path, backend: Backend, index_type: IndexType):
integer_column_values_to_try = [
10,
np.int64(10),
2**40,
np.int64(2**40),
2**80,
10.5,
np.float64(10),
np.float64(10.5),
np.float32(10),
np.float32(10.5),
]
float_column_values_to_try = [
10,
np.int64(10),
2**40,
np.int64(2**40),
np.int32(2**31 - 100),
np.int64(2**63 - 100),
2**80 - 100,
10.5,
np.float64(10),
np.float64(10.5),
np.float64(np.finfo(np.float32).max.astype(np.float64) * 10),
np.float32(10),
np.float32(10.5),
]

pandas = backend == Backend.PANDAS
filename = f"{backend}-setitem-{index_type.name}.md"
with open(output_directory / filename, "w") as f:
if pandas:
f.write(f"Pandas {pd.__version__} behaviour for {index_type!r}\n\n")
else:
f.write(f"CUDF {cudf.__version__} behaviour for {index_type!r}\n\n")

generate_table(
f,
[2**31 - 10, 2**31 - 100, 3, 4, 5],
integer_column_values_to_try,
np.int32,
index_type=index_type,
pandas=pandas,
)
f.write("\n")
generate_table(
f,
[2**63 - 10, 2**63 - 100, 3, 4, 5],
integer_column_values_to_try,
np.int64,
index_type=index_type,
pandas=pandas,
)
f.write("\n")
generate_table(
f,
[np.finfo(np.float32).max, np.float32(np.inf), 3, 4, 5],
float_column_values_to_try,
np.float32,
index_type=index_type,
pandas=pandas,
)
f.write("\n")
generate_table(
f,
[np.finfo(np.float64).max, np.float64(np.inf), 3, 4, 5],
float_column_values_to_try,
np.float64,
index_type=index_type,
pandas=pandas,
)

def main(
output_directory: Path = typer.Argument(Path("."), help="Output directory for results"),
backend: Backend = typer.Option("pandas", help="Dataframe backend to test"),
):
os.makedirs(output_directory, exist_ok=True)
for index_type in IndexType.__members__.values():
generate_tables(output_directory, backend, index_type)

if __name__ == "__main__":
typer.run(main)
```

## Numeric columns

### Integer column dtypes

#### dtype width < max integer width

Initial values `[2**31 - 10, 2**31 - 100, 3]`. `np.int32` is
representative of any integer type that is smaller than the max width.

| Initial dtype | New value | Final dtype | Lossy? |
|-------------------|-----------------------------|----------------------|--------|
| `np.dtype[int32]` | `10` | `np.dtype[int32]` | No[^1] |
| `np.dtype[int32]` | `np.int64(10)` | `np.dtype[int32]` | No[^1] |
| `np.dtype[int32]` | `1099511627776` | `np.dtype[longlong]` | No[^2] |
| `np.dtype[int32]` | `np.int64(1099511627776)` | `np.dtype[longlong]` | No[^2] |
| `np.dtype[int32]` | `1208925819614629174706176` | `np.dtype[object_]` | No[^3] |
| `np.dtype[int32]` | `10.5` | `np.dtype[float64]` | No[^4] |
| `np.dtype[int32]` | `np.float64(10.0)` | `np.dtype[int32]` | No[^1] |
| `np.dtype[int32]` | `np.float64(10.5)` | `np.dtype[float64]` | No[^2] |
| `np.dtype[int32]` | `np.float32(10.0)` | `np.dtype[int32]` | No[^1] |
| `np.dtype[int32]` | `np.float32(10.5)` | `np.dtype[float64]` | No[^5] |

[^1]: value is exact in the initial dtype
[^2]: next largest numpy type that contains the value
[^3]: not representable in a numpy type, so coercion to object column
[^4]: default float type is float64
[^5]: `np.int32` is losslessly convertible to `np.float64`

#### dtype width == max integer width

Initial values `[2 ** 63 - 10, 2 ** 63 - 100, 3]`. These provoke edge
cases in upcasting because:
```python
import numpy as np
np.find_common_type([], [np.int64, np.float64])
# => np.float64 Noooooo! Hates it
# Yes, I know this is the same as the integer to float promotion in
# C/C++, I'm allowed to hate that too.
```

| Initial dtype | New value | Final dtype | Lossy? |
|-------------------|-----------------------------|---------------------|---------|
| `np.dtype[int64]` | `10` | `np.dtype[int64]` | No[^1] |
| `np.dtype[int64]` | `np.int64(10)` | `np.dtype[int64]` | No[^1] |
| `np.dtype[int64]` | `1099511627776` | `np.dtype[int64]` | No[^1] |
| `np.dtype[int64]` | `np.int64(1099511627776)` | `np.dtype[int64]` | No[^1] |
| `np.dtype[int64]` | `1208925819614629174706176` | `np.dtype[object_]` | No[^3] |
| `np.dtype[int64]` | `10.5` | `np.dtype[float64]` | Yes[^6] |
| `np.dtype[int64]` | `np.float64(10.0)` | `np.dtype[int64]` | No[^1] |
| `np.dtype[int64]` | `np.float64(10.5)` | `np.dtype[float64]` | Yes[^6] |
| `np.dtype[int64]` | `np.float32(10.0)` | `np.dtype[int64]` | No[^1] |
| `np.dtype[int64]` | `np.float32(10.5)` | `np.dtype[float64]` | Yes[^6] |

[^6]: `np.int64` is _not_ losslessly convertible `np.float64`

### Float column dtypes

#### dtype width < max float width

Initial values `[np.finfo(np.float32).max, np.float32(np.inf), 3]`

| Initial dtype | New value | Final dtype | Lossy? |
|---------------------|--------------------------------------|---------------------|----------|
| `np.dtype[float32]` | `10` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.int64(10)` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `1099511627776` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.int64(1099511627776)` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.int32(2147483548)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float32]` | `np.int64(9223372036854775708)` | `np.dtype[float32]` | Yes[^7] |
| `np.dtype[float32]` | `1208925819614629174706076` | `np.dtype[object_]` | No[^3] |
| `np.dtype[float32]` | `10.5` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.float64(10.0)` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.float64(10.5)` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.float64(3.4028234663852886e+39)` | `np.dtype[float64]` | No[^2] |
| `np.dtype[float32]` | `np.float32(10.0)` | `np.dtype[float32]` | No[^1] |
| `np.dtype[float32]` | `np.float32(10.5)` | `np.dtype[float32]` | No[^1] |

[^7]: value is not losslessly representable, but also, expecting
`np.float64`!

#### dtype width == max float width

Initial values `[np.finfo(np.float64).max, np.float64(np.inf), 3]`

| Initial dtype | New value | Final dtype | Lossy? |
|---------------------|--------------------------------------|---------------------|----------|
| `np.dtype[float64]` | `10` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.int64(10)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `1099511627776` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.int64(1099511627776)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.int32(2147483548)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.int64(9223372036854775708)` | `np.dtype[float64]` | Yes[^6] |
| `np.dtype[float64]` | `1208925819614629174706076` | `np.dtype[object_]` | No[^3] |
| `np.dtype[float64]` | `10.5` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.float64(10.0)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.float64(10.5)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.float64(3.4028234663852886e+39)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.float32(10.0)` | `np.dtype[float64]` | No[^1] |
| `np.dtype[float64]` | `np.float32(10.5)` | `np.dtype[float64]` | No[^1] |

## Everything else

Basically, you can put anything in a column and you get an object out,
but numpy types are converted to `object` first.

# CUDF behaviour

CUDF trunk, and state in #11904.

## Numeric columns

### Integer column dtypes

#### dtype width < max integer width

Initial values `[2**31 - 10, 2**31 - 100, 3]`. `np.int32` is
representative of any integer type that is smaller than the max width.

| Initial dtype | New value | Final dtype (trunk) | Final dtype (#11904) | Lossy? (trunk) | Lossy? (#11904) |
|-------------------|-----------------------------|-----------------------|-------------------------|----------------|-----------------|
| `np.dtype[int32]` | `10` | `np.dtype[int32]`[^8] | `np.dtype[int64]`[^9] | No | No |
| `np.dtype[int32]` | `np.int64(10)` | `np.dtype[int32]`[^8] | `np.dtype[int64]`[^9] | No | No |
| `np.dtype[int32]` | `1099511627776` | `np.dtype[int32]`[^8] | `np.dtype[int64]`[^9] | Yes | No |
| `np.dtype[int32]` | `np.int64(1099511627776)` | `np.dtype[int32]`[^8] | `np.dtype[int64]`[^9] | Yes | No |
| `np.dtype[int32]` | `1208925819614629174706176` | OverflowError | OverflowError | N/A | N/A |
| `np.dtype[int32]` | `10.5` | `np.dtype[int32]`[^8] | `np.dtype[float64]`[^9] | Yes | No |
| `np.dtype[int32]` | `np.float64(10.0)` | `np.dtype[int32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[int32]` | `np.float64(10.5)` | `np.dtype[int32]`[^8] | `np.dtype[float64]`[^9] | Yes | No |
| `np.dtype[int32]` | `np.float32(10.0)` | `np.dtype[int32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[int32]` | `np.float32(10.5)` | `np.dtype[int32]`[^8] | `np.dtype[float64]`[^9] | Yes | No |

[^8]: Bug fixed by #11904
[^9]: CUDF doesn't inspect values, so type-based promotion (difference
from pandas)

#### dtype width == max integer width

Initial values `[2 ** 63 - 10, 2 ** 63 - 100, 3]`.

| Initial dtype | New value | Final dtype (trunk) | Final dtype (#11904) | Lossy? (trunk) | Lossy? (#11904) |
|-------------------|-----------------------------|-----------------------|-------------------------|----------------|-----------------|
| `np.dtype[int64]` | `10` | `np.dtype[int64]`[^8] | `np.dtype[int64]`[^9] | No | No |
| `np.dtype[int64]` | `np.int64(10)` | `np.dtype[int64]`[^8] | `np.dtype[int64]`[^9] | No | No |
| `np.dtype[int64]` | `1099511627776` | `np.dtype[int64]`[^8] | `np.dtype[int64]`[^9] | No | No |
| `np.dtype[int64]` | `np.int64(1099511627776)` | `np.dtype[int64]`[^8] | `np.dtype[int64]`[^9] | No | No |
| `np.dtype[int64]` | `1208925819614629174706176` | OverflowError | OverflowError | N/A | N/A |
| `np.dtype[int64]` | `10.5` | `np.dtype[int64]`[^8] | `np.dtype[float64]`[^9] | Yes | Yes[^6] |
| `np.dtype[int64]` | `np.float64(10.0)` | `np.dtype[int64]`[^8] | `np.dtype[float64]`[^9] | No | Yes[^6] |
| `np.dtype[int64]` | `np.float64(10.5)` | `np.dtype[int64]`[^8] | `np.dtype[float64]`[^9] | Yes | Yes[^6] |
| `np.dtype[int64]` | `np.float32(10.0)` | `np.dtype[int64]`[^8] | `np.dtype[float64]`[^9] | No | Yes[^6] |
| `np.dtype[int64]` | `np.float32(10.5)` | `np.dtype[int64]`[^8] | `np.dtype[float64]`[^9] | Yes | Yes[^6] |

### Float column dtypes

#### dtype width < max float width

Initial values `[np.finfo(np.float32).max, np.float32(np.inf), 3]`

| Initial dtype | New value | Final dtype (trunk) | Final dtype (#11904) | Lossy? (trunk) | Lossy? (#11904) |
|---------------------|--------------------------------------|-------------------------|-------------------------|----------------|-----------------|
| `np.dtype[float32]` | `10` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `np.int64(10)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `1099511627776` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `np.int64(1099511627776)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `np.int32(2147483548)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | Yes[^10] | No |
| `np.dtype[float32]` | `np.int64(9223372036854775708)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | Yes[^10] | Yes[^6] |
| `np.dtype[float32]` | `1208925819614629174706076` | OverflowError | OverflowError | N/A | N/A |
| `np.dtype[float32]` | `10.5` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `np.float64(10.0)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `np.float64(10.5)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | No | No |
| `np.dtype[float32]` | `np.float64(3.4028234663852886e+39)` | `np.dtype[float32]`[^8] | `np.dtype[float64]`[^9] | Yes[^8] | No |
| `np.dtype[float32]` | `np.float32(10.0)` | `np.dtype[float32]`[^8] | `np.dtype[float32]`[^9] | No | No |
| `np.dtype[float32]` | `np.float32(10.5)` | `np.dtype[float32]`[^8] | `np.dtype[float32]`[^9] | No | No |

[^10]: As for [^6], but promotion from `np.int32` to `np.float32` is
also not lossless.

#### dtype width == max float width

Initial values `[np.finfo(np.float64).max, np.float64(np.inf), 3]`

| Initial dtype | New value | Final dtype (trunk) | Final dtype (#11904) | Lossy? (trunk) | Lossy? (#11904) |
|---------------------|--------------------------------------|---------------------|----------------------|----------------|-----------------|
| `np.dtype[float64]` | `10` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.int64(10)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `1099511627776` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.int64(1099511627776)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.int32(2147483548)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.int64(9223372036854775708)` | `np.dtype[float64]` | `np.dtype[float64]` | Yes[^6] | Yes[^6] |
| `np.dtype[float64]` | `1208925819614629174706076` | OverflowError | OverflowError | N/A | N/A |
| `np.dtype[float64]` | `10.5` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.float64(10.0)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.float64(10.5)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.float64(3.4028234663852886e+39)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.float32(10.0)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |
| `np.dtype[float64]` | `np.float32(10.5)` | `np.dtype[float64]` | `np.dtype[float64]` | No | No |

## Everything else

This is where it starts to get _really_ messy. This section is a work
in progress. We should decide what we _want_ the semantics to be,
because in most cases pandas doesn't have the same dtypes that CUDF does.

### Inserting strings into numerical columns

This "works", for some value of "works" on #11904 if the string value
is parseable as the target dtype.

So

```python
s = cudf.Series([1, 2, 3], dtype=int)
s.iloc[2] = "4" # works
s.iloc[2] = "0xf" # => ValueError: invalid literal for int() with base 10: '0xf'
```

And similarly for float strings and float dtypes.

This is probably a nice feature.

### Inserting things into string columns

Works if the the "thing" is convertible to a string (so numbers work),
but Scalars with list or struct dtypes don't work.

I would argue that explicit casting from the user here is probably
better.

### List columns

The new value must have an identical dtype to that of the target column.

### Struct columns

The new value must have leaf dtypes that are considered compatible in
some sense, but then the leaves are downcast to the leaf dtypes of the
target column. So this is lossy and likely a bug:

```python
sr = cudf.Series([{"a": 1, "b": 2}])
sr.iloc[0] = {"a": 10.5, "b": 2}
sr[0] # => {"a": 10, "b": 2} (lost data in "a")
```
## What I think we want (for composite columns)

For composite columns, if the dtype shapes match, I think the casting
rule should be to traverse to the leaf dtypes and promote using the
rules for non-composite columns. If shapes don't match, `__setitem__`
should not be allowed.

This, to me, exhibits principle of least surprise.