Conversion error with Python `float` value conversion to `UNION` containing float

[Hunknown]

What happens?

When registering a Python UDF that returns a union of float/varchar/int, calling the function in duckdb will raise a Conversion Error if the result contains a float value.

See duckdb/duckdb#19309 for full discussion.

To Reproduce

Snippet to reproduce the issue:

import duckdb
import numpy as np
from duckdb.typing import (
	DuckDBPyType
)

arr = [{"a": 1, "b": 1.2}, {"a": 3, "b": 2.4}]

def test():
    return arr

return_type = DuckDBPyType(list[dict[str, Union[str, int, np.float64, np.float32, float]]])

duckdb.create_function("test", test, return_type=return_type)

duckdb.sql("select test()")

ConversionException: Conversion Error: Could not convert 'float' to type UNION(u1 VARCHAR, u2 BIGINT, u3 DOUBLE, u4 FLOAT, u5 DOUBLE)

As stated in previous section, there's no issue if the result doesn't contains any float value:

arr = [{"a": 1, "b": "abc"}, {"a": 3, "b": "def"}]

duckdb.sql("select test()")

┌──────────────────────────────────────────────────────────────────────────────┐
│                                    test()                                    │
│ map(varchar, union(u1 varchar, u2 bigint, u3 double, u4 float, u5 double))[] │
├──────────────────────────────────────────────────────────────────────────────┤
│ [{a=1, b=abc}, {a=3, b=def}]                                                 │
└──────────────────────────────────────────────────────────────────────────────┘

OS:

Windows 11

DuckDB Package Version:

1.4.0

Python Version:

3.12.9

Full Name:

Maxime Nguyen

Affiliation:

Credit Mutuel AM

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[paultiq]

The cause seems to be that PythonValueConversion doesn't have the DefaultCastAs branch that the other datatypes have:
Consider HandleDouble:

duckdb-python/src/duckdb_py/native/python_conversion.cpp

Lines 478 to 479 in 3e79791

	default:
	throw ConversionException("Could not convert 'float' to type %s", target_type.ToString());

vs HandleBigint:

duckdb-python/src/duckdb_py/native/python_conversion.cpp

Lines 503 to 505 in 3e79791

	default:
	res = Value::BIGINT(value).DefaultCastAs(target_type);
	break;

Possible fix: this seems to fix it, provided the original return types are disambiguated with return_type=DuckDBPyType("MAP(VARCHAR, UNION(int_val BIGINT, float_val DOUBLE, str_val VARCHAR))[]"):

  	default:
     	    result = Value::DOUBLE(val).DefaultCastAs(target_type);
            break;

Alternate Solution

If the types are constant for each mapping, where a's are ints and b's are floats, could use a struct instead:

import duckdb
import numpy as np
from duckdb.typing import (
	DuckDBPyType
)
import duckdb.typing as dt

arr = [{"a": 1, "b": 1.2}, {"a": 3, "b": 2.4}]

def test():
    return arr

with duckdb.connect() as conn:
    conn.create_function("test", test, return_type="STRUCT(a BIGINT, b DOUBLE)[]")

    print(conn.sql("select test()").df())
    print(conn.sql("describe select test()").df())

[Hunknown]

Hi @paultiq

Yes, I've just tested both solutions and I can confirm that the alternate solution does work.

But the first one still raise an error, so as @Tishj mentioned by changing the title of the issue, it's only when the float variable is in an Union that it doesn't work.

[paultiq]

The first one requires a change/build of the source code to change the default case. If you only tried with the updated return type without rebuilding from source, it wouldn't work.