CASE expression returns unexpected results when used with partition by subtable + status window + aggregate UDF

[lw-leo]

描述

按子表切分 + 状态窗口 + CASE 表达式 + UDF聚合函数， CASE 表达式的结果异常( 把UDF聚合函数给我普通聚合函数结果就正常)

环境

TDengine：3.2.3.0 Linux leo 5.4.0-174-generic #193-Ubuntu SMP Thu Mar 7 14:29:28 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

测试

一、使用非UDF聚合函数 （结果正常）

1. SQL

select 
  count(ts) as c, 
  case when dsg = 2 then 1 else 0 end  as status 
from 
  bms.bms_st 
where 
  ts >= '2024-03-16T09:29:27.749Z' 
  and ts <= '2024-03-21T09:29:27.749Z' 
partition by tbname 
state_window( case when dsg = 2 then 1 else 0 end)

2. 输出

           c           |        status         |
================================================
                     9 |                     0 |
                  6373 |                     1 |
                   694 |                     0 |
                    20 |                     1 |
                   858 |                     1 |
.....

二、使用UDF聚合函数 （结果异常）

1. SQL

select 
  session_elapsed(ts, 5m, 1s) as wd,
  case when dsg = 2 then 1 else 0 end  as status 
from 
  bms.bms_st 
where 
  ts >= '2024-03-16T09:29:27.749Z' 
  and ts <= '2024-03-21T09:29:27.749Z' 
partition by tbname 
state_window( case when dsg = 2 then 1 else 0 end)

2. 输出

            wd             |        status         |
====================================================
         0.000000000000000 |       139734277949392 |
        21.298999999999999 |       139734277955456 |
         0.000000000000000 |       139734279117344 |
         0.000000000000000 |   3616733763980572208 |
         0.000000000000000 |       139734279164469 |
         0.000000000000000 |       139734277947536 |
         0.000000000000000 |                    48 |
         0.000000000000000 |                   689 |
....

三、使用 UDF聚合函数 + 普通聚合函数 （结果异常）

1. SQL

select 
  session_elapsed(ts, 5m, 1s) as wd,
   count(ts) as c,
   case when dsg = 2 then 1 else 0 end  as status 
from 
  bms.bms_st 
where 
  ts >= '2024-03-16T09:29:27.749Z' 
  and ts <= '2024-03-21T09:29:27.749Z' 
partition by tbname 
state_window( case when dsg = 2 then 1 else 0 end)

2. 输出 (status 不可能有这么大的数据)

            wd             |           c           |        status         |
============================================================================
       168.384999999999991 |                    19 |                     0 |
         0.000000000000000 |                     1 |                   177 |
         0.000000000000000 |                     1 |       139739579850688 |
         0.000000000000000 |                     1 |       139739579547776 |
         0.000000000000000 |                     1 |                     0 |
         0.000000000000000 |                     1 |           34359738368 |
         0.000000000000000 |                     1 |                   263 |
        10.179000000000000 |                     3 |                     0 |
         0.000000000000000 |                     1 |            4294967297 |
         0.000000000000000 |                     1 |                   258 |
       105.259000000000000 |                    10 |                     0 |
         0.000000000000000 |                     1 |                   624 |
         0.000000000000000 |                     1 |                    52 |
         0.000000000000000 |                     1 |       139739579853920 |
         0.000000000000000 |                     1 |       139739579851712 |
....