map字段union all多次执行出现返回不同结果

Viewed 57
2.0

版本：2.0.12

执行如下sql

SELECT
  event_date,
  os,
  map('exp_search_dialog_cnt', count(case when event_type = 'show' then user_id end)) as cnt_map
FROM
  xxx
GROUP BY
  1,
  2

无结果返回

执行如下sql

SELECT
  event_date,CASE
    WHEN jsonb_extract_string(extra, '$.load_env') = '1' THEN os
    WHEN jsonb_extract_string(extra, '$.load_env') = '2' THEN '小程序'
    ELSE 'other'
  END as os,
  map('exp_search_page_cnt', count(CASE WHEN event_type = 'page_load' THEN user_id END)) as cnt_map
FROM
  xxx
GROUP BY
  1,
  2

返回结果
image.png

将上述两个sql union all

SELECT
  event_date,
  os,
  map('exp_search_dialog_cnt', count(case when event_type = 'show' then user_id end)) as cnt_map
FROM
  xxx
GROUP BY
  1,
  2
union all
SELECT
  event_date,CASE
    WHEN jsonb_extract_string(extra, '$.load_env') = '1' THEN os
    WHEN jsonb_extract_string(extra, '$.load_env') = '2' THEN '小程序'
    ELSE 'other'
  END as os,
  map('exp_search_page_cnt', count(CASE WHEN event_type = 'page_load' THEN user_id END)) as cnt_map
FROM
  xxx
GROUP BY
  1,
  2

多次执行出现不同的结果，如下图所示：

image.png

image.png

yugunsung71
2 Answers

这种最好拿profile看看，另外如果是多副本表试下use_fix_replica分别设置0、1、2结果稳定不

zclllyybb286