Modifying Arrays in sub-queries

[Ingmar Poese]

Hi Folks,

maybe someone can help me here. I have a two tables that i need to join based on a few criterias. However, the joining of the two columns is giving me a hard time.

Here is a sample of what i *want* to do:

Assume this table for now:

select [1,2,3] as test, 4 as test2;

SELECT
    [1, 2, 3] AS test,
    4 AS test2

┌─test────┬─test2─┐
│ [1,2,3] │     4 │
└─────────┴───────┘

Now, all i want to do is add the 4 in test2 (this is a normal Int32) to the array in test (which is an array of the same type -> Int32). The only way i could come up with to do it so far was to do a very cumbersome string conversion like so:

select arrayMap(x -> toInt32(x), splitByChar(',', concat(arrayStringConcat(arrayMap(x->toString(x), test), ','), ',', toString(test2)))) as result from (select [1,2,3] as test, 4 as test2);

SELECT arrayMap(lambda(tuple(x), toInt32(x)), splitByChar(',', concat(arrayStringConcat(arrayMap(lambda(tuple(x), toString(x)), test), ','), ',', toString(test2)))) AS result
FROM
(
    SELECT
        [1, 2, 3] AS test,
        4 AS test2
)

┌─result────┐
│ [1,2,3,4] │
└───────────┘

Which is the result i want. However, when i run this on my real dataset, I keep getting errors - to be specific, this one:

Code: 32. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Attempt to read after eof: Cannot parse UInt32 from String, because value is too short.

which i have traced to toInt32 in the outer arrayMap. The only reason i can see for this to happen is if (for some reason) the Array is empty (the int cannot be empty, it would always default to 0). Even when adding a check that the array cannot be empty, this still fails with the same error.

So, if someone could point me in the right direction for solving/working around this, i would really appreciate it.

Best Regards

[man...@gmail.com]

Hi.

1. To try to parse an integer or return zero in case of parse error, you may use toInt32OrZero function.
2. This way to concat two arrays is very ugly. We just created a task to implement arrayConcat, arrayPush, arrayPop functions in our task tracker.

[Ingmar Poese]

Yep, with that functions it works like a charm. However, the 0 acctually never occurs anywhere in the entire output. No idea why it has to be able to default when it never uses the default.

And thanks for adding the functions to the tracker.  Please let me know when they become available.

Thanks