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

Viewed 57

版本: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

2 Answers

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