liuanxin commented on PR #29463:
URL: https://github.com/apache/doris/pull/29463#issuecomment-1968426176
这个 pr 是有问题的, @starocean999 @englefly @morrySnow @xiaokang 相关的 sql 如下
```sql
drop table if exists temp1;
create table temp1 (
a tinyint,
b varchar(40),
c varchar(80),
d datetimev2,
e bigint,
f tinyint default '0',
g bigint,
h bigint
)
unique key(a, b, c, d, e)
partition by range(d) (
partition pmin values less than ("2023-08-01 00:00:00"),
partition p202308 values less than ("2023-09-01 00:00:00"),
partition p202309 values less than ("2023-10-01 00:00:00"),
partition p202310 values less than ("2023-11-01 00:00:00"),
partition p202311 values less than ("2023-12-01 00:00:00"),
partition p202312 values less than ("2024-01-01 00:00:00"),
partition p202401 values less than ("2024-02-01 00:00:00"),
partition p202402 values less than ("2024-03-01 00:00:00")
)
distributed by hash(b) buckets auto
properties (
"enable_unique_key_merge_on_write" = "true",
"estimate_partition_size" = "2G",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "month",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"replication_allocation" = "tag.location.default: 1"
);
/*
当前 pr 是从 2.0.4 开始加进去的, 下面的查询语句
在 2.0.4 之前, 数据是正确的, 最后 4 列分别是 100 1 81100 36
从 2.0.4 开始(包含最新的 2.0.5), 查询有问题, 最后 4 列成了 100 1 100 1
*/
select ctemind aa, b bb, 0 cc, 0 dd, c ee, datediff(dates, ctemind) ff,
sum(h) gg, count(distinct if(basic.h > 0, ctedi, null)) hh,
sum(sum(h)) over (partition by ctegroup order by dates rows between
unbounded preceding and current row) ii,
sum(count(if(basic.h > 0, 1, null))) over (partition by ctegroup order by
dates rows between unbounded preceding and current row) jj
from (
select a, to_date(d) dates, b, c, g, h
from temp1 i where i.a in (1, 3) and i.b = 'abc' and c = '123'
) basic
inner join (
select b cteai, c cteci, g ctedi, min(if(a = 1, dates, null)) ctemind,
dense_rank() over ( order by min(dates), b, c ) ctegroup
from (
select a, to_date(d) dates, b, c, g, h
from temp1 i where i.a in (1, 3) and i.b = 'abc' and c = '123'
) t
group by cteai, cteci, ctedi
having ctemind is not null
) cte on cte.cteai = basic.b and cte.cteci = basic.c and cte.ctedi = basic.g
group by ctemind, b, c, dates, ctegroup
having ff >= 0 and ff < 180 and date_add(ctemind, interval ff day) >=
'2023-12-29' and ctemind = '2023-12-21';
````
temp1 表的示例数据放在下个 comment
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
