使用array_concat无法使用if判断

💬 StarRocks 用户问答 常见 FAQ
#1

【详述】在 https://docs.starrocks.io/zh-cn/2.4/sql-reference/sql-functions/array-functions/array_concat 写到数组拼接时，null会当做正常值处理，实际使用中SELECT array_concat([1,2], NULL) 会返回null，我考虑对拼接的数组使用if判断或者
coalesce(,[null])，会报错：No matching function with signature: if(boolean, ARRAY, unknown type: NULL_TYPE).
请问应该如何使用保证数组拼接当其中一个元素是NULL时返回另一值

希望的解决方案：

  1. array_concat当其中1个参数为null时，忽视这个参数其他正常拼接
    2.支持if和coalesce，当判断为null时，支持返回其他值参与数组拼接

【背景】无
【业务影响】当拼接其中一个是NULL无法返回需要的值
【StarRocks版本】例如：2.4

#2

可以这样试试

select array_concat([1,2],cast(JSON_ARRAY(ifnull("","")) as array<varchar>));

上面会拼出来一个null的数据元素

image
image714×124 7.56 KB

#3
  1. array_concat(cast(IF(a.active_days is null,’[]’,1) as array),r.active_days)
  2. 楼上的方案：
SELECT array_concat([1,2],cast(JSON_ARRAY(NULL) as array<varchar>));
SELECT array_concat([1,2],cast(JSON_ARRAY(IFNULL(NULL,"")) as array<varchar>));

2.5.5版本 上述几种方案都会导致BE挂掉

image
image1014×588 62.1 KB

验证：

测试集群2.5.2 执行正常，升级到2.5.5测试也会挂：

image
image3818×1856 286 KB

#4

贴下be.out

#5 
start time: Wed Jun 14 08:23:09 UTC 2023
2.5.5 RELEASE (build 24c1eca)
query_id:03760b0a-0a90-11ee-8221-8267db55d04b, fragment_instance:03760b0a-0a90-11ee-8221-8267db55d04c
tracker:process consumption: 1006281872
tracker:query_pool consumption: 2560
tracker:load consumption: 0
tracker:metadata consumption: 323351662
tracker:tablet_metadata consumption: 223066604
tracker:rowset_metadata consumption: 100285058
tracker:segment_metadata consumption: 0
tracker:column_metadata consumption: 0
tracker:tablet_schema consumption: 8491748
tracker:segment_zonemap consumption: 0
tracker:short_key_index consumption: 0
tracker:column_zonemap_index consumption: 0
tracker:ordinal_index consumption: 0
tracker:bitmap_index consumption: 0
tracker:bloom_filter_index consumption: 0
tracker:compaction consumption: 0
tracker:schema_change consumption: 0
tracker:column_pool consumption: 0
tracker:page_cache consumption: 0
tracker:update consumption: 0
tracker:chunk_allocator consumption: 0
tracker:clone consumption: 0
tracker:consistency consumption: 0
*** Aborted at 1686732414 (unix time) try "date -d @1686732414" if you are using GNU date ***
PC: @          0x4e28a88 starrocks::vectorized::ArrayColumn::get()
*** SIGSEGV (@0x11) received by PID 3298337 (TID 0x7ff23e94f700) from PID 17; stack trace: ***
    @          0x58f9dc2 google::(anonymous namespace)::FailureSignalHandler()
    @     0x7ff2e7cf9ce0 (unknown)
    @          0x4e28a88 starrocks::vectorized::ArrayColumn::get()
    @          0x4d2ca08 starrocks::vectorized::ArrayConcat<>::_array_concat()
    @          0x4d2cfd3 starrocks::vectorized::ArrayFunctions::array_concat_int()
    @          0x3e32797 starrocks::vectorized::VectorizedFunctionCallExpr::evaluate()
    @          0x381a06e starrocks::ExprContext::evaluate()
    @          0x2f92002 starrocks::pipeline::ProjectOperator::push_chunk()
    @          0x2cf7836 starrocks::pipeline::PipelineDriver::process()
    @          0x4f2ef23 starrocks::pipeline::GlobalDriverExecutor::_worker_thread()
    @          0x4924bf2 starrocks::ThreadPool::dispatch_thread()
    @          0x491f6ea starrocks::Thread::supervise_thread()
    @     0x7ff2e7cef1ca start_thread
    @     0x7ff2e71ccdd3 __GI___clone
    @                0x0 (unknown)
start time: Wed Jun 14 08:46:55 UTC 2023

京ICP备2022026421号-1