2.1.7 版本同时执行coalesce和STDDEV_POP出现类型错误

Viewed 41

建表语句:

CREATE TABLE `test` (
  `dt` date NULL COMMENT '分区字段',
  `uid` bigint NOT NULL COMMENT '用户id'
) ENGINE=OLAP
DUPLICATE KEY(`dt`, `uid`)
COMMENT '用户列表'
PARTITION BY RANGE(`dt`)
(PARTITION p19700101 VALUES [('0000-01-01'), ('2024-12-01')),
PARTITION p20241201 VALUES [('2024-12-01'), ('2024-12-02')),
PARTITION p20241202 VALUES [('2024-12-02'), ('2024-12-03')),
PARTITION p20241203 VALUES [('2024-12-03'), ('2024-12-04')),
PARTITION p20241204 VALUES [('2024-12-04'), ('2024-12-05')),
PARTITION p20241205 VALUES [('2024-12-05'), ('2024-12-06')),
PARTITION p20241206 VALUES [('2024-12-06'), ('2024-12-07')),
PARTITION p20241207 VALUES [('2024-12-07'), ('2024-12-08')),
PARTITION p20241208 VALUES [('2024-12-08'), ('2024-12-09')),
PARTITION p20241209 VALUES [('2024-12-09'), ('2024-12-10')),
PARTITION p20241210 VALUES [('2024-12-10'), ('2024-12-11')),
PARTITION p20241211 VALUES [('2024-12-11'), ('2024-12-12')),
PARTITION p20241212 VALUES [('2024-12-12'), ('2024-12-13')),
PARTITION p20241213 VALUES [('2024-12-13'), ('2024-12-14')),
PARTITION p20241214 VALUES [('2024-12-14'), ('2024-12-15')),
PARTITION p20241215 VALUES [('2024-12-15'), ('2024-12-16')),
PARTITION p20241216 VALUES [('2024-12-16'), ('2024-12-17')),
PARTITION p20241217 VALUES [('2024-12-17'), ('2024-12-18')),
PARTITION p20241218 VALUES [('2024-12-18'), ('2024-12-19')),
PARTITION p20241219 VALUES [('2024-12-19'), ('2024-12-20')),
PARTITION p20241220 VALUES [('2024-12-20'), ('2024-12-21')),
PARTITION p20241221 VALUES [('2024-12-21'), ('2024-12-22')),
PARTITION p20241222 VALUES [('2024-12-22'), ('2024-12-23')),
PARTITION p20241223 VALUES [('2024-12-23'), ('2024-12-24')),
PARTITION p20241224 VALUES [('2024-12-24'), ('2024-12-25')),
PARTITION p20241225 VALUES [('2024-12-25'), ('2024-12-26')),
PARTITION p20241226 VALUES [('2024-12-26'), ('2024-12-27')),
PARTITION p20241227 VALUES [('2024-12-27'), ('2024-12-28')),
PARTITION p20241228 VALUES [('2024-12-28'), ('2024-12-29')),
PARTITION p20241229 VALUES [('2024-12-29'), ('2024-12-30')),
PARTITION p20241230 VALUES [('2024-12-30'), ('2024-12-31')),
PARTITION p20241231 VALUES [('2024-12-31'), ('2025-01-01')),
PARTITION p20250101 VALUES [('2025-01-01'), ('2025-01-02')),
PARTITION p20250102 VALUES [('2025-01-02'), ('2025-01-03')),
PARTITION p20250103 VALUES [('2025-01-03'), ('2025-01-04')),
PARTITION p20250104 VALUES [('2025-01-04'), ('2025-01-05')),
PARTITION p20250105 VALUES [('2025-01-05'), ('2025-01-06')),
PARTITION p20250106 VALUES [('2025-01-06'), ('2025-01-07')),
PARTITION p20250107 VALUES [('2025-01-07'), ('2025-01-08')),
PARTITION p20250108 VALUES [('2025-01-08'), ('2025-01-09')),
PARTITION p20250109 VALUES [('2025-01-09'), ('2025-01-10')),
PARTITION p20250110 VALUES [('2025-01-10'), ('2025-01-11')),
PARTITION p20250111 VALUES [('2025-01-11'), ('2025-01-12')),
PARTITION p20250112 VALUES [('2025-01-12'), ('2025-01-13')),
PARTITION p20250113 VALUES [('2025-01-13'), ('2025-01-14')),
PARTITION p20250114 VALUES [('2025-01-14'), ('2025-01-15')),
PARTITION p20250115 VALUES [('2025-01-15'), ('2025-01-16')),
PARTITION p20250116 VALUES [('2025-01-16'), ('2025-01-17')),
PARTITION p20250117 VALUES [('2025-01-17'), ('2025-01-18')))
DISTRIBUTED BY HASH(`uid`) BUCKETS AUTO
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Etc/UTC",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "10",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

数据案例:

INSERT INTO test.test (`dt`, `uid`) VALUES ('2024-12-05', '12345');

复现代码:

SELECT
  avg(uid) AS mean,
  count(uid) AS `valuesCount`,
  count(DISTINCT uid) AS `distinctCount`,
  MIN(uid) AS min,
  MAX(uid) AS max,
  coalesce(
    SUM(
      CAST(
        CASE
          WHEN (uid IS NULL) THEN 1
          ELSE 0
        END AS BIGINT
      )
    ),
    0
  ) AS `nullCount`,
  STDDEV_POP(uid) AS stddev,
  SUM(CAST(uid AS BIGINT)) AS sum
FROM
  test.test
LIMIT
  1;

出现错误:

errCode = 2, detailMessage = (doris-be-cn-0.doris-be-cn.dataplatform.svc.cluster.local)[INTERNAL_ERROR]output type not match expr type , col name , expected type Float64 , real type Nullable(Float64)

1 Answers

复现步骤:

  1. 基于上面的建表语句新建test表
  2. 插入数据案例
  3. 执行复现代码,出现上面错误

该场景主要来自openmetadata添加doris profiler时执行报错