Can't put `map_extract(MAP {'a': 1}, 'a') = 1` as filter

[lewiszlw]

Describe the bug

I run below sql on lastet main datafusion-cli and execution failed.

> select 1 where map_extract(MAP {'a': 1}, 'a') = 1;
type_coercion
caused by
Error during planning: Cannot infer common argument type for comparison operation List(nullable Int64) = Int64

To Reproduce

No response

Expected behavior

No response

Additional context

No response

[lewiszlw]

Why map_extract function return type is List?

[Jefffrey]

That seems to be how it is defined: https://datafusion.apache.org/user-guide/sql/scalar_functions.html#map-extract

Returns a list containing the value for the given key or an empty list if the key is not present in the map.

Seems consistent with DuckDB version: https://duckdb.org/docs/stable/sql/functions/map#element_atmap-key

Return the value for a given key as a list, or an empty list if the key is not contained in the map. The type of the key provided in the second parameter must match the type of the map's keys; else, an error is thrown.

[lewiszlw]

Thanks for explanation.
What about adding a map_get function similar like map_extract but not return list type, instead the value type?

[Jefffrey]

You can index a map type directly:

> select MAP {'a': 1, 'b': 2}['a'] = 1;
+----------------------------------------------------------------------------------+
| map(make_array(Utf8("a"),Utf8("b")),make_array(Int64(1),Int64(2)))[a] = Int64(1) |
+----------------------------------------------------------------------------------+
| true                                                                             |
+----------------------------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.005 seconds.

[lewiszlw]

Thank you. That's exactly what I need.