Implement `array_resize` function

[izveigor]

Is your feature request related to a problem or challenge?

Summary

Characteristic	Description
Function name:	array_resize
Aliases:	list_resize
Original function?:	No
Function Description:	ClickHouse: Changes the length of the array. DuckDB: Resizes the list to contain size elements. Initializes new elements with value or NULL if value is not set.
Sources:	Concept DuckDB ClickHouse

Examples:

SELECT arrayResize([1], 3)

Query id: 2c975fce-0843-485f-9aef-1a8899728cdc

┌─arrayResize([1], 3)─┐
│ [1,0,0]             │
└─────────────────────┘

SELECT arrayResize([[1, 2]], 2)

Query id: 2765406e-c2dd-4208-bf5b-7fe42cd8b682

┌─arrayResize([[1, 2]], 2)─┐
│ [[1,2],[]]               │
└──────────────────────────┘

SELECT arrayResize([1, 2], 4, 5)

Query id: b75e55d1-1abb-4933-b2b1-f9d472308d91

┌─arrayResize([1, 2], 4, 5)─┐
│ [1,2,5,5]                 │
└───────────────────────────┘

SELECT arrayResize([[1, 2], [3, 4]], 4, [1, 1])

Query id: ebaeb0e6-b9e1-4a1f-986f-ff546eb37f9d

┌─arrayResize([[1, 2], [3, 4]], 4, [1, 1])─┐
│ [[1,2],[3,4],[1,1],[1,1]]                │
└──────────────────────────────────────────┘

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[Rachelint]

Hi, I'm interested in this issue, can I have a try?

[viirya]

For the issues no assignment and no others claiming to do, please feel free to pick it. Thanks.

[Rachelint]

For the issues no assignment and no others claiming to do, please feel free to pick it. Thanks.

Thanks for advice 🚀

[Rachelint]

@izveigor Hi, sorry for reporting no any progress for so long due to work reasons.
For the value is not set case like:

SELECT array_resize([[1, 2]], 2)

I want to fill NULL like duckdb rather than clickhouse, is it ok to do that?
In duckdb, the output will be like:

SELECT array_resize([[1, 2]], 2)
┌─────────────────────────────────────────────────────────┐
│ array_resize(main.list_value(main.list_value(1, 2)), 2) │
│                        int32[][]                        │
├─────────────────────────────────────────────────────────┤
│ [[1, 2], NULL]                                          │
└─────────────────────────────────────────────────────────┘

[izveigor]

Hello, @Rachelint!
As I understand, you worry about two questions: "Can NULL be an element in array?" and "May I implement DuckDB implementations rather ClickHouse?".
I can not give you guarantees about null handling, because that problem has long history and lots of contradictions. If you interest about it, you can read the discussion in #7142 (comment). Also, I would like to notice @jayzhan211 and his PR: #7338.
The answer of the second question is predictably simple: you can use any methods if they have pragmatic value.

I think I can answer all your questions. If you don't comprehend some situations, then you can ask me again :)

[Rachelint]

Hello, @Rachelint! As I understand, you worry about two questions: "Can NULL be an element in array?" and "May I implement DuckDB implementations rather ClickHouse?". I can not give you guarantees about null handling, because that problem has long history and lots of contradictions. If you interest about it, you can read the discussion in #7142 (comment). Also, I would like to notice @jayzhan211 and his PR: #7338. The answer of the second question is predictably simple: you can use any methods if they have pragmatic value.

I think I can answer all your questions. If you don't comprehend some situations, then you can ask me again :)

Thanks for the reply, it's very helpful to me！I am reading #7142 and #7338 , and I feel it can solve my confusion. I'm really confused about how datafusion intends to handle NULL, and thing got worse after I see the different behaviors in duckdb and clickhouse... I think above discussion can help me a lot !

About May I implement DuckDB implementations rather ClickHouse, it is due some behaviors in clickhouse confused me actually... And behaviors in duckdb seem more understandable.
For example, this query is no problem in clickhouse:

SELECT arrayResize([1, 2, 3], 4, NULL)

[1,2,3,NULL]

and this query occurs error:

SELECT arrayResize([[1], [2], [3]], 4, NULL)

Code: 386. DB::Exception: There is no supertype for types Array(UInt8), Nullable(Nothing) because some of them are Array and some of them are not: While processing arrayResize([[1], [2], [3]], 4, NULL). (NO_COMMON_TYPE) (version 23.8.1.2891 (official build))

But it may be not so important now, because my main purpose is to know how datafusion plans to handle this, and the discussion may help.

[Weijun-H]

I will work on this.

[Rachelint]

I will work on this.

Ok, I am too busy with work lately...
Sorry for the lack of progress for so long @izveigor

[Weijun-H]

I will work on this.

Ok, I am too busy with work lately... Sorry for the lack of progress for so long @izveigor

I saw there was no progress on this issue, so I tried to pick it up. Your precious discussion is very helpful. Thank you for your contribution! @Rachelint 👍